LauraC1984
Board Regular
- Joined
- Jan 30, 2020
- Messages
- 63
- Office Version
- 365
- Platform
- 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
I get error code as follows, on the line in yellow
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
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
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: