Macro - Opening File and Copying Data

[lawre2000]

Hi, I am new to macros and having difficulty in creating my 1st macro. I am attempting to open 3 seperate files in the same directory and cut and paste data from them into a new file. The data is on specific tabs within each of the 3 files and I would like the information to be cut and paste into specific tabs within the new file.

I started with the macro for the 1st file ("Domestic Price Model") that has data on the tab "Domestic Summary" to be input in the new file ("Book 4v2"). I have some moderate success with the exception that I can not figure out how to select each sheet in the source file to copy from and how to select sheet to paste to. Don't laugh too hard but I have included the code below. PLEASE show me the error of my ways.

Thanks!

Dim ThisPath As String
ThisPath = Range("A6")
FName1 = Range("B6")

Application.DisplayAlerts = False
Set OldWorkbook1 = Workbooks.Open(Filename:=ThisPath & FName1)

Cells.Select
Selection.Copy
Windows("Book4 v2.xls").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E13").Select
Windows("Domestic Price Model.xls").Activate
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Close SaveChanges:=False
'
End Sub

 

[Joe4]

WELCOME TO THE BOARD!

If you use the Macro Recorder, and record yourself performing these steps manually, it should give you the code you are looking for.

 

[lawre2000]

I didn't think you could use macro recorder to open and close files? Thanks

 

[Joe4]

Sure you can. Just open a blank workbook, and turn on the Macro Recorder there. Then you are not opening or closing the workbook that you are recording the macro in.

 

[coasterfreak]

FileName1 = "C:\Temp1"
FileName2 = "C:\Temp2"
Workbooks.Open (FileName1)
Workbooks.Open (FileName2)
this will get two files open then open all u want to work with at once then either use ChDir to go back and forth or use ActiveWorkbook. One thing I do suggest is you ues a line Application.ScreenUpdating = False
in your Sub so it runs faster. Good Luck.