Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error "1004" on this code, help pls
I have this code:
Private Sub RollUP_Click() Dim newRng As Range Dim newWs As Worksheet Set newWs = Worksheets("ROLLUP") Set newRng = newWs.Range("B:B") For Each Sh In ActiveWorkbook.Worksheets If Sh.Name < newWs.Name Then Sh.Range("G:G").Copy newRng.PasteSpecial xlPasteValues newRng.PasteSpecial xlPasteFormats Sh.Range("lblWTotal").Value = Sh.Name Set newRng = newRng.Offset(0, 1) End If Next Application.ScreenUpdating = False Worksheets("ROLLUP").Columns("B").Hidden = True Application.ScreenUpdating = True End Sub -------- and each time I run it, I get the 1004 error message on this code Sh.Range("lblWTotal").Value = Sh.Name What I'm trying to accomplish is to change the value of a merged cell named "lblWTotal" from each worksheet to the name of the worksheet (ie. 205, 206, 207, 208, etc). Thanks for your help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error "1004" on this code, help pls
I suspecty that you do not have a range named lblWTotal locally defined on
each sheet in your workbook. give this code a try... Private Sub RollUP_Click() Dim newRng As Range Dim newWs As Worksheet Dim rng as Range Set newWs = Worksheets("ROLLUP") Set newRng = newWs.Range("B:B") For Each Sh In ActiveWorkbook.Worksheets If Sh.Name < newWs.Name Then Sh.Range("G:G").Copy newRng.PasteSpecial xlPasteValues newRng.PasteSpecial xlPasteFormats set rng = nothing On error resume next set rng = Sh.Range("lblWTotal") On error goto 0 if rng is nothing then msgbox "Sheet " & sh.name & " does not contain lblWTotal" else Sh.Range("lblWTotal").Value = Sh.Name end if Set newRng = newRng.Offset(0, 1) End If Next Application.ScreenUpdating = False Worksheets("ROLLUP").Columns("B").Hidden = True Application.ScreenUpdating = True End Sub -- HTH... Jim Thomlinson " wrote: I have this code: Private Sub RollUP_Click() Dim newRng As Range Dim newWs As Worksheet Set newWs = Worksheets("ROLLUP") Set newRng = newWs.Range("B:B") For Each Sh In ActiveWorkbook.Worksheets If Sh.Name < newWs.Name Then Sh.Range("G:G").Copy newRng.PasteSpecial xlPasteValues newRng.PasteSpecial xlPasteFormats Sh.Range("lblWTotal").Value = Sh.Name Set newRng = newRng.Offset(0, 1) End If Next Application.ScreenUpdating = False Worksheets("ROLLUP").Columns("B").Hidden = True Application.ScreenUpdating = True End Sub -------- and each time I run it, I get the 1004 error message on this code Sh.Range("lblWTotal").Value = Sh.Name What I'm trying to accomplish is to change the value of a merged cell named "lblWTotal" from each worksheet to the name of the worksheet (ie. 205, 206, 207, 208, etc). Thanks for your help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error "1004" on this code, help pls
Thanks Jim. I now realized what went wrong. You were right, not all
my worksheets contain "lblWTotal" and I forgot to exclude them in the If statement. Thanks for the fast response. Now I can move on to the next problem. :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
runtime error code 1004 | Excel Worksheet Functions | |||
Hiding Rows Error - "Runtime Error 1004 - Unable to set the hidden property of the range class" | Excel Programming | |||
Error Message "Runtime error '13' Type mismatch" | Excel Programming | |||
Runtime Error "1004" Select Method of Range Class Failed | Excel Programming | |||
RunTime Error "1004" QueryTable is Invalid | Excel Programming |