VBA code debug paste special

L

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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
What happens if you add
Rich (BB code): 
With Sheets("2020 Sales list") 
    .Unprotect       
    .Range("A4").PasteSpecial Paste:=xlPasteValues    
End With
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Help with Vba Code
Replies
3
Views
146
Coyotex3
C
C
  • Solved
Automation error The Object invoked has disconnected from its clients.
Replies
7
Views
668
cstuder
C
VinceF
  • Solved
VBA to Copy paste value based on value in cell
Replies
4
Views
300
VinceF
VinceF
G
  • Solved
How do I automatically insert into next blank row..
Replies
2
Views
524
ghrek
G
I
  • Question
Find last row of sheet based off cell value to reference the sheet name
Replies
0
Views
163
ItalianPlatinum
I

Forum statistics

Threads
1,214,570
Messages
6,120,297
Members
448,954
Latest member
EmmeEnne1979

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