VBA Code To Switch Between 2 Workbooks
Apr 18, 2003
I have written VBA code to open a separate file and then copy back and forth between the two files. However, when I recorded, I got commands like the following:
Windows("DestinationFile.XLS").Activate
then the copy/paste stuff then
Windows("SourceFile.XLS").Activate
The problem is that my "SourceFile" may have a different filename. I need the code to be robust enough to handle the possibility of a different filename. I tried the following, but it did not work:
Dim MarketProfileName As String
MarketProfileName = ThisWorkbook.FullName
Windows("DestinationFile.XLS").Activate
then the copy/paste stuff then
Windows(MarketProfileName).Activate
View 6 Replies
ADVERTISEMENT
Jun 20, 2012
I am working with Excel for Mac. This has presented a number of challenges but I have successfully handled them. I am having a problem though and would love some input. I have a workbook that contains a pivot table. The pivot table is based on "Sheet1" of my workbook. The user will hit a button on the pivot table spreadsheet and the first operation is to select a file from their computer. I am using a function called - MacScript to open the finder window and select the file. The file name (with full path) is stored in a variable called filepath. The file consists of a single sheet - Sheet1 and I select all and copy the cells. I then go back to the original workbook, select my worksheet with the source data for the pivot and I again select all and paste the new information over the old data. I have a named range called pivotsource that references the 9 columns that are required for the pivot table. What I would like to do now is go back to the original workbook and close it, do not save anything, just close it like nothing happened. I have tried to close it by using:
/* Workbooks(filepath).Activate
ActiveWindow.Close
*/
Since I had a large amount of data that is in the clipboard, I will also have to deal with the message coming up asking what I want to do with the clipboard. My bigger problem is that the activation of the filepath workbook doesn't work. How can I get back to the original workbook? I thought about the index of the workbooks but I don't know if the users will have other workbooks open at the same time. If they did, I could not reference it.
View 1 Replies
View Related
Jan 6, 2009
I have created a very long switch statement, which is too long to be placed in one row in VBA. I have attempted to put a space and underscore at the end of one line and continue the statement on the row below by placing a comma at the start of the second line. VBA will accept my efforts, but when I run the statement in the immediate window, the following error appears.
"Invalid procedure call or argument"
I understand that there are certain rules where I can split a switch statement onto two lines, yet I do not know what they may be.
View 9 Replies
View Related
Jun 4, 2008
i am trying to write a macro that will allow me to, while two workbooks are open and one is defined as active i.e
in workbook 1 i want the cell A1 to be defined as the sum of cell A2 and A3 in workbook 2.
i have a code that lets me do it but the problem is that this code (that i recorded) only gives me specific workbook names to work from..
i dont know if i make much sense but i would like the macro to work on any 2 workbook that are open and perhaps having flexibilty to choose would be great here is my attempt
View 14 Replies
View Related
Oct 5, 2009
I'm trying to use VBA to write from one workbook to another. I'm trying to populate other workbooks from a single 'control' workbook with a template.
I've managed to figure out how to open up Excel workbooks within a directory (this needs to be done multiple times) supplied by the user, then retrieve information from it to be collated, but I can't for the life of me figure out how to write to the file that I've opened.
I've got the code below, and it's very scrappy as I've been trying lots of different things but nothing's worked. I'll try and highlight as best as I can where I'm having difficulties, as I get the "object not defined" or some such error.
View 12 Replies
View Related
Jan 31, 2012
For example, I have 50 clients. In 1 workbook, I have a sheet for each client. When I'm finished with a particular client, I need their one sheet to place in their file and be done with it.
The current process is to open that 1 global workbook, copy and paste the sheet I need into a new workbook and go from there.
I was wondering if there is a simpler way to achieve this with VBA coding?
Ideally, I would like to click an object button and then be prompted to select a worksheet from a list that contains all current non-hidden worksheets.
Once I select a worksheet, I would then be prompted to save as and select a file path. That would save that specific client worksheet in its own file that I selected.
View 7 Replies
View Related
Apr 8, 2008
Workbooks.Open "E:Prep2008" & "" & Format(ActiveSheet.Range("C6"), "ddd dd-mm-yy") & ".xls"
is code I use to open a workbook (workbook b) based on the date found at cell c6 of workbook A.
Question ... how do I divert activity to to the newly opened WB B from WB A?
Workbooks?.Select?
View 9 Replies
View Related
Apr 23, 2008
I know that there has got to be a post on here somewhere on this, but anytime I enter in "Import VBA Code" I get back loads of stuff that really isn't relevant.
In post [url] I had to change a line of code so that the formatting macros I made would be accessable to everyone.
Now that I have the fix in place I must import the fix in the one line of code into 100+ workbooks. I suppose I could do this manually, but I imagine that there is a much faster way to do this using VBA.
I was flipping through the "VBA and Macros for Excel" book by Mr. Excel and on page 334-335 I came across something that looked like it might work. However, it seems that this code is switching modules and my code is pasted in "ThisWorkbook".
All of the files are located in the same directory, so it would involve opening all the files in the specified directory, making the VBA code change, saving the file and closing.
View 9 Replies
View Related
Jul 13, 2006
I have written a sub that will compare the data in one colum to the data that i paste in another colum, when it finds a similarity it deletes the row. Everything works fine, but the file that needs to run the code will be changing every month. how do i export or package to use in other sheets. Also what command will i need to insert to get the new sheet to run the code.
View 7 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
Aug 28, 2009
I was trying to see if this code would work for my application where I want to compare two forms. Basically the master is filled out and then sent to a vendor. When it comes back from them rather than going through then entire thing and trying to determine if they changed or added anything manually I was hoping to use something like this code to do it.. I tried this code using a similar setup, basically a master file and an update file but it wouldn't actually populate the changes in the changes sheet on the master form..
View 3 Replies
View Related
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
Sep 7, 2009
I tried to use Workbooks("Inactivebookname.xlsm").Close False
and many variations thereof, but I simply cannot close this workbook when currently another workbook is active.
I have no problem in closing an active workbook with
ActiveWorkbook.Close
View 9 Replies
View Related
Nov 1, 2006
Am trying to copy some code from the ThisWorkbook object into about 100 other Workbooks. I know how to Import and Export Modules, but when I export code from ThisWorkbook in saves it as a CLS file. Upon export a new class module is created. I simply want to create code that will automatically copy the code from one VBA project ThisWorkbook object to another VBA project ThisWorkbook.
I have the code to open all the files etc just need to figure out how to import the code from ThisWorkbook.
View 7 Replies
View Related
Apr 24, 2007
I support an excel file that has a bunch of vba code behind including calls to msquery. The customer used this excel file by opening the original pulling in data for a specific client and than saving the file to a different name. The result is she has multiple copies of the file.
It currently has two problems:I need to change the code in one of the subroutines to fix a known problem. This of course involves opening up the Visual Basic editor (from the excel file), finding the code to change and making the code change.We have just upgraded to Office 2003 and MSquery is looking for xlquery.xla which is no longer needed in 2003. The problem this causes and its solutions are described at Microsoft Support here. I have done the quick fix which is putting the xlquery.xla where excel is looking for it, but I want to do the correct fix which involves running the following code in every workbook that accesses xlquery.xla
Sub DeleteQueryDefinedNames()
Dim n As Name
For Each n In ActiveWorkbook.Names
If n.Visible = False And InStr(1, n.Name, "QUERY", _
vbTextCompare) > 0 And InStr(1, n.Name, _
"Query_from", vbTextCompare) = 0 Then
n.Delete
End If
Next
End Sub
and than making sure you save the file as office 2003.Both of these things are much to complicated and time consuming for my customer to do for each individual excel workbook.
… Which leads me to my question: Is there a way to automate this?
View 8 Replies
View Related
Jun 16, 2014
I'm trying to write code in Airport1.xlsm to allow me to create a macro to automatically copy data from Airport-Data.xlsm and reformat it to what is needed in Airport1.xlsm.
Basically I need the code to take each airport in column A of Airport-Data and where there is a non-zero value in rows column C to G I need it to clear the appropriate column in Airport1.xlsm and add a 1 to the appropriate box.
I got stuck thinking about the nested For Next Cell in range procedure and how to execute it with a search for the correct row in Airport1.xlsm.
View 14 Replies
View Related
Nov 1, 2011
how to make VBA code work in all workbooks. I created a new module in my PERSONAL.xls file, and added the code, but the code does not work when I open a new workbook. Using Excel 2007.
View 4 Replies
View Related
Feb 16, 2009
We're using XP Pro using Excel 07
The background is there are about 40 people who use a Excel based program that contains a pivot and a bunch of other tools that they use on a regular basis, this set of tools has a version number. In this excel workbook, it has a function that looks on the network drive that we have and checks the local version vs the version on our network drive, if its wrong, then the user gets a popup stating your tools are out of date, would you like to update, then they click yes and I have these lines of code
Public Function GetNewTools()
Dim MyFullName As String
'Turn off alerts
Application.DisplayAlerts = False
'Open the new version of tools
Workbooks.Open Filename:= _
"Network DriveUpdate.xlsm"
End Function
The update file has this code that executes on fileopen in the thisworkbook section by calling the following sub
The main issue we're running into is near the very end, the code never makes it to "TEST 2". After the first workbooks close, the code just stops running. No crashes, errors, freezes, anything. It just stops running and never makes it to the second msg box.
View 9 Replies
View Related
Nov 21, 2006
I have a workbook which includes a simple set of options on closing such as selecting the front sheet, restoring scrollbars and saving the workbook. To avoid problems with subscripts out of range I am using the ThisWorkbook statement to close the workbook.
This works fine and causes the workbook to close when close is clicked on any excel window. The problem is that excel falls over when it tries to resume closing the other workbooks. I am given (ironically) an error saying "excel has encountered a problem and needs to close". Does anyone know how to work around this?
My code is below:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Toolbars9(True)
With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayWorkbookTabs = True
End With
View 8 Replies
View Related
May 3, 2007
I've seen a lot of code on how to pull data from workbooks in the same folder. However, I'm trying to pull the data in workbooks in various folders.
For examply, I have one folder, called "Master Files". In this folder is 12 folders, one for every month of the year. Within these folders, is one for each week, with the last day of the week being the name for the folder. In other words, it's like \Master FilesApril4-6-07data.xls
Now, I know how to get the data I need if all files are within the same folder. But how would I write a macro to run through all of those files when they're in separate folders?
View 9 Replies
View Related
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
View Related
Feb 19, 2014
I want the code to run when we open excel workbook "TEST" and it should open up all the workbooks one by one in the folder J:ABC and copy cells C2 and C4 values in the A and B columns of TEST workbook.
e.g There are 5 workbooks in the folder J:ABC so when the TEST workbook is opened then the code should run and open 1st workbook and copy values in cells C2 and C4 to it and close the workbook.
The code should run as below:
1st workbook:
C2 value will go in Test workbook B1
C4 value will go in Test workbook A1
Close 1st workbook
2nd workbook:
C2 value will go in Test workbook B2
C4 value will go in Test workbook A2
close 2nd workbook.
3rd workbook:
C2 value will go in Test workbook B3
C4 value will go in Test workbook A3
close 3rd workbook.
and so on It will be going to next rows in A and B columns.
View 2 Replies
View Related
Jun 5, 2008
I am trying to run the 'loop through a folder' code on multiple workbooks I receive.
The workbooks I receive are full of drop downs that have associated values of 1-3 on the first sheet. (About 100 in total) This particular workbook has the drop downs on one worksheet and the numeric results on another worksheet 'Results'
The second workbook 'Totals' (very basic) , just referenced each 'Results' worksheet and had equations that averaged all the drop downs cell by cell.
I would love to be able to use the 'loop through a folder' code to open them and then average them on the 'Totals' sheet. The main reason is that I am delegating this to another person and would like to eliminate the risk or human error. ( unless it is my own)
I am a total VBA n00b. Any assistance would be appreciated.
If needed I can upload the code or sheet as an example.
The base folder would always be the same. ie c: estresults*.xls
The naming would be very similar.
This loop code seemed relevant as it did not seem to require any file naming and would run through a folder and process all XLS files.
[url]
View 14 Replies
View Related
Oct 1, 2012
I need to copy a range of cells from various columns/rows from 1 sheet into 6 other sheets, but into a specfic range of cells(in the same workbook). I am looking for a code which would copy the cells, then allow me to save the sheets it has copied them to as seperate workbooks without loosing the values it copied. I would also like to make amendments to the cells which have been copied onto the other sheets, without having an error message if I type anything into the cells, also having any blank cells left blank rather than placing the 'o' value in the cell.
View 2 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
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
Jul 6, 2008
I have an excel file which contains following data in it.
Col-A Col-B Col-C ......
Cust Cd Name Sales
=======================
101 AAA 1000
101 AAA 500
101 AAA 3000
102 BBB 800
102 BBB 200
103 CCC 200
103 CCC 200
103 CCC 200
I need to create following three workbooks with name based on Cust Cd from above excel file.
Workbook - 1 : 101.xls which contains records only pertaining to Cust Cd 101.
Workbook - 2 : 102.xls which contains records only pertaining to Cust Cd 102.
Workbook - 3 : 103.xls which contains records only pertaining to Cust Cd 103.
View 4 Replies
View Related
Mar 28, 2014
I have a folder with multiples excel sheets
Destination : C: Project CustomerExcel
I would like to extract and compile the information contained in these cells:
a5,c5,a6,c6,c7,a14,g14,e16,g16,e18,i18,a20,g20,h22,j22,h24,l24 all the sheets.
New sheet would contain the information of each sheet eg. Column A2= file name
And Row B2 to R2 or whatever will be the corresponding cells mentioned above for each cell. The code I have only brings back the file name but only a5 from the range but not the rest.
VB:
Sub MergeAllWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim NRow As Long
Dim FileName As String
Dim WorkBk As Workbook
Dim SourceRange As Range
[Code] .....
View 5 Replies
View Related
Feb 24, 2014
Trying to manipulate a macro I recorded to be slightly more dynamic with some VBA code. The macro has saved me a lot of time however each month I have to re-do it due to the date changing and I'm sure there must be an easy way to adapt the below code.
Current Code (it repeats afterwards as there are lots and lots of sheets I need to perform the action on).
Windows("Raw Data.xls").Activate
ActiveWindow.DisplayWorkbookTabs = True
Sheets("Calls").Select
Range("C43").Select
Selection.Copy
Windows("Volume Tracking.xlsm").Activate
Sheets("Offline").Select
Range("AE33").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
What I'm after is a way to alter the range highlighted based upon matching a cell in "Raw Data.xls' and a cell in "Volume Tracking.xlsm".
The cell A43 in "Raw Data.xls" contains a month (which changes every month); if the month is Jan then I would want the Range to remain as AE33. If it's Feb then I would want the Range to be AF33 (and so on).
In "Volume Tracking.xlsm" I have the months in cells AE4 (Jan), AE5 (Feb) etc.
Is there a way to locate the column in "Volume Tracking.xlsm" on row 4 that contains the value found in A43 in "Raw Data.xls" and then paste what was copied out of "Raw Data.xls" C43 and paste it in to row 33 of the correct column that holds the matching month?
If not could I add code to say if cell A43 in "Raw Data.xls" = Jan then paste to AE33, if Feb then paste to AF33 and so on.........
Excel 2007
View 9 Replies
View Related
Mar 27, 2009
I have a user form that has several tab pages on it when the form opens it checks to see if the user has entered there default info if it is empty it needs to go to the default tab so the user can enter there info. How do I do this using vba in excel 2000 I have not been able to find any code that will do this if I use the set focus option for a text box on the tab page it comes up with an error saying the item is not visible! The user forme is names = UserDataInputForm. The tab pages I have are
1st = InputDailyTimesPage
2nd = OverTimePage
3rd = DataPage
4th = DefultDataPage
the form opens using the 1st tab page by default
in the user forms inisilisation code I have this check
View 2 Replies
View Related
