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
MARK858 said:
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
Click to expand...

If I skip the line its stopping at and continue the macro then the rest of the macro runs fine
the page is protected but its in the coding to unprotect this
yes still 1004 error
 
Upvote 0

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
I've just noticed as well that the sort on 'wall 1' isn't being carried out, all data is being transferred and the sheet is hidden again but the sort isn't being done
 
Upvote 0
Did you have an merged cells in the range?
and what do you get if you change
VBA Code: 
Sheets("Input").Range("B2:G2").Copy    
With Sheets("2020 Sales list")        
   .Range("A4").PasteSpecial Paste:=xlPasteValues    
End With

to

VBA Code: 
    With Sheets("Input").Range("B2:G2")
        Sheets("2020 Sales list").Range("A4").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
 
Upvote 0
Its now debugging on line
'Sheets("2020 Sales list").Range("K3").AutoFill Destination:=Range("K3:K15"), Type:=xlFillDefault'

its gone past the part you asked me to change, however now instead of it inserting a row its now just copying the data over the top of what ever is in row 4

also it seems to now be inserting 2 lines on the 'wall 1' worksheet but only copying the data once, and it doesn't sort it correctly.
 
Upvote 0
LauraC1984 said:
its gone past the part you asked me to change, however now instead of it inserting a row its now just copying the data over the top of what ever is in row 4
Click to expand...
There was nothing in the last code that was posted in post number 6 to insert a row at that point on the "Input" sheet, the only insert above it inserts on the "wall 1" sheet.
LauraC1984 said:
also it seems to now be inserting 2 lines on the 'wall 1' worksheet but only copying the data once, and it doesn't sort it correctly.
Click to expand...
There is nothing in the code change that I made that affects either of the above.
LauraC1984 said:
Sheets("2020 Sales list").Range("K3").AutoFill Destination:=Range("K3:K15"), Type:=xlFillDefault
Click to expand...
You definitely seem to have an issue with that sheet and to me it is acting as if it is still protected. Post what the error states.

I am going out for a few hours and will have a look again when I get back but in the meantime can you post the code as you currently have it.
 
Upvote 0
This is the latest code - thanks!

Rich (BB 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
    With Sheets("Input").Range("B2:G2")
        Sheets("2020 Sales list").Range("A4").Resize(.Rows.Count, .Columns.Count).Value = .Value
    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
The error is still the 1004 error, it stops on the line highlighted yellow in the last post
1583760136198.png
 
Upvote 0
@LauraC1984 - You did not answer the question about merged cells. If you are trying to copy merged cells and paste them to unmerged cells, or vice versa, you will get error messages. VBA does not like merged cells.
 
Upvote 0
You must log in or register to reply here.

Similar threads

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

Forum statistics

Threads
1,214,431
Messages
6,119,462
Members
448,899
Latest member
maplemeadows

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