Runtime error 1004 from a macro

[euan_444]

Hi

I am a beginner at VB code and have been playing around by recording small macros to do tasks and then pasting them together and adapting to complete the task. I have created a macro that using an advanced filter will sort the raw data using 8 different parameters and after each filter column s is selected and copied and pasted into another sheet in the same workbook to then be summed by formula in another sheet in a table.

I am sure there would be a better way to do this but for my purposes this will suffice, however when i created the macro and tested all seemed fine but once the macro was linked to a button i now get the run time error 1004 the information cannot be pasted because the copy area and paste area are not the same size. The copy is of a whole column as again i was unsure of how to capture just the data as the amount could change each time and the paste is into 1 cell reference. Please see the code below.

Sheets("rhd").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Sheets("Sheet1").Select
Rows("8:8").Select
Selection.Copy
Sheets("rhd").Select
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False 'BOOKER
Range("A1:AF1000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Sheet1").Range("A1:A2"), Unique:=False
Columns("S:S").Select
Selection.Copy
Sheets("Sheet1").Select
Range("A10").Select
ActiveSheet.Paste
Sheets("rhd").Select
Application.CutCopyMode = False
ActiveSheet.ShowAllData
Range("A1").Select 'CHESS PLASTICS
Range("A1:AF1000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Sheet1").Range("B1:B2"), Unique:=False
Columns("S:S").Select
Selection.Copy
Sheets("Sheet1").Select
Range("B10").Select
ActiveSheet.Paste
Range("E17").Select
Sheets("rhd").Select
Range("A1").Select
Application.CutCopyMode = False
ActiveSheet.ShowAllData
Range("A1").Select
Range("A1:AF1000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Sheet1").Range("C1:C2"), Unique:=False
Columns("S:S").Select
Selection.Copy
Sheets("Sheet1").Select
Range("C10").Select
ActiveSheet.Paste
Sheets("rhd").Select
Application.CutCopyMode = False
ActiveSheet.ShowAllData
Range("A1").Select
Range("A1:AF1000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Sheet1").Range("D1:D2"), Unique:=False
Columns("S:S").Select
Selection.Copy
Sheets("Sheet1").Select
Range("D10").Select
ActiveSheet.Paste
Sheets("rhd").Select
Application.CutCopyMode = False
ActiveSheet.ShowAllData
Range("A1").Select
Range("A1:AF1000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Sheet1").Range("E1:E2"), Unique:=False
Columns("S:S").Select
Selection.Copy
Sheets("Sheet1").Select
Range("E10").Select
ActiveSheet.Paste
Sheets("rhd").Select
Application.CutCopyMode = False
ActiveSheet.ShowAllData
Range("A1").Select
Range("A1:AF1000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Sheet1").Range("F1:F2"), Unique:=False
Columns("S:S").Select
Selection.Copy
Sheets("Sheet1").Select
Range("F10").Select
ActiveSheet.Paste
Sheets("rhd").Select
Application.CutCopyMode = False
ActiveSheet.ShowAllData
Range("A1").Select
Range("A1:AF1000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Sheet1").Range("G1:G2"), Unique:=False
Columns("S:S").Select
Selection.Copy
Sheets("Sheet1").Select
Range("G10").Select
ActiveSheet.Paste
Sheets("rhd").Select
Application.CutCopyMode = False
ActiveSheet.ShowAllData
Range("A1").Select
Range("A1:AF1000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Sheet1").Range("H1:H2"), Unique:=False
Columns("S:S").Select
Selection.Copy
Sheets("Sheet1").Select
Range("H10").Select
ActiveSheet.Paste
Sheets("rhd").Select
Application.CutCopyMode = False
ActiveSheet.ShowAllData

End Sub

 

[Trevor G]

Welcome to the Forum,

If the issue is about copying a range that varies try using currentregion, what you do is point to one cell then use the currentregion as it selects all the cells in the table no mater what the size.

Sample1

Range("b3").select
activecell.currentregion.copy

Sample2
Range("b3").select
activecell.currentregion.Select