Macro using cell reference to get the file path then looping

September 15, 2013 at 16:09:06
Specs: Windows 7
I am looking for some help in VBA (Macro). What I am trying to is to open up Workbook 1/ sheet 1 in “C:\My Documents\Workstack Reports \Workbook1” , then open Workbook 1A/Drop, in “C:\My Documents \Workstack Masters\Workbook1A” and do copy and pasting between them and then close and save,

I have worked it out but a long winded way as I have inputted all the file paths but there is a problem with that is I am the only one that can rum the macro so what I was looking for is to use a reference sheet with the file path in cell “A1” for Workbook1 and cell “A2” for Workbook2 then “A3” for Workbook3 and so until it have finished and do the same for the Workbook 1A by using “B1” then “B2” for Workbook 2A and so
I guess I am looking on how to do a loop but using reference cells to get the file path

This is the Macro that i am using

Workbooks.Open ("C:\My Documents\Workstack Reports\Workstack1.xlsx")
Application.DisplayAlerts = False
Selection.Copy
Sheets("Sheet1").Select
Range("A1:AP5000").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Open ("C:\Users\My Documents\Workstack Reports Master\Workstack 1A.xlsx")
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
Columns("A:AT").Hidden = False
Sheets("Drop").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Drop").Select
Columns("A:AT").Select
Selection.Copy
Sheets("Master").Select
Range("A1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select

ActiveWorkbook.Close SaveChanges:=True
Windows("Workstack 1A.xlsx").Activate
ActiveWindow.Close

Workbooks.Open ("C:\My Documents\Workstack Reports\Workstack2.xlsx")
Application.DisplayAlerts = False
Selection.Copy
Sheets("Sheet1").Select
Range("A1:AP5000").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Open ("C:\Users\My Documents\Workstack Reports Master\Workstack 2A.xlsx")
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
Columns("A:AT").Hidden = False
Sheets("Drop").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Drop").Select
Columns("A:AT").Select
Selection.Copy
Sheets("Master").Select
Range("A1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select

ActiveWorkbook.Close SaveChanges:=True
Windows("Workstack 2A.xlsx").Activate
ActiveWindow.Close

Hope this makes sense the macro is no run for both of the workbooks but a separate workbook that will contain the cell that will reference the file paths along with the loop. so I am looking for is once the workbooks are open is stay open and a loop to refernce the workbook1 and 1A in each filepath and keep downing that till the workbooks are finished
Hope you will be able to see what I am looking for and be able to help

Thanks Tom


See More: Macro using cell reference to get the file path then looping

Report •

#1
September 19, 2013 at 17:47:32
First, please read the instructions found by clicking on the following line and then repost your code using the pre tags. It is much easier to read if the indents are retained.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question