Referring To Other Workbooks Via VBA Code
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
ADVERTISEMENT
Jan 27, 2009
Sub vocab6()
Dim fname As String
fname = "c:Documents and Settings
obertDesktopvocab.xlsx"
Workbooks.Open Filename:=fname
Workbooks(fname).Worksheets("tangible nouns").Range("A1").Value = 9
End Sub
I just want to open a workbook saved on my desktop (from a different workbook), and insert 9 into the first sheet (which is named tangible nouns).
View 9 Replies
View Related
Feb 22, 2014
These workbooks are located in different folders.
The first is pasted "as link" from two open instances of Excel and is an array formula.
The second is just pasted from another open workbook.
{=Excel.Sheet.12|'xx.xxxFolderFolderFilename.xlsx'!'!SheetName!R2C9:R1500C22'}=IF(H4="N/A","N/A",VLOOKUP(A2,'I:FolderMore Folders[FileName.xlsx]Sheet'!$A$1:$T$35,13,FALSE))
What is the difference between using these two different methods?
View 1 Replies
View Related
Jul 31, 2012
My first version is this:
Code:
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).XValues = "=DATA!$A$8:$A$19
Works without problems, but I really don't want to use "ActiveSheet" there
I changed it to:
Code:
Dim chtTR As ChartObject
Set chtTR = shtData.ChartObjects("Chart 1")
chtTR.SeriesCollection(1).XValues = "=DATA!$A$8:$A$19"
and it gives me
"Object doesn't support this property or method."
View 3 Replies
View Related
Jul 14, 2006
If I have a button on each of numerous sheets, how do I get all of the buttons to refer to the same bit of code, without having to copy it to the click event of each?
View 4 Replies
View Related
Jun 14, 2014
The below is a data sheet which is going to be designed to keep the records of "sending and receiving" details of wash-garments from a garment factory.
Descriptions;
01. Starting from B12, the dates of sending and receiving are entered in the sequence they occur. Both sending and receiving can occur on the same date.
02. Starting from C12, there are reference codes related to the activity, if Sending the letter is entered as "S", and if receiving it is entered as "R". I included this for the calculation or sorting purpose.
03. Columns D to J includes the break down of sizes of the quantities sent or received.
04. column K simply calculates the totals of the columns from D to J.
Requirement : I need to have the totals of each size wise quantities sent and received in the "summary table" as follows;
>> Size wise totals of "sent qtys" to be shown in D5 to J5(referring to the code "S")
>> Size wise totals of "received qtys" to be shown in D6 to J6(referring to the code "R")
>> The dates to be updated and displayed"automatically" in the rows of the column B, when the code letters("S" or "R") is entered in the column "C"
Special Remarks : The last date of sending or receiving cannot be predefined, the rows(dates)will be kept adding according to the way sending and receiving may occur.
View 14 Replies
View Related
Feb 6, 2007
This time ive created a visual basic form that asks for a row number and when you put a row number in this form it deletes what is in column A, B and C of that row and shift the rows up so that there are no gaps between the data in the rows.
I need to know what code i would have on the 'delete' button of the form.
If you want me to describe it any further i would be happy to, just ask.
View 9 Replies
View Related
Jul 26, 2006
How does one pick up the data contained in a worksheet cell (say B5) and use it in the vba code of a custom function without passing the cell as an arguement
View 3 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
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
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
