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:
Sort by date Sort by votes
Ok, going to take this way back to just to get my head around where we are now.
On a copy of your workbook run the code below and tell me
Where it errors out and what the error says?
What it is doing incorrectly (except any hiding/unhiding of sheets and the save)?
I am expecting both errors and incorrect actions as some bits in the code are guesses.

VBA Code: 
Sub InputWall1()
    '
    ' InputWall1 Macro
    '

    '
    'Sheets("Input").Select
    Sheets("Wall 1").Visible = True
    Sheets("Wall 1").Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Sheets("Input").Range("B2:AB2").Copy
    Sheets("Wall 1").Range("A3").PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
   
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Wall 1").AutoFilter.Sort.SortFields.Clear
    Worksheets("Wall 1").AutoFilter.Sort.SortFields.Add2 Key:=Worksheets("Wall 1").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").Visible = False
    'Sheets("Input").Select
    Sheets("2020 Sales list").Visible = True
    Sheets("Input").Unprotect
    Sheets("2020 Sales list").Unprotect
   
    Sheets("2020 Sales list").Range("A4:L4").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Sheets("Input").Range("B2:G2").Copy
    Sheets("2020 Sales list").Range("A4").PasteSpecial Paste:=xlPasteValues

    Application.CutCopyMode = False
    Sheets("Input").Range("I2:J2").Copy
    Sheets("2020 Sales list").Range("G4").PasteSpecial Paste:=xlPasteValues

    Application.CutCopyMode = False
    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:=Sheets("2020 Sales list").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
    ActiveWorkbook.Worksheets("2020 Sales list").Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
                                                         False
    Sheets("2020 Sales list").Visible = False
 
    Sheets("Calendar").Unprotect
    Sheets("Calendar").Range("AR4:BV866").Copy
    Sheets("Calendar").Range("B4").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Sheets("Calendar").Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
                        False
    'ActiveWorkbook.Save
    Application.Goto Sheets("Calendar").Range("B4")
End Sub
 
Upvote 0

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
morning, ok instead of taking a copy I thought I would make it a bit cleaner, so I copied only the sheets relating to this macro into a new workbook. entered the macro and ran it. lo and behold it works like it should!! from a quick check anyway it all seems to be fine
guess it must be something in my other workbook that is causing the issues, very frustrating!
 
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
669
cstuder
C
VinceF
  • Solved
VBA to Copy paste value based on value in cell
Replies
4
Views
301
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
164
ItalianPlatinum
I

Forum statistics

Threads
1,214,592
Messages
6,120,433
Members
448,961
Latest member
nzskater

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