P
Phillips
I would like to create a function that will get a filename from a cell,
then copy worksheets from a different workbook,to a differnt workbook with
a new name.
for example: In Book1 I have 1 sheet. Labeled "SheetstoCopy"
It will have a list of of the sheets I want to copy for workbook2
WS1, WS2,WS3 would be in column A and col B would have OrigFileName1 Col C
would have Workbook2
I would like to call the function, hopefully like WSCopy("WS1",
"OrigFileName1", "WorkBook2")
and what would happen is it would insert a new sheet into WorkBook2, naming
it "OrigFileName1-WS1" (or pass a new name as a parameter)
Then it would select all (including hidden) OrigFileName1.WS1, and then copy
it to Workbook2.OrigFileName1-WS1
HEre is what I have figured out with hard coded names., but I get a
subscript out of range error
I would LIKE to have it so I can pass varibles to it.
Thanks
Phil
Sub CopyWS()
wsName = "Master"
WB1 = "11-27-03.xls"
WB2 = "Testq.xls"
Dim currentws As String
'currentws = ActiveWorksheet.Name
MsgBox wsName
MsgBox WB1
MsgBox WB2
newName = WB2 & "." & wsName
Workbooks(WB1).Worksheets(wsName).Activate
ActiveWorksheet.Copy After:=Worksheets(newName) ' this gives me a subsript
out of range...
'currentws.Activate
End Sub
then copy worksheets from a different workbook,to a differnt workbook with
a new name.
for example: In Book1 I have 1 sheet. Labeled "SheetstoCopy"
It will have a list of of the sheets I want to copy for workbook2
WS1, WS2,WS3 would be in column A and col B would have OrigFileName1 Col C
would have Workbook2
I would like to call the function, hopefully like WSCopy("WS1",
"OrigFileName1", "WorkBook2")
and what would happen is it would insert a new sheet into WorkBook2, naming
it "OrigFileName1-WS1" (or pass a new name as a parameter)
Then it would select all (including hidden) OrigFileName1.WS1, and then copy
it to Workbook2.OrigFileName1-WS1
HEre is what I have figured out with hard coded names., but I get a
subscript out of range error
I would LIKE to have it so I can pass varibles to it.
Thanks
Phil
Sub CopyWS()
wsName = "Master"
WB1 = "11-27-03.xls"
WB2 = "Testq.xls"
Dim currentws As String
'currentws = ActiveWorksheet.Name
MsgBox wsName
MsgBox WB1
MsgBox WB2
newName = WB2 & "." & wsName
Workbooks(WB1).Worksheets(wsName).Activate
ActiveWorksheet.Copy After:=Worksheets(newName) ' this gives me a subsript
out of range...
'currentws.Activate
End Sub