Programiclly copying worksheet to another workbook

[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

 

[mudraker]

variable NewName is set to workbook name & work sheet name
newName = WB2 & "." & wsName

you then try and insert copied sheet after a sheet with this combine
name which does not exist

try

Workbooks(WB1).Worksheets(wsName)..Cop
After:=workbooks(wb2).Worksheets(1