Copy sheets from one workbook to another

[Tinhare]

Hi all,
I am trying to copy some sheets from one workbook into another. It works if I specify a destination workbook name but the name of the destination workbook will change depending on the week of the year. This name is stored in a variable. When i use the variable in the copy the VBA code falls over.

The code I am using is similar to this :-

Sheets(Array("Sheet1","Sheet2")).Copy Before:=Workbooks("Workbook2").Sheets (3)

What I want to be able to do is replace workbook to with a variable. Something like:-

WrkBook2="Week 2"

Sheets(Array("Sheet1","Sheet2")).Copy Before:=Workbooks(Wrkbook2).Sheets (3)

Thanks for any help
Alan

 

[Jay Petrulis]

Hi,

Define your string with the .xls extension.

Sub tester()

Dim Wrkbook2 As String

Wrkbook2 = "Week 2.xls"
Sheets(Array("Sheet1", "Sheet2")).Copy Before:=Workbooks(Wrkbook2).Sheets(3)

End Sub

worked for me.

If you are going to prompt for the name of the destination workbook by using .GetOpenFilename or similar, you will have to convert the string by removing the full path before continuing. Disregard this if not an issue.

Bye,
Jay

 

[Tinhare]

Thanks for that. It was a bit of user error on my part Doh!. I wonderedwhy it wouldn't work as I had also dimensioned a string for the workbook name.

Regards,
Alan