2 Files open - Paste to One
 

Hi-

I have a macro saved in a file ("Macro1.xls"). This macro opens a
file.....does it's thing...closes the file....opens the next
file....does it's thing....closes that file.....etc. This goes on for
over 100 files.

I just added a copy and paste feature that grabs cell values from the
"Macro1.xls" file and pastes them into the open file. The problem I
have is the name of these files changes from day to day. Currently I
have this code which works for a file with the specific file name
"Chris.xls":

Windows("Macro1.xls").Activate
Sheets("Sheet5").Select
Rows("1:2").Select
Selection.Copy
Windows("Chris.xls").Activate
Selection.Insert Shift:=xlDown

Can anyone recommend some code that will paste this value in the file
that is open that is not named "Macro1.xls"?? The only two Excel files
that will be open when this macro runs are the macro file and the final
data file.

TIA!

-Chris

2 Files open - Paste to One
 

Dim bk as Workbook
set bk = Workbooks.Open(sName)
Workbooks("Macro1.xls").Worksheets("Sheet5").Range ("1:2").copy
bk.Worksheets(1).Rows(3).Insert Shift:=xldown

Adjust to insert at the appropriate location.

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
Hi-

I have a macro saved in a file ("Macro1.xls"). This macro opens a
file.....does it's thing...closes the file....opens the next
file....does it's thing....closes that file.....etc. This goes on for
over 100 files.

I just added a copy and paste feature that grabs cell values from the
"Macro1.xls" file and pastes them into the open file. The problem I
have is the name of these files changes from day to day. Currently I
have this code which works for a file with the specific file name
"Chris.xls":

Windows("Macro1.xls").Activate
Sheets("Sheet5").Select
Rows("1:2").Select
Selection.Copy
Windows("Chris.xls").Activate
Selection.Insert Shift:=xlDown

Can anyone recommend some code that will paste this value in the file
that is open that is not named "Macro1.xls"?? The only two Excel files
that will be open when this macro runs are the macro file and the final
data file.

TIA!

-Chris

2 Files open - Paste to One
 

Hi Tom-

Thanks for the reply. This code errors on the set bk line. It is
giving me a run-time error 1004.

Just to be clear....the file that is being pasted into is already open
at the point this code is run. Not sure what the (sName) code is above
as well...

Thanks,
Chris

2 Files open - Paste to One
 

That is the point Chris. You say you are processing 100 files, so it
doesn't open itself. When you open it, set a reference to it and use that
reference.

for example, if you were opening all the files in a single directory

Dim bk as Workbook
Dim sName as String
sName = Dir("C:\Myfolder\*.xls")
do while sName < ""
set bk = Workbooks.Open("C:\MyFolder\" & sName)
Workbooks("Macro1.xls").Worksheets("Sheet5").Range ("1:2").copy
bk.Worksheets(1).Rows(3).Insert Shift:=xldown
loop

Adapt if you are working through a list of names.

--
Regards,
Tom Ogilvy



wrote in message
ps.com...
Hi Tom-

Thanks for the reply. This code errors on the set bk line. It is
giving me a run-time error 1004.

Just to be clear....the file that is being pasted into is already open
at the point this code is run. Not sure what the (sName) code is above
as well...

Thanks,
Chris

2 Files open - Paste to One
 

Tom-

Thanks for the help. Everything is running properly. I appreciate the
explanation and the help.

Take Care,
Chris