VBA code debug paste special

LauraC1984

Board Regular
Joined
Jan 30, 2020
Messages
63
Office Version
  1. 365
Platform
  1. Windows
Hi
I have a code that is supposed to just paste values only but it keeps getting stopped at this stage.

The code is as follows
Rich (BB code):
Sub InputWall1()
'
' InputWall1 Macro
'

'
Sheets("Input").Select
Sheets("Wall 1").Visible = True
Rows("3:3").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("Input").Select
Range("B2:AB2").Select
Selection.Copy
Sheets("Wall 1").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Wall 1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Wall 1").AutoFilter.Sort.SortFields.Add2 Key:= _
Range("A1:A76"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Wall 1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("Wall 1").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Input").Select
Sheets("2020 Sales list").Visible = True
ActiveSheet.Unprotect
Range("A4:L4").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("Input").Select
Range("B2:G2").Select
Selection.Copy
Sheets("2020 Sales list").Select
Range("A4").Select
ActiveSheet.Unprotect
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False
Sheets("Input").Select
Range("I2:J2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("2020 Sales list").Select
Range("G4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
Sheets("Input").Select
Range("L2:M2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("2020 Sales list").Select
Range("I4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("K3").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("K3:K15"), Type:=xlFillDefault
Range("K3:K15").Select
ActiveWorkbook.Worksheets("2020 Sales list").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("2020 Sales list").AutoFilter.Sort.SortFields.Add2 _
Key:=Range("A1:A93"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("2020 Sales list").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False
Sheets("2020 Sales list").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Calendar").Select
ActiveSheet.Unprotect
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
Range("AR4:BV866").Select
Selection.Copy
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 837
ActiveWindow.ScrollRow = 832
ActiveWindow.ScrollRow = 823
ActiveWindow.ScrollRow = 814
ActiveWindow.ScrollRow = 808
ActiveWindow.ScrollRow = 794
ActiveWindow.ScrollRow = 780
ActiveWindow.ScrollRow = 719
ActiveWindow.ScrollRow = 671
ActiveWindow.ScrollRow = 564
ActiveWindow.ScrollRow = 515
ActiveWindow.ScrollRow = 467
ActiveWindow.ScrollRow = 438
ActiveWindow.ScrollRow = 400
ActiveWindow.ScrollRow = 382
ActiveWindow.ScrollRow = 350
ActiveWindow.ScrollRow = 324
ActiveWindow.ScrollRow = 275
ActiveWindow.ScrollRow = 242
ActiveWindow.ScrollRow = 220
ActiveWindow.ScrollRow = 213
ActiveWindow.ScrollRow = 206
ActiveWindow.ScrollRow = 203
ActiveWindow.ScrollRow = 201
ActiveWindow.ScrollRow = 199
ActiveWindow.ScrollRow = 196
ActiveWindow.ScrollRow = 192
ActiveWindow.ScrollRow = 191
ActiveWindow.ScrollRow = 189
ActiveWindow.ScrollRow = 185
ActiveWindow.ScrollRow = 182
ActiveWindow.ScrollRow = 178
ActiveWindow.ScrollRow = 175
ActiveWindow.ScrollRow = 170
ActiveWindow.ScrollRow = 166
ActiveWindow.ScrollRow = 159
ActiveWindow.ScrollRow = 154
ActiveWindow.ScrollRow = 140
ActiveWindow.ScrollRow = 134
ActiveWindow.ScrollRow = 111
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 1
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False
ActiveWorkbook.Save
Range("B4").Select
End Sub

I get error code as follows, on the line in yellow
1583335211361.png


Also on the data input sheet (sheet named input), it seems to be adding new rows each time I run the formula, and I cannot see why!
Thanks
 
Last edited by a moderator:

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
It probably does not like the space following the := before the line extension symbol. Sinsce you don't need all the default parameters anyway try it this way.
Code:
Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
 

LauraC1984

Board Regular
Joined
Jan 30, 2020
Messages
63
Office Version
  1. 365
Platform
  1. Windows
Morning, thanks. amended this line but it is still bugging out on the same line

also it is still adding in a row on both the input sheet and the '2020 sales list' sheet, any ideas why?
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
see if this works for you.
Code:
Sub InputWall1()
'
' InputWall1 Macro

Sheets("Wall 1").Rows("3:3").Insert Shift:=xlDown
Sheets("Input").Range("B2:AB2").Copy
Sheets("Wall 1").Range("A3").PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
Application.CutCopyMode = False
Sheets("Wall 1").AutoFilter.Sort.SortFields.Clear
Sheets("Wall 1").AutoFilter.Sort.SortFields.Add Key:=Sheets("Wall 1").Range("A1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Wall 1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Sheets("Wall 1").Visible = False
Sheets("2020 Sales list").Visible = True
ActiveSheet.Unprotect
Sheets("Wall 1").Range("A4:L4").Insert Shift:=xlDown
Sheets("Input").Range("B2:G2").Copy
    With Sheets("2020 Sales list")
        .Unprotect
        .Range("A4").PasteSpecial Paste:=xlPasteValues
    End With
Sheets("Input").Range("I2:J2").Copy
Sheets("2020 Sales list").Range("G4").PasteSpecial Paste:=xlPasteValues
Sheets("Input").Range("L2:M2").Copy
Sheets("2020 Sales list").Range("I4").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Sheets("2020 Sales list").Range("K3").AutoFill Destination:=Range("K3:K15"), Type:=xlFillDefault
ActiveWorkbook.Worksheets("2020 Sales list").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("2020 Sales list").AutoFilter.Sort.SortFields.Add2 _
Key:=Range("A1:A93"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("2020 Sales list").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False
Sheets("2020 Sales list").Visible = False
Sheets("Calendar").Unprotect
Range("AR4:BV866").Copy
Range("B4").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False
ActiveWorkbook.Save
Range("B4").Select
End Sub
 

LauraC1984

Board Regular
Joined
Jan 30, 2020
Messages
63
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

still got an error message :(
1583421619018.png

on line .Range("A4").PasteSpecial Paste:=xlPasteValues
if I skip that stage and continue with the macro - the rest works perfectly
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
I did a little more tweaking, see if it runs any smoother now.
VBA Code:
Sub InputWall1()
'
' InputWall1 Macro
Sheets("Wall 1").Rows("3:3").Insert Shift:=xlDown
Sheets("Input").Range("B2:AB2").Copy
Sheets("Wall 1").Range("A3").PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
Application.CutCopyMode = False
Sheets("Wall 1").Sort.SortFields.Clear
Sheets("Wall 1").Sort.SortFields.Add Key:=Sheets("Wall 1").Range("A1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Worksheets("Wall 1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Sheets("Wall 1").Visible = False
Sheets("2020 Sales list").Visible = True
ActiveSheet.Unprotect
Sheets("Wall 1").Range("A4:L4").Insert Shift:=xlDown
Sheets("Input").Range("B2:G2").Copy
    With Sheets("2020 Sales list")
        .Range("A4").PasteSpecial Paste:=xlPasteValues
    End With
Sheets("Input").Range("I2:J2").Copy
Sheets("2020 Sales list").Range("G4").PasteSpecial Paste:=xlPasteValues
Sheets("Input").Range("L2:M2").Copy
Sheets("2020 Sales list").Range("I4").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Sheets("2020 Sales list").Range("K3").AutoFill Destination:=Range("K3:K15"), Type:=xlFillDefault
Sheets("2020 Sales list").UsedRange.AutoFilter
Sheets("2020 Sales list").Sort.SortFields.Clear
Sheets("2020 Sales list").Sort.SortFields.Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Worksheets("2020 Sales list").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False
Sheets("2020 Sales list").Visible = False
Sheets("Calendar").Unprotect
Range("AR4:BV866").Copy
Range("B4").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False
ActiveWorkbook.Save
Range("B4").Select
End Sub
 

LauraC1984

Board Regular
Joined
Jan 30, 2020
Messages
63
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Morning, I have ran this again but its still debugging on line
With Sheets("2020 Sales list")
.Range("A4").PasteSpecial Paste:=xlPasteValues
End With

I have tried changing it to range A3 or A4 too just incase it was the formatting of that line it didn't like but it still does it

is there another way of copy and pasting values only?
Thank you
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,048
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Is it still the same 1004 error? do you have any merged cells or sheet protection?

Are the lines below working correctly?
VBA Code:
Range("AR4:BV866").Copy 
Range("B4").PasteSpecial Paste:=xlPasteValues
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,048
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
What happens if you add
Rich (BB code):
With Sheets("2020 Sales list") 
    .Unprotect       
    .Range("A4").PasteSpecial Paste:=xlPasteValues    
End With
 

Watch MrExcel Video

Forum statistics

Threads
1,129,513
Messages
5,636,780
Members
416,939
Latest member
Rajakumaran

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
Top