Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Method 'Copy' of object'_Worksheet failed
Afternoon, new to developing so please go gently on the answers.
I have the following macro to create a number of sheets based on a list, name each sheet as per name on the list and then to copy paste a template onto each sheet. Sounds so (b)loody simple except for a little error message that pops up when its time to copy paste the template: Method 'Copy' of object'_Worksheet failed. The macro looks like this: Dim wstemp As Worksheet Dim Rng As Range Dim ListRng As Range Set wstemp = Worksheets("Template") 'this is the one to copy Set ListRng = Range(Range("B7"), Range("B7").End(xlDown)) For Each Rng In ListRng If Rng.Text < "" Then wstemp.Copy after:=Worksheets(Worksheets.Count) Worksheets(Worksheets.Count).Name = Rng.Text End If Next Rng End Sub And the debug is on this line wstemp.Copy after:=Worksheets(Worksheets.Count) So now to the world I ask for your assistance in sorting this little thorn in my side. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Method 'Copy' of object'_Worksheet failed
Worked fine for me. Have you got another workbook open?
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "q2w3e4r" wrote in message ... Afternoon, new to developing so please go gently on the answers. I have the following macro to create a number of sheets based on a list, name each sheet as per name on the list and then to copy paste a template onto each sheet. Sounds so (b)loody simple except for a little error message that pops up when its time to copy paste the template: Method 'Copy' of object'_Worksheet failed. The macro looks like this: Dim wstemp As Worksheet Dim Rng As Range Dim ListRng As Range Set wstemp = Worksheets("Template") 'this is the one to copy Set ListRng = Range(Range("B7"), Range("B7").End(xlDown)) For Each Rng In ListRng If Rng.Text < "" Then wstemp.Copy after:=Worksheets(Worksheets.Count) Worksheets(Worksheets.Count).Name = Rng.Text End If Next Rng End Sub And the debug is on this line wstemp.Copy after:=Worksheets(Worksheets.Count) So now to the world I ask for your assistance in sorting this little thorn in my side. Thanks -- q2w3e4r |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Method 'MacroOptions' of object'_Application' failed Runtime Error 1004 | Excel Discussion (Misc queries) | |||
Intermittent Error - Method 'Add' of object 'HPageBreaks' failed | Excel Worksheet Functions | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
Copy worksheet & maintain cell reference across worksheets | Excel Worksheet Functions | |||
Copy Function | Excel Discussion (Misc queries) |