Object Required VBA

[jamesmev]

I am attempting to merge multiple sheets together using this code:

Sub Combine()  Dim lst As Long 'combines order form into winshuttle ready combinedOE
     Application.ScreenUpdating = False


   Sheets(1).Copy Sheets(1)
  With Sheets(1).Name
     lst = .Range("I" & Rows.Count).End(xlUp).Row + 1
    .Range("A" & lst).PasteSpecial xlPasteColumnWidths
    .Range("A" & lst).PasteSpecial xlPasteValues


End With
   With Sheets(1).UsedRange
      .ColumnWidth = 22
      .RowHeight = 18
      Application.ScreenUpdating = True
      Application.DisplayAlerts = True
      
   End With
End Sub

However I am getting object required error.
I can write this to where it copies and pastes over the first blank cell in row A. However, I need it to paste over the first blank cell after A2 in column J. (Since A2 will always be blank)

please help.

 

[RoryA]

This:

With Sheets(1).Name

should be just:

With Sheets(1)

 

[jamesmev]

This:

With Sheets(1).Name

should be just:

With Sheets(1)

I am now getting Run-Time Error '1004':
PasteSpecial Method of Range class failed.

here:

.Range(A") &lst).PasteSpecial xlPasteColumnWidths

 

[RoryA]

You just copied a worksheet, not a range, so you can't paste. What are you actually trying to do?

 

[Joe4]

I am now getting Run-Time Error '1004':
PasteSpecial Method of Range class failed.

here:

[COLOR=#333333].Range(A") &lst).PasteSpecial xlPasteColumnWidths[/COLOR]

Also, this would not be correct syntax anyway.

[COLOR=#333333].Range(A") &lst)[/COLOR]

should be:

[COLOR=#333333].Range("A" & lst)[/COLOR]

like you had in your original post.

 

[jamesmev]

I am combining workbooks from a network source.
The worksheets starts on A2. when merging it is overwriting all the data of the other records if A is blank.

here is the format of what should be copied and pasted.

A B C D E F G H I J
1 DATA DATA DATA DATA DATA DATA DATA DATA
2 DATA DATA
3 DATA DATA
4 DATA DATA
5 DATA DATA
6 DATA DATA

The next copied sheet should provide the same format.
However, it is pasting over the Data in I & J because A2 is blank.