copy to hidden sheet

[bob]

the following code will copy the source to data sheet if it is the first
postition on destination but i get an error if i hide the temporary data
sheet
how do i copy to a hidden sheet

ActiveWorkbook.Windows(1).Caption = "Book1"

'delete data sheet each time before copy
Application.DisplayAlerts = False 'trun off del warning
Windows("test1.xls").Activate

Sheets("Data").Select

ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True 'trun warning back on
Windows("Book1").Activate

' COPY to test1 workbook & Data sheet, place at beginning
ActiveWorkbook.ActiveSheet.Copy Before:=Workbooks("test1.xls").Sheets(1)
' Rename first sheet
Workbooks("test1.xls").Sheets(1).Name = "Data"

 

[Bob Phillips]

Bob,

I don't see too much copying going on in your code, just a sheet delete.

The problem is trying to select a hidden sheet, as select is giving it
focus, which doesn't add up if it's hidden. You can do it without selecting
by changing these lines

Sheets("Data").Select
ActiveWindow.SelectedSheets.Delete

to

Worksheets("Data").Delete


So where does copy come into it?

 

[Tom Ogilvy]

The sheet to be copied is always assumed to be the activesheet in Book1 in
your code. You give no other information about it. The solution would be
to refer to it specifically without assuming it is the activesheet - but you
don't provide enough information to give an example.

Regards,
Tom Ogilvy