I have a work book with many sheets and staff continue to mess this up so I would like to know how I can have a specific sheet called Instructions open when this workbook is opened preferably no macro's is this possible
View 7 RepliesThis is a shot in the dark. I have a workbook with 12 sheets, one for each month of the year. Each month is divided into the different weeks of the month. For example sheet January is divided into:
Week 1: 1/1/12 through 1/7/12
Week 2: 1/8/12 through 1/14/12
and so on:
I would like my workbook to open on the cell containing the date range for that particular date.
For example, if I open the workbook on 1/10/12 I need the workbook to open on the cell range that contains Week 2: 1/8/12 through 1/14/12.
Need to have a particular workbook open at a specific time...
I wrote this and placed it in a Personal.xls module, did not run automatically. I then placed the 2nd SUB() in the "This Workbook" and nothing. Where or how is the proper way to run this...
----------------------------
Sub Open_IndexAnalysis()
Workbooks.Open Filename:="e:Index Analysis.xls"
End Sub
------------------------------
Sub Run_OpenIndexAnalysis()
Application.OnTime TimeValue("09:40:00"), "Open_IndexAnalysis"
End Sub
-----------------------
Problem: I want a macro to detect if a workbook is open so I can skip the workbook and move onto the next item. I do not want the user to be prompted in any way. I do not want the workbook to be shared. I simply want to pass over the workbook (but be aware I have passed it over).
--Long Version--
About forty team members have their own workbooks and at day’s end the team leader uses a macro to poll each team member’s workbook in turn to gather work results. When gathering these results the macro also needs to write some admin data to each workbook.
I am aware of the workbook sharing option in Excel, but I am wary of using it. It is not a show stopper if a team member’s workbook is already open and work is not gathered on a particular day – we’ll get it the next day. I just need to be able to trap the event and manage it. The central command I use to access a team member’s workbook is :
Workbooks.Open FileName:=WorkBookName................
Is there a simple one line VBA code to protect all sheets when a workbook is opened?
View 5 Replies View RelatedIm looking for a Workbook_Open event that will "clear all" on every sheet.
View 3 Replies View RelatedWhat im trying to do is have a macro that will open a file called Blue 1.xls in location "I:SchedulesBlue" and then copy data from cells N13:034. Then paste it into another excel file (Press.xls)on worksheet "Press Break" cell G14.
The data that is copied not always fills up the cells N13:O34 so I need it to go to the next blank cell in column N and paste the information from file Blue 2, and so on for Yellow 1, Yellow 2, YellNR, and Green.
So to summarise I need a macro to open 6 files copy data from the same location on each of the files(N13:O34) then close and paste it into a master document(Press.xls) worksheet "Press Break".
I am looking for the code that I would use so that when the workbook is opened it would always open with "Main" sheet.
View 4 Replies View RelatedVBA code that would delete ALL other sheets in the workbook except for Sheet "Data".
View 2 Replies View RelatedI am trying to have a macro that separates a list with unique values in column a, copy the results, open a file name in column AG, and then paste the copied stuff into the open workbook.
Currently, it runs, but when I copy to the new sheet, the thing I want goes where I want it to, but I also get an extra sheet1. how can i change this so the sheet1 doesn't get pasted in?
Sub MC()
Dim r As Long, rng As Range, ws As Worksheet
Application.DisplayAlerts = False
Application.ScreenUpdating = False
ActiveSheet.Name = "Sheet1"
With Sheets("Sheet1")
Sheets.Add().Name = "temp"
[code]....
I have a workbook that has worksheets that serve as data input pages for the user.
I have control boxes created by the forms toolbar.
I created a splash screen that is to be launched when the file is opened.
Instead, often the last sheet that the user was on when they last saved the file (usually but not always the worksheet called "Main Menu") is seen first.
I understand that not all the sheets can be hidden - that one must be unhidden.
So I created a sheet called "Background" that is a solid color with no gridelines.
Objective:
When the file is opened, I want the "Background" sheet to be seen first and then the splash screen to be initiated.
Now - when the user opens the file, the Main Menu worksheet is seen for a few seconds (or whatever the last sheet was on before it was last saved), then it disappears and the solid background screen is shown with the splash screen. When the splash screen ends, the "Background" sheet disappears.
Here is the code that I have in the workbook
Private Sub Workbook_Open()
Dim wks As Worksheet
On Error Resume Next
For Each wks In ThisWorkbook
If wks.Name "Background" Then
wks.Visible = False
End If
Next wks
End Sub
What has to be changed so that the first sheet that is seen upon opening the file is the "Background"; then the splash screen happens; then it returns to the Main Menu sheet?
I want a specific workbook to be always on manual but when I open other workbooks I want them to remain on automatic even though the first workbook is set on manual through vba code. Is that possible to be done?
This is the code I run:
Private Sub Workbook_Activate()
With Application
.Calculation = xlManual
.MaxChange = 0.001
.CalculateBeforeSave = False
[Code] .....
I know that Application. Calculation refers to all open workbooks but I don't know the code to specify the manual calculation to this workbook only while others are open.
I have a workwook with multiple sheets. One of the sheet name is "ABC".
I have a macro which does a particular task. I want that when the macro is run, it should run on all the sheets of the workbook except sheet "ABC".
Here is whar I have tried:
Sheets("ABC").Activate
For Each ws In ActiveWorkbook.Worksheets
If ws.Name ActiveSheet.Name Then
"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
End If
I have x number of sheets in a workbook, and I am looking to copy sheets a, b and c into a new workbook that I will create.
The a, b and c are in no order, or consecutive (although they could be). I want to be able to count the number of sheets in the workbook, and traverse through that to find sheets that I need, and select them to copy over to a new workbook.
I am trying to modify the macro that I have put together so that it executes only specific worksheets within a single workbook. In this example, I would like the macro to update worksheets A, B, and C but I have more sheets in my actual file. I am only able to get the macro to work on the active sheet. Attached is my workbook.
VB:
Sub HideColumns()
Application.ScreenUpdating = False
Dim myRange As Range
For Each myRange In Range("B5:M5")
[Code] .....
I have created a spreadsheet some time ago and have been asked to improve on it but I'm rusty with VBA.
I have an automated ordering system that saves each sent order as the date e.g "05-04-2013.xls" but the management team want a graph with the data for the last 4 weeks compared. I have created a seperate workbook called "consumables report.xls" which has a column with the products listed followed by columns "Quantity" and "cost" which is repeated for the 4 weeks of the month.
I want to add a button to prompt the user to choose the saved order e.g "05-04-2013.xls" (all orders saved in same directory) to copy and paste the quantity and cost columns (c8,D69) into "consumables report.xls". I got this to work earlier but it would only paste the formulas and not the values. So I need
A prompt to open workbook
Copy range (c8,d69)
Close work sheet
Paste special .value (c8,D69)
I dont care if it has to open the workbook to copy the data as this will only be used once a month so it dosnt matter how slow the code is.
week 1 week 2 week 3 week 4
Product
quantity
cost
quantity
cost
cost
quantity
cost
1
2
3
4
5
6
7
8
I have Monthly sales sheets that import my cash register data into them. I wanted to set them up to do everything without being there. So I have my task manager open excel at 9:30pm everyday and it runs the macro to import the data into the correct day of the month. Here is the workbook
open macro-
Private Sub Workbook_Open()
Dim dTime As Date
dTime = Time
If dTime >= TimeValue("9:30 PM") And _
dTime < TimeValue("9:40 PM") Then
ImportData
End If
End Sub
This is in my July spreadsheet only. So is there a way to make it know which month spreadsheet to open on the 1st of the month? So come August 1st it will automatically open the August workbook and input the data for the first day? By using the date?
I have a workbook with the following sheets i do not want to hide called "Detailed Template", "INTERFACES", "STATUS".
I then have a number of worksheets which have their names as cell addresses i.e. "E4", "E5", "D4", "F4", "G7" and so on.
I would like to hide all worksheets (except the 3 mentioned at the top [which are sheets 1-3]) and any which contain say the value of '4'. This value is defined by a cell in the "INTERFACES" worksheet say 'A1' for example.
I want the macro to be able to automatically do this for which ever value is in 'A1'. So if it were to change to '7' it would hide every sheet that doesn't contain '7' in the name.
I have a presentation that I open from a short-cut. After the "Welcome Page" is opened, I want to open a second workbook in a new instance of Excel after 4 seconds.
I think that I can open the new instance of excel, but I don't know how to activate the macro after 4 seconds.
I'm sure there is a function someplace for this that can be used in a macro.
Then, after the second Workbook is opened, I want the Welcome Page "Workbook" closed, leaving the second Worbook open.
My Splash screen opens a few seconds after the workbook has loaded. Is there a way to make the workbook open minimzed until the the splash screen closes then open properly? What I trying to say is that only the splash screen is visible until it closes.
View 5 Replies View RelatedI would like to write a Sub that will see if a workbook is open and if it is not then open it. I know how to have a macro automatically open a workbook, but I run into problems when the macro runs and tries to open an already opened workbook.
View 5 Replies View Relatedthis is a relatively straightforward query, would be obliged for any tips on same. I have the following piece of -
View 2 Replies View RelatedCreate a full copy of an open workbook (eg. activeworkbook MyFile.xls) using VBA, with the new copy (eg Book1.xls) open as well ,without having to save a copy first then open it ?
View 9 Replies View RelatedI did a macro on my mac to transfer a sheet from one workbook to another worbook. It works very well when the destination workbook is open. Therefore I wanted to add some piece of code to check if the destination workbook is open. If not then I wanted the macro to open it before tranfering the sheet. Here is the code I´m using for tranfering the sheet
Sub Transfer_Sluttet()
If ActiveSheet.Index <> Sheets.Count Then
Application.DisplayAlerts = False
Set ws = ActiveSheet
Sheets(ws.Index + 1).Delete
ws.Move Before:=Workbooks("Sluttet.xls").Sheets("sheet2")
'Moves active sheet to beginning of named workbook.
'Replace Test.xls with the full name of the target workbook you want.
Application.DisplayAlerts = True
End If
End Sub
This is the type of macro I useually use on my pc to check if a workbook is open and if not then open it
If IsWorkbookOpened("Filename.xls", "C:Documents and ..................
I have an Excel application in which I use the Workbook Open event to show a userform.
This works fine when Excel is not already open, but if another Excel workbook is already open, the Workbook Open event does not work.
Need to move data from 14 sheets (1 pay period=14 days)(2 showing on attachment for example purposes) to time cards for each person (number of employees will vary).
Key data to move to the time card is the date of the hours, #Reg Hours, #OT hours, the ticket# and job# for those hours.(ie Chris Adams Aug 15, need Chris's Reg and OT hours (8 Reg and 2 OT) on his time sheet, and the corresponding ticket number and job number (in this case from I1, I2).
Unknown number of ticket/job numbers, but the pattern of cell entries will remain the same along the top of the date sheets.(starting at I1, then L1, O1, the next would be R1)
Unknown number of employees but they will continue filling down where the names are on the date sheets (column A). Each employee would have a time card sheet as well.
Copy data from workbook, open existing workbook, select range and paste. But my copied data is lost.
Sub Select_Copy_Paste()
'
'
Windows("ElektroFunctiesDatabase.xlsm").Activate
Sheets("PowerSupply's").Select
Range("A2:I6").Select
Selection.Copy
[Code] .........
' Here i need to do something to paste data into r.address?
I have data that daily needs to be refreshed and printed to pdf.
I figure the simplest way to do this would be to task schedule the workbook to open daily. Then on open it will refresh the data, print it after all data has been refreshed and close the workbook.
I set it up originally without the need to print so I have all the queries set to refresh when opening the file, however when I now try and put the code to print to pdf on the workbook open event it runs before the queries are finished running. (Query notes: queries were created through Microsoft query, and are accessing a MySQL database queries set to refresh when opening the file queries set to enable background refresh).
I run a model in Excel that automatically saves my file every xx iterations. After saving the file I want to make a backup of the file. Tried
[Code] .....
but get a permission denied error message.
I don't want to use .SaveAs as it is a huge file that takes a while to save and SaveAs has a tendency to break links that should not be broken..
Here's my macro:
Code:
Sub CopyRow()'
'Copies row to new sheet, highlights it, marks column 'A' as copied.
'
Dim cCell As Range
Set cCell = Selection.Cells(1, 1)
Selection.Copy
Sheets("Sheet2").Select
Rows("2:2").Select
[Code] .....
Is it possible to modify it to paste into a different workbook called c:filesDestination.xlsm, instead of the existing workbook (Source.xlsm)? The destination sheet name is the same (Sheet2). It's OK if both workbooks are open at the same time.