Object Required Error when running Macro

[Donal28]

Hi There

I am trying to run the following macro to copy a data range(A1:HX1) range from one sheet(sheet 6) and past it into the next available blank row in another sheet called New_Overall_Input_File but get the following error when I try and run it......."Object Required"?


Sub ALLCARS()
Sheet6.Range("A1:HX1").Copy
New_Overall_Input_File.Range("D" & Rows.Count).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

Any Help would be much appreciated

Regards
Donal

 

[Andrew Poulsom]

What is New_Overall_Input_File?

Worksheets("New_Overall_Input_File")

 

[snowblizz]

You'd need to formulate this line as follows:
Worksheets("New_Overall_Input_File").Range("D" & Rows.Count).Offset(1, 0).PasteSpecial xlPasteValues

I'm also unsure whether the line with Sheet6 will work. You are referring to the "codename" as opposed to the sheet name.

 

[Donal28]

"New_Overall_Input_File" is the name of the worksheet where I want the data to be pasted to

 

[Donal28]

still doesn't work comes up with the same error

 

[Andrew Poulsom]

You can't offset the last row by one row. Is it?

Worksheets("New_Overall_Input_File").Range("D" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

 

[Donal28]

I changed that line but getting a runtime error 9 "Subscript Out of Range" now

 

[snowblizz]

I changed that line but getting a runtime error 9 "Subscript Out of Range" now

Is it on the first line? You should probably be using Worksheets("Sheet6") there as well.

 

[Donal28]

Yes changed the the 1st line to Worksheets("Sheet6") but still getting error 9 "Subscript Out of Range". The code seems to compile OK

 

[snowblizz]

The error comes from referring to sheets that do not exist or beyond the effective range of the cells.

Can you repost your code with the modifications.