Getting and Extra Worksheet

[robsimons]

I have this code...

[VBA]Set oWB = Workbooks.Open(Me.txt_HCSA_oldFilename.Value)
For Each wSht In oWB.Worksheets
If Range("A1").Value <> "" Then
oWB.Worksheets.Copy 'after:=oThis.Worksheets(oThis.Worksheets.Count)
oThis.Sheets(wSht).Name = "Sheet1"
oWB.Close SaveChanges:=False

End If
Next wSht[/VBA]

And I'm really trying to copy a worksheet from 1 excel file to the open excel file and rename it to "sheet1".

However when I run this it does the copy just fine but it also creates a BLANK worksheet with the name "Sheet1" in the open excel file. and I get a error when I try to rename.

Why, What's wrong?

Thanks,

[rory]

This line:
[VBA]oWB.Worksheets.Copy[/VBA]
copies all the worksheets to a new workbook for each worksheet in the existing book, since you didn't specify a single worksheet to copy or a destination.
This line:
[VBA]oThis.Sheets(wSht).Name = "Sheet1"[/VBA]
will fail because wSht is a worksheet object, not an index number or the name of a sheet.

[robsimons]

thanks this did help a little.