Macro Code To Work Between 2 Open Workbooks
Jun 4, 2008
I am trying to simultaneously use two Excel workbooks that are open at the same time--one is calculations--other is data scenarios...one worksheet = one scenario: A macro uses "Set" to specify workbooks for "current_wb" and "wbkFrom". Code here
Set current_wb = ThisWorkbook
Dat_Fil = Application. GetOpenFilename
Workbooks.Open Dat_Fil
Set wbkFrom = Application.ActiveWorkbook
NOTE-Workbook object variables are declared outside any macro, and as "public." Code here
Public current_wb As Workbook
Public wbkFrom As Workbook
In a second macro I want to use the Current_wb and wbkFrom object variables I set in first macro, but get the following error: "object variable or With block variable not set" for any of the following statements in the second macro
current_wb.Activate
current_wb.Worksheets(1).Select
wbkFrom.Activate
I've declared the object variables as public in a different code module where there is no other code; I've tried declaring as public at the top of the code module that has my macros, but declared at top of code module and outside any macro; I've tried declaring the object variables as "static" variables (instead of "public") within the first macro. I can't figure this out.
View 4 Replies
ADVERTISEMENT
Mar 23, 2014
I have this code...
Code:
Sub Macro1()
'
Sheets("Log").Select
Range("F1").Copy
For Each wb In Workbooks
If wb.Name Like "Test Book*" Then
wb.Activate
Sheets("Data").Select
Range("O2").Select
ActiveSheet.Paste
[code].....
can't get it to work properly...Basically I want the code to copy the date in cell F1 of the 'Log' sheet in Workbook 'Main' - then goto an open Workbook called 'Test Book' (note: this Test Book is a partial string name used hence the other code around it) and paste the copied date into cell O2 in sheet 'Data'
Using this pasted date in cell O2 carry out the required filter function.
The copy/paste of the date from my Workbook Main to Test Book isn't working so the code then shows error when trying to filter the data using the pasted date.
View 4 Replies
View Related
Mar 12, 2014
I have a macro to get copy of sheet named "Doc Info" from workbook File 1 to active workbook.
I could do it for one file on any active workbook.
But what I would require is, upon executing this macro , i want this macro to get executed in all open workbooks( could be any numbers ).
I want to move copy of sheet from File 1 to all open workbooks ( which i am doing it manualy for every file )
All these open workbooks could be from any folder , wont be in same folder.
So logic is to execute my macro apply in all open workbooks in my computer.
Below is the code and i have attached file for test
HTML Code:
Sub Copysheet()
Dim wSht As Worksheet
Dim wBk As Workbook
Dim wBk1 As Workbook
Set wBk = ActiveWorkbook 'Workbooks("File 2.xls")
Set wBk1 = Workbooks("File 1.xlsm")
Set wSht = wBk1.Sheets("Doc Info")
wSht.Copy before:=wBk.Sheets(1)
End Sub
View 2 Replies
View Related
Apr 17, 2007
I need to remove hyperlinks across probably 30-40 workbooks, is there a way of doing creating a macro that would do this across many instead of 1 at a time?
View 9 Replies
View Related
Feb 8, 2014
I have my code here:
VB:
Sub openfiles()Dim Path As String
Dim ExcelFile As String
' Path = GetFolder("C:UsersKinteshDesktop")
Path = "C:UsersKinteshDesktopVBA programmingMaps"
ExcelFile = Dir(Path & "*.xls")
[Code] ....
NextCode:
GetFolder = sitem
Set fldr = Nothing
End Function
My problem is that the code all actually works (including the function and when I use the commented part), but pointing to this one specific directory (the one I'm using right now), literally nothing happens.
View 6 Replies
View Related
Aug 23, 2008
I am having difficulties with my Worksheet_Activate() macro. It works great within workbook1 when it is only workbook1 open - but when I open another workbook2, the macro stills runs, presumably because Sheet1 of workbook1 is still activated as well as the newly activated sheet in workbook2.
Is there a way to ensure that only 1 worksheet of 1 workbook is activated at a time? Or that sheet1 of workbook1 is deactivated when workbook2 is opened/clicked on? I need my Worksheet_Deactivate macro to run to get rid of my Worksheet_Activate macro (which runs an application that resets the function of keyboards keys). Otherwise moving around workbook2 is a nightmare. When I navigate back to workbook1 while workbook2 is still open, I still want sheet1 of workbook1 to be activated and my macro to run .
View 7 Replies
View Related
May 29, 2007
I'm trying to get a Workbook.Open macro to work. I"m using the exact name for the location. For example:
Workbooks.Open ("O:AdministrationPM Weekly's2007PMWeeklyWE")
My other locations are working fine. For example I am using:
Workbooks.Open ("O:AdministrationDCS Week EndingStats"
View 9 Replies
View Related
Mar 29, 2013
I am trying to cycle ALL open workbooks so that I can choose the file to manipulate in subsequent code this is the code that I am utilizing furhter below
this is my code
VB:
For Each wb In Workbooks
If MsgBox("Do you want to do access this Workbook for the Update " & Chr(10) & Chr(10) & wb.Name, vbYesNo) = vbYes Then
wb.Activate
VI_wb = wb.Name
I = True
End If
Next wb
But for some reason it only loops through .xls and xlm workbooks BUT not .xlsx
is there a way that I can loop through any and all open workbooks.
The reason I do this is because my update are based on many numerous excel workbooks with differnet extentions, and naming conventions and many of the come via email, I cannot use the eact naming convention
View 1 Replies
View Related
Oct 15, 2008
the code necessary to merge all open workbooks (each contains only one worksheet) into 1 workbook made up of all of these individual worksheets?
View 2 Replies
View Related
Apr 14, 2009
i have this macro to save as a csv this macro only save the workbook the you are looking at, i will like for this macro to save as a csv all open workbooks preserving the name of the xls file.
Sub SAVE_AS_CSV()
Dim strFilename As String
strFilename = ActiveWorkbook.Name
strFilename = Left(strFilename, InStr(strFilename, ".")) & "csv"
ActiveWorkbook.SaveAs filename:=strFilename, FileFormat:=xlCSV
End Sub
View 2 Replies
View Related
Jun 20, 2008
I've got a workbook ("Overview") which needs to refer to other workbooks (actually staff timesheets) which sit on a shared drive. All of these are passworded so only the staff member (and myself) can access them
As the workbook opens, it reads the links from the other files, and thus I have to enter the passwords one by one to update the data. I have come up with a macro which should do the job as follows:-
Private Sub Workbook_Open()
MsgBox "OK to run macro?"
' Message is here to let me know if the macro has run
Application.Workbooks.Open "S:LeaveTimesheetsFred 2008.xls", True, False, , "abc", "abc"
Application.Workbooks.Open "S:LeaveTimesheetsBill 2008.xls", True, False, , "def", "def"
Application.Workbooks.Open "S:LeaveTimesheetsTom 2008.xls", True, False, , "ghi", "ghi"
Application.Workbooks.Open "S:LeaveTimesheetsDick 2008.xls", True, False, , "jkl", "jkl"
Application.Workbooks.Open "S:LeaveTimesheetsHarry 2008.xls", True, False, , "mno", "mno"
End Sub
This seems to work, as it opens the workbooks. However, it doesn't run until after I've been manually prompted to enter the passwords. I'm guessing that the workbook links are taking precedence over the macro?
Is there either:-
a) A way to update the data into Overview without having to actually force the timesheets to open?
b) A way to make the macro run before the links update?
(btw I've not compromised the user passwords as Overview is itself passworded, so no-one can view the code!)
View 9 Replies
View Related
Oct 16, 2006
Here is what I'm doing:
-Open .xls file
-Run a macro (it works correctly when run by itself but I'm trying to speed things up)
-Save
-Close
-Open next file, and so on (I'll do this on no more than 200 files at a time all in the same folder)
Here is the code I am using: ....
View 9 Replies
View Related
May 16, 2007
In the last week the following code has ceased to work, and I do not know why:
If TradeShow = True Then
ReportFile = " ANALYSIS PRINT (CS+T) MASTER.xls"
Else
ReportFile = "ANALYSIS PRINT (CS ONLY) MASTER.xls"
End If
Workbooks.Open Filename:=ReportFile, ReadOnly:=True
Worksheets("TRANSFER DATA").Select
PriceFile = "PRICE COMPARISON.xls"
The Workbooks.Open command executes, and the screen shows the cells of the new spreadsheet. Nothing else happens, and the cursor is sensitive to operator control, showing that Macro control has been lost. I have inserted a test 'MsgBox' after the .Open command, and not even that is executed - so I am certain that the .Open is in some way wiping out the Macro.
View 9 Replies
View Related
Feb 12, 2009
I'm just starting out on my journey into VBA and this forum has been a valuable resource for picking up hints and tricks
I've decided to cut some corners and ask for help for the final piece of my current jigsaw - effectively this comes in two bits.
Part one:
I want to copy a sheet from two open workbooks and paste them into my active wookbook. Both source workbooks only have one sheet. I want a dialogue box to select the desired workbook, select and copy all data and then paste to a specified sheet (replacing the current data) in the destination workbook. I then want to select the other source workbook from the dialogue box and copy all data to a separate sheet in the destination workbook. I would like the dialogue box to have two options - Ok to select, copy and paste data, Cancel to end the macro.
Part two:
I want to copy a sheet from my source workbook and paste it as a separate sheet in a new workbook (a one page workbook would be ideal). I then want to save the new workbook in a specified location as "Data - Date" in the format 2009 02 12.
View 9 Replies
View Related
Sep 27, 2006
I'm trying to write a macro that opens selected files, and updates cell F8 to a given value. But if I add more than one file it doesn't work... all in all I want to add 27 files to this macro.
at the moment it is
Sub UpdatePeriod()
'
' updateperiods1 Macro
' Macro recorded 27/09/2006 by navinderm
'
' Keyboard Shortcut: Ctrl+Shift+O
'
Workbooks.Open Filename:="G:....*.xls"
View 3 Replies
View Related
May 2, 2014
I currently have a piece of code that opens all of the files in a folder that are called "*agent*", opens them and copies information. Now, these files come with numbers at the beginning which, are always the same. I only want to open certain files that begin with, for example, 801, 802, 803, 804, 805 and 806. How would I write this into my code? As you can see from the below code, it now looks for the files that all have "agent" in the name, but this is opening files that have that name but are not the right ones. Here is my current macro...
[Code] ....
I hope this isnt as simple as putting "MyFile = Dir(MyFolder & "*801*", "*802*")" etc.
View 2 Replies
View Related
Jun 7, 2014
I have 3 workbooks open. 1 is my working file and I name it as "Final[date].xlsm". The other 2 files are my source files which are also open are named as source1.xlsx and source2.xlsx. Both the source files has only one tab/sheet as "Sheet1". My objective is to bring the contents of the source file to my working file in 2 different sheets. all the contents in Sheet1 of "Source1.xlsx" should be paste.values only to the "Final[date].xlsm" with a sheet name "BankDetails". This will enable the user also to replace the old data in "BankDetails" tab. The "source2.xlsx" sheet1 has a different situation. I need to copy only the cells with values, not the entire cells, because it has to be pasted (values only) to range C2:L. I have formulas in the other columns before C and after L.
Take note that my working file name is changing every based on the date while my source file has always same file name.
View 12 Replies
View Related
Jul 25, 2012
I am having an issue with a macro to open password protected workbooks. The macro works just fine in opening the files and whatnot, but for some reason it prompts me to enter the password again once the file has been opened. Funny thing is I can either hit OK or hit Cancel and it goes to the next file. All files open correctly, I was just curious as to why this is happening and how to correct it?
View 1 Replies
View Related
Mar 8, 2013
I have a macro that opens all workbooks from one directory and runs a macro for each workbook to clean up the data. I cannot figure out how to take all those open workbooks and save them to another directory and close the workbook. Also, I do not want the macro workbook (xlsm) to save. I only want it to close. I am working in 2007 Excel.
View 8 Replies
View Related
Jul 21, 2007
I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the
Windows("estimate sheet one.xls").Activate
View 2 Replies
View Related
Sep 15, 2008
I have been given the following code to transfer data from multiple workbooks within folders and subfolders to retreive the same line of data from each of the workbooks and place them in a master workbook.
the folders are set up as follows,
there is a main folder, (a yearly folder)
within this are 12 monthly folders (named January to December)
within these are four weekly folders (named week 1 -week 4)
contained within these weekly folders are the workbooks that i wish to copy data from.
for example a1 - k1
the code i am using transfers the file names but comes up with #REF! instead of transfering the data
Here is the
View 9 Replies
View Related
Jan 21, 2009
below code
Sub Collect_OS_Data()
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Dim fso As Object, fld As Object, fil As Object, fldPath As String, wbSrc As Workbook, wbCur As Workbook
fldPath = "Q:PublicPAYMENTS Q&RREPORTSSuspense Activity BUSINESSOngoing - Suspense BUSINESS - Activity Reports"
Set wbCur = ActiveWorkbook
ActiveSheet.Name = "Raw Data"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.getfolder(fldPath)
On Error GoTo ErrHandler
For Each fil In fld.Files
This opens all spreadsheets within a folder and takes the data i require out of each one and pastes to a new workbook, however the files are password protected. Can you add into the code to open each spreadsheet as a readonly copy and paste data and then close without a save prompt or denying the save prompt ?
View 9 Replies
View Related
Aug 11, 2004
how to open a pdf file (located: "C:myfile.pdf") in macro code?
View 4 Replies
View Related
Feb 24, 2008
I have been looking for some VBA code that simple, when it runs, opens a browser and a specific, hard coded website. Is this possible in VBA?
View 2 Replies
View Related
Mar 5, 2008
I am currently using the following code in order to open a web page by clicking a button:
Private Sub Timetable18_Click()
ActiveWorkbook.FollowHyperlink Address:="http://journeyplanner.tfl.gov.uk/user/AHF/JP15__00006e8e.pdf"
NewWindow = True
End Sub
However, when this is executed it returns the following error:
Run-time error '-2146697210 (800c0006)':
Method 'FollowHyperlink' of object '_Workbook' failed
Clearly, I'm doing something wrong. I wondered if it had anything to do with the fact that the webpage I'm trying to open is a pdf and not html or similar, but this doesn't appear to be the issue.
View 4 Replies
View Related
May 12, 2008
I am trying to write code to open a new (blank) workbook while in an existing workbook (I am then going to pass data between the two which is easy). When I try to record the code to get the syntax for opening a new workbook it will not record any code. I also need to name the new workbook based on text in a cell in the existing workbook. I just saw before posting this that the code;
workbooks.add
will open a new workbook now I just need to name the new workbook based on text in my existing workbook.
View 2 Replies
View Related
Dec 3, 2008
I have built a sub that prompts the user for a folder then opens every workbook in the folder 1 at a time to get stats on the contents of each workbook. Worked like a dam until I ran into an unexpected bug. Some of the users built on open events in their workbooks. ...
Right now my routine inventories workbooks to get formula counts, cell counts, most complex formula, highest value... it does this by looping throught the sheets and the cells. If there is a way of obtaining those stats without opening the workbook I may need to rethink a lot of my work.
way to suppress the code in the target workbook I open through workbooks.open
View 9 Replies
View Related
Aug 7, 2008
how to open a Hyperlink directly. I have writen some code which allows me to copy over certain cells from a large list of hyperlinked files. However I am struggling to figure out how to open the hyperlink without actually writing the hyperlink address into the code. The hyperlinks are the file names and not the file paths. In essence I'm aiming to just get the macro to "click" the hyperlink to open it. Once the file is open the rest of my code will work, it is just this one sticking point.
View 2 Replies
View Related
Apr 26, 2012
I am making a macro and I need to extract some data from workbooks. However, I have many workbooks and it would take time to the macro to open and analyze each of them.... is it possible to work with workbooks without opening them?????
For instance, I need to open a workbook and get all rows that contain the word HEXXX..... However, I have about 30 workbooks and it would take forever...
View 2 Replies
View Related
Jan 27, 2009
I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..
View 2 Replies
View Related
