Help with debugging error '1004' for macros button command

L

lbillie

New Member
Joined
Jun 21, 2013
Messages
28
I'm a newbie to VBA codes. I know I'm going to run into a lot of errors in this macros button. I can run the macros with no problem without it assigned to check boxes or buttons, but once I paste the macros in the code for an active x button; I run into errors. Please help. My code is shown below.
What's highlight is:
Columns ("T:W").Select <---error '1004'

Private Sub CommandButton1_Click()
'
' step1 Macro
'

'
Columns("AI:AP").Select
Range("AI2").Activate
Selection.EntireColumn.Hidden = False
Range("AK158:AK307").Select
Selection.Copy
Range("AL158").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("AJ:AP").Select
Selection.EntireColumn.Hidden = True

Sheets("Parts Tracking").Select
'
' step2 Macro
'
'
Columns("T:W").Select
Selection.EntireColumn.Hidden = False
Range("T5:T154").Select
Selection.Copy
Range("U5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Dim MyRange As Range
Set MyRange = Range("U:U,V:V,W:W")
MyRange.EntireColumn.Hidden = Not MyRange.EntireColumn.Hidden
Application.GoTo Sheets(1).Range("D1"), True
Sheets("Parts Tracking").Select
'
' Pvtupdate Macro
'
'
Range("E5").Select
ActiveWorkbook.RefreshAll
'
' step3 Macro
'
'
Sheets("Quote Comparison").Select
Columns("AI:AP").Select
Range("AI6").Activate
Selection.EntireColumn.Hidden = False
Range("AO158:AO307").Select
Selection.Copy
Range("D158").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Dim QcRange As Range
Set QcRange = Range("AJ:AJ,AK:AK,AL:AL,AM:AM,AN:AN,AO:AO,AP:AP,F:F,G:G,H:H,I:I,J:J,K:K,L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,S:S,T:T,U:U,V:V,W:W")
QcRange.EntireColumn.Hidden = Not QcRange.EntireColumn.Hidden
Application.GoTo Sheets(2).Range("c1"), True

'
' step4 Macro
'
'
Range("AO158:AO307").Select
Selection.Copy
Range("AP158").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AP2").Select
Application.CutCopyMode = False

End Sub
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Try:
Code: 
Private Sub CommandButton1_Click()
'
' step1 Macro
'

Application.ScreenUpdating = False

Columns("AI:AP").EntireColumn.Hidden = False
Range("AK158:AK307").Copy
Range("AL158").PasteSpecial Paste:=xlPasteValues
Columns("AJ:AP").EntireColumn.Hidden = True


Sheets("Parts Tracking").Select
'
' step2 Macro
'
If Columns("T:W").EntireColumn.Hidden Then Columns("T:W").EntireColumn.Hidden = False
Range("T5:T154").Copy
Range("U5").PasteSpecial Paste:=xlPasteValues


Dim MyRange As Range
Set MyRange = Range("U:U,V:V,W:W")
MyRange.EntireColumn.Hidden = Not MyRange.EntireColumn.Hidden
Application.Goto Sheets(1).Range("D1"), True
Sheets("Parts Tracking").Select
'
' Pvtupdate Macro
'
Range("E5").Select
ActiveWorkbook.RefreshAll
'
' step3 Macro
'
Sheets("Quote Comparison").Select
Columns("AI:AP").EntireColumn.Hidden = False
Range("AO158:AO307").Copy
Range("D158").PasteSpecial Paste:=xlPasteValues


Dim QcRange As Range
Set QcRange = Range("AJ:AJ,AK:AK,AL:AL,AM:AM,AN:AN,AO:AO,AP:AP,F:F,G:G,H:H,I:I,J:J,K:K,L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,S:S,T:T,U:U,V:V,W:W")
QcRange.EntireColumn.Hidden = Not QcRange.EntireColumn.Hidden
Application.Goto Sheets(2).Range("C1"), True
'
' step4 Macro
'
Range("AO158:AO307").Copy
Range("AP158").PasteSpecial Paste:=xlPasteValues
Range("AP2").Select

Application.ScreenUpdating = True


End Sub
 
Upvote 0
Thank you, :). Well it passed the
If Columns ("T:W:) EntireColumn ....

Now the Range ("U5").PasteSpecial Paste:=xPasteValues is showing error message.
 
Upvote 0
No prob and now try:
Code: 
Private Sub CommandButton1_Click()
'
' step1 Macro
'
Application.ScreenUpdating = False

Columns("AI:AP").EntireColumn.Hidden = False
Range("AL158:AL307").Value = Range("AK158:AK307").Value
Columns("AJ:AP").EntireColumn.Hidden = True

Sheets("Parts Tracking").Select
'
' step2 Macro
'
If Columns("T:W").EntireColumn.Hidden Then Columns("T:W").EntireColumn.Hidden = False
Range("U5:U154").Value = Range("T5:T154").Value

Dim MyRange As Range
Set MyRange = Range("U:U,V:V,W:W")
MyRange.EntireColumn.Hidden = Not MyRange.EntireColumn.Hidden
Application.Goto Sheets(1).Range("D1"), True
Sheets("Parts Tracking").Select
'
' Pvtupdate Macro
'
Range("E5").Select
ActiveWorkbook.RefreshAll
'
' step3 Macro
'
Sheets("Quote Comparison").Select
Columns("AI:AP").EntireColumn.Hidden = False
Range("D158:D0307").Value = Range("AO158:AO307").Value

Set MyRange = Range("AJ:AJ,AK:AK,AL:AL,AM:AM,AN:AN,AO:AO,AP:AP,F:F,G:G,H:H,I:I,J:J,K:K,L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,S:S,T:T,U:U,V:V,W:W")
MyRange.EntireColumn.Hidden = Not MyRange.EntireColumn.Hidden
Application.Goto Sheets(2).Range("C1"), True
'
' step4 Macro
'
Range("AP158:AP307").Value = Range("AO158:AO307").Value
Range("AP2").Select

Application.ScreenUpdating = True

End Sub
 
Upvote 0
Range("U5:U154").Value = Range("T5:T154").Value

It's still stuck on this line. It's funny how easily it goes through running the macros, but when assigned to an active x button, error messages would pop up.
 
Upvote 0
Range("U5:U154").Value = Range("T5:T154").Value works fine for me in a test sheet. What is the error message given?
 
Upvote 0
It looks like it's trying to run that portion of the macros in the wrong work sheet. it's stating; Run-time error '1004': Cannot enter a null value as an item or field name in a PivotTable report. It's suppose run it in my Parts Tracking worksheet, not my Quote Comparison worksheet. Can I email you my file?
 
Upvote 0
Try this:
Code: 
Private Sub CommandButton1_Click()
' step1 Macro
Dim rng As Range

Application.ScreenUpdating = False

Range("AI:AP").EntireColumn.Hidden = False
Range("AL158:AL307").Value = Range("AK158:AK307").Value
Columns("AJ:AP").EntireColumn.Hidden = True

' step2 Macro
With Sheets("Parts Tracking")
    If .Range("T:W").EntireColumn.Hidden = True Then .Range("T:W").EntireColumn.Hidden = False
    .Range("U5:U154").Value = .Range("T5:T154").Value
    Set rng = .Range("U:W")
    rng.EntireColumn.Hidden = Not rng.EntireColumn.Hidden
    Application.Goto Sheets(1).Range("D1"), True
    .Select
End With

' Pvtupdate Macro
ActiveWorkbook.RefreshAll

' step3 Macro
With Sheets("Quote Comparison")
    .Range("AI:AP").EntireColumn.Hidden = False
    .Range("D158:D0307").Value = .Range("AO158:AO307").Value
    Set rng = .Range("AJ:AP,F:W")
    rng.EntireColumn.Hidden = Not rng.EntireColumn.Hidden
    Application.Goto Sheets(2).Range("C1"), True
End With

' step4 Macro
With ActiveSheet
    .Range("AP158:AP307").Value = .Range("AO158:AO307").Value
End With

Application.ScreenUpdating = True

End Sub
It should run a little quicker than your previous one too.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Increasing row before the macro runs again
Replies
1
Views
188
kbk
K
willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
244
willow1985
willow1985
J
  • Solved
Continuous Copy and paste
2
Replies
10
Views
166
Joe4
Joe4
brent3162
  • Solved
VBA help with increasing row before macro runs again.
Replies
3
Views
343
jolivanes
jolivanes
Dazzawm
  • Solved
Change Range To Recorded Macro
Replies
3
Views
95
Alex Blakenburg
A

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top