Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-Time Error 1004
I agree with Tom and for that reason I avoid using the
copy command and the cut command whenever possible. I instead try have the value in a cell or range = the value in another cell or range. example: thiscell.value = thatcell.value or thisrange.value = thatrange.value you will have to name the target range prior to the command and you may have to do some pre-formatting. Not sure but that seems to cut down the problem. -----Original Message----- Hi, Can anyone please workout why I'm getting a Run-Time Error 1004 (Copy method of worksheet class failed) in code after 30 sheets have been copied? Code works fine up until then. Is there a limit on how many sheets can be copied to new workbook. This is confusing because if your open a new file, it can have up to 255 sheets. All my code is doing is running through a list of names and for each copying a template sheet into a new workbook. I have 50 names which I need to create the same template sheet for each name and store in one new workbook. For some reason can't get past 30 sheets. Appreciate any help with this. Alex XL2003 VBA ' loop While (Ctr) < EmpCtr CrtEmpName = Workbooks(DataFile).Sheets (DataSht).Cells(rCtr, 1).Value ' Master file containing employee names sheet ' copy template to new workbook on first occurance If ShtCtr = 0 Then Sheets(CalcSht).Copy ' Template sheet to copy ActiveWorkbook.Sheets (CalcSht).Activate ' New workbook created with tempate sheet copied CalcFile = ActiveWorkbook.Name ' Else R-T Error -- Sheets(CalcSht).Copy After:=Workbooks (CalcFile).Sheets(ShtCtr) End If ActiveSheet.Name = CrtEmpName Workbooks(DataFile).Sheets(CalcSht).Activate ' Master file containing template sheet rCtr = rCtr + 1 Ctr = Ctr + 1 ShtCtr = ShtCtr + 1 Wend . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run time error 1004 | Excel Discussion (Misc queries) | |||
Run time error 1004! | Excel Discussion (Misc queries) | |||
Run time error 1004, General ODBC error | New Users to Excel | |||
Run-time error '1004' | Excel Programming | |||
Run time error '1004' | Excel Programming |