Propagate And Run Vba Code Into Multiple Workbooks

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


ADVERTISEMENT

Run The 'loop Through A Folder' Code On Multiple Workbooks I Receive

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

Macro- Code To Transfer Data From Multiple Workbooks Within Folders

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

Create Multiple Workbooks From One Single Workbook Based On Customer Code?

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

VBA Code Error - Extract Data From Multiple Workbooks To Single Excel Spreadsheet

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

Combining Multiple Cells In Multiple Worksheets In Multiple Workbooks Into One Table

Jan 6, 2009

I'm currently doing a survey using an excel workbook that contains multiple questions across multiple worksheets using radio buttons linked to certain cells.

I have around 400 workbooks coming back to me, so what i want to do is take specific values from across many worksheets within each workbook and combine them into a large master table in a seperate workbook.

I've tried using VBA, but not being very proficient at it i've hit a brick wall with that, so i'm hoping that there is an easier way to do it than what i'm currently pursuing.

View 9 Replies View Related

Running Multiple Modules Across Multiple Workbooks With Multiple Sheets

Feb 19, 2012

I've got data being scraped from a site, putting 1 new workbook in a folder each day

each workbook has 40 sheets in it.

i need to run 5 modules in sequence on a sheet then loop to the next sheet and run the same 5 modules.

ive writen all the modules, and can loop them through the sheets in sequence but i cant work out how to loop them through the each workbook in the folder..

is there an easy way to do this or can it not be done because it would need access to the folder that holds all the wordbooks which lives outside of excel on the desktop ?

View 5 Replies View Related

Consolidate All Data In Multiple Worksheets Of Multiple Workbooks In One Master File?

Jul 12, 2014

I need a macro that would consolidate all data in multiple worksheets of multiple workbooks in one Master file.

All the workbooks will be in one particular folder. The macro should search for data in all the workbooks and consolidate it in one master excel workbook.

I am currently using both excel 2007 and excel 2010. This macro would really reduce manual work as currently consolidating data from 45 to 50 sheets takes an ample amount of time...

View 4 Replies View Related

Copy Multiple Columns In Multiple Workbooks Into Separate Worksheet?

Feb 27, 2014

The following code won't let me copy from the first workbook. I get a run time 1004 error stating "That command cannot be used on multiple selections".

I would rather not have to copy this by column for each of the 4 workbooks

[Code].....

View 6 Replies View Related

Summing Data From Multiple Workbooks Based On Multiple Criteria

May 14, 2014

I track distribution using 1 workbook for each sales rep. Each workbook has a separate worksheet (scorecard) for each of their locations showing which of our products are used in that location, for various functions. Each of the locations has a segment (bar, nightclub, casual dining, etc.), and a class (AA, A, B, etc.). All of the scorecards are identical, and are in the same file folder on our shared drive.

Sample:

Location A Class:AA Segment: Sports Bar Rep: John Smith

Product: Product X Product Y Product Z
Well: 1 0 0
Back Bar: 0 1 0
Cocktail Menu: 0 1 1

I was unable to find an HTML maker so this is not an actual sample but what I'm looking at. The above data indicates that for location A (Sports Bar, Class AA), Product X is used as the well, Product Y is on their back bar and their cocktail menu, and Product Z is not used at this location at all.

I need to set up an analytic workbook where I can sum data based on multiple and grouped criteria, and for multiple reps ie- display a scorecard that shows totals for nightclubs and sports bars, class AA, A, and B, for a particular rep; OR display a scorecard that shows totals for nightclubs, class A for a group of reps. Ideally I would like to do this using drop down menus that allow the user to select multiple criteria in each of the drop downs, having the data auto-populate based on those selections.

View 2 Replies View Related

Copy Multiple Values From Multiple Workbooks To Master Workbook

Oct 7, 2009

I want create a macro which will extract 6 values (see below) from a workbook tab called summary to an master workbook for reporting purposes. Each workbook has a unique file name e,g ACI1150.

Values on sheet SUMMARY:
A1
A2
A3
G21
G24
G26

I tried to adapt the below to get one item copied/extracted. However it would no work.

I am new to using macros

Sub GetG26s()
Dim MyDir As String, FN As String, SN As String, NR As Long
Application.ScreenUpdating = False

View 9 Replies View Related

Combine Workbooks With Multiple Sheets Into 1 Multiple Sheet Workbook

Mar 16, 2008

I have about 20 workbooks with different file names for different projects all saved in the same folder. Each workbook has about 10 worksheets and each worksheet is named in a similar fashion in each of the 20 workbooks (eg. revenue, cost, variance etc.). I want to pull out a worksheet named ' forecast' from each workbook into a master workbook so that the master workbook would contain the 20 forecast worksheets.

View 9 Replies View Related

Code For Summing Between Workbooks

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

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 View Related

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 View Related

VBA Code For Worksheets Within Workbooks

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

Code For Moving Between Workbooks

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

Change VBA Code In 100+ Workbooks

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

Making Code Available To Other Workbooks

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

Append Multiple Workbooks With Multiple Worksheets

Sep 5, 2008

I would like to append multiple workbooks with multiple worksheets in a separate workbook. For eg. I have workbook "A" with sheets 1,2,3 and workbook "B" with sheets 4,5,6. Now I want to append "A" and "B" to create workbook "C" with sheets 1,2,3,4,5,6.

View 9 Replies View Related

Changing Multiple Workbooks And Multiple Worksheets Within

Jan 28, 2009

I have a folder - U:30000 - that contains a number of identically layed out workbooks. What I'm trying to do is, in each, change a number of cell values. Where dominicb's solution to the previous problem didn't work for me, however, is that I need to change cells in multiple worksheets. The cells are specifically:

On worksheet 'Construction': C3 (which is a date), D3 (which is a text value); on worksheet 'FF&E': D3 (the same text value as D3 on 'Construction'. Unfortunately, whoever initially set up the workbook didn't have the foresight to link it!)

View 4 Replies View Related

Combine Multiple Worksheets From Multiple Workbooks

Dec 27, 2013

Code to merge worksheets from different workbooks stored in different location.

I have a sheet called "Master" in all the workbook I want to combine.

I have a unique password for all the workbook as well.

All the workbooks are stored in different folder location.

I would like to do a paste special values when the consolidation takes place.

View 1 Replies View Related

Combine Multiple Worksheets From Multiple Workbooks

Sep 7, 2006

I'm trying to combine data from several worksheets (one sheet per workbook) into a single, consolidated master worksheet for reporting purposes (filters and pivot tables). We do not need to keep formulas for the master worksheet, only values and formats. Individual worksheets are used by different users to capture case data in a Human Services field. Column headings are identical, but rows contain data on individual cases. I'm trying to find a relatively easy way to combine multiple worksheets into a single master. After I establish the worksheets and technique, it will be operated by extremely basic users so I've been reluctant to use extensive macros.

Because of complex reporting needs, the exact combination of worksheets being combined for reporting may vary. For example, one time I may combine Tom, Dick and Harry, another time Tom, Dick and Bob, and yet another time Tom, Dick, Bob and Harry. Obviously, one method is to cut and paste the rows into a single worksheet. Are there more elegant solutions that could easily be handled by very basic users? Worksheets are stored in a single folder along with a separate worksheet used for validation rules (as you can guess, this would ideally be a database application but for various economic and political reasons we are using Excel). One possibility, if straightforward, is to use Access to consolidate data then export it back to Excel for analysis. I've scoured the various threads but have not found a situation mirroring mine. The number of rows for each worksheet is generally less than one hundred, but there will be a few exceeding several hundred. Total numer of rows of the resultant master worksheet will not exceed 10,000.

View 4 Replies View Related

Find Multiple Instances In Multiple Workbooks

Feb 16, 2008

I'm trying to search through a group of Excel files for specific values in column B and then copy and paste the value from column C of the same row into the current worksheet if the search term is found. Thanks to some help from turtle44, I can do that if the search term only appears once, but if it appears more than once, I can only find the first occurance.

I have searched through te archives and found lots of people asking for the same thing. The common suggestion is to use .FindNext, but no matter how I try to use FindNext, it doesn't give me the results I want. It either seems to find the first occurance again or to find nothing at all.

Here is a simplified version of my code, if someone could just explain how to find the second occurance of "Use Code" and past the relevant value into column F of the destination workbook, I'd appreciate it and I should be able to work out the rest on my own.

Sub SearchAllXLSFiles()

Dim lCount As Long
Dim wbSource As Workbook
Dim wbDestiny As Workbook

On Error Resume Next

Set wbDestiny = ThisWorkbook

View 5 Replies View Related

VBA Code To Run Macro In All Open Workbooks

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

Trying To Modify Code To Track Changes In Two Workbooks

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

Code To Work Between Two Open Workbooks

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

VBA Code To Close Inactive Workbooks

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

Copy Event Code To Other Workbooks

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

VBA Code To Copy Data And Reformat It Between Two Workbooks

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







Copyrights 2005-15 www.BigResource.com, All rights reserved