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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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

P
  • Solved
Counter in For Each loop
Replies
2
Views
197
Possum17
P
P
  • Solved
Need help with macro Dynamic Range??
Replies
6
Views
173
pennbowl
P
A
  • Question
Macro to update list to last row on another sheet copy source from multiple sheets
Replies
0
Views
308
Azamamir
A
P
  • Question
Help with Vba Code
Replies
3
Views
160
Coyotex3
C
D
  • Question
VBA optimization advice
Replies
3
Views
97
bobsan42
bobsan42

Forum statistics

Threads
1,214,965
Messages
6,122,500
Members
449,090
Latest member
RandomExceller01

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