PDA

View Full Version : Getting and Extra Worksheet



robsimons
11-08-2007, 09:25 AM
I have this code...

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

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
11-08-2007, 04:34 PM
This line:
oWB.Worksheets.Copy
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:
oThis.Sheets(wSht).Name = "Sheet1"
will fail because wSht is a worksheet object, not an index number or the name of a sheet.

robsimons
11-09-2007, 07:07 AM
thanks this did help a little.