How can i add paste values to this vba code?

[JumboCactuar]

Dim Last_Row As Long
Last_Row = Range("A1").End(xlDown).Offset(1).Row
Range("A1:N1").Copy Range("A" & Last_Row)
Sheets("Sheet1").Select
Range("A41").Select

I want to paste the values at A41 on sheet 1

I tried the below but it errors:

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False

Thank-you

 

[JLGWhiz]

Dim Last_Row As Long
Last_Row = Range("A1").End(xlDown).Offset(1).Row
Range("A1:N1").Copy Range("A" & Last_Row)
Sheets("Sheet1").Select
Range("A41").Select

I want to paste the values at A41 on sheet 1

I tried the below but it errors:

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False

Thank-you

Dim Last_Row As Long
Last_Row = Range("A1").End(xlDown).Offset(1).Row
Range("A1:N1").Copy 
Range("A" & Last_Row).PasteSpeccial xlPasteValues
Sheets("Sheet1").Select
Range("A41").Select

 

[JumboCactuar]

Dim Last_Row As Long
Last_Row = Range("A1").End(xlDown).Offset(1).Row
Range("A1:N1").Copy 
Range("A" & Last_Row).PasteSpeccial xlPasteValues
Sheets("Sheet1").Select
Range("A41").Select

Thanks for the reply, ill try it shortly

It looks like the paste special is pasting over original rather data? Instead of pasting at A41 on sheet1

 

[JLGWhiz]

This will put it in A41, my first post was just to show how to use the paste special.

Dim Last_Row As Long
Last_Row = Range("A1").End(xlDown).Offset(1).Row
Range("A1:N1").Copy 
Sheets("Sheet1").Range("A41").PasteSpeccial xlPasteValues

 

[JumboCactuar]

This will put it in A41, my first post was just to show how to use the paste special.

Dim Last_Row As Long
Last_Row = Range("A1").End(xlDown).Offset(1).Row
Range("A1:N1").Copy 
Sheets("Sheet1").Range("A41").PasteSpeccial xlPasteValues

I tried this and now it just copies the first row?

 

[MARK858]

What do you mean now it only copies the first row?
That is all the Range("A1:N1") does in the code you posted in post #1 does.

 

[JumboCactuar]

What do you mean now it only copies the first row?
That is all the Range("A1:N1") does in the code you posted in post #1 does.

yes a mistake in my coding but with this line:

with me having this line i thought people would know what im trying to do
Last_Row = Range("A1").End(xlDown).Offset(1).Row


i simply want all data in columns A:N copied to sheet 1

 

[MARK858]

i thought people would know what im trying to do

You are using the line you posted as the destination sheets variable not part of the copy range and so no people aren't psychic and so don't know what you are trying to do without you actually stating it.

What exactly are you trying to do?
Are you trying to copy the range A1 to N where column A is the longest column? or are you basing the last row on another longest column ? or are you basing ithe last row on the longest column whichever column it is within the range?

Then are you trying to paste it in the next row based on the next empty cell in column A in the destination sheet?

Finally what are both sheets names?

 

[JumboCactuar]

You are using the line you posted as the destination sheets variable not part of the copy range and so no people aren't psychic and so don't know what you are trying to do without you actually stating it.

What exactly are you trying to do?
Are you trying to copy the range A1 to N where column A is the longest column? or are you basing the last row on another longest column ? or are you basing ithe last row on the longest column whichever column it is within the range?

Then are you trying to paste it in the next row based on the next empty cell in column A in the destination sheet?

Finally what are both sheets names?

Sheet 2
Columns A to N have data but row count varies.
So copy from A1 down to N(last row)

Then paste values on Sheet 1 at A41

This is what i had to start with to ensure i grabbed it all but i just want a way to not select the blanks

Sheets("Sheet2").Select
Range("A1:N1000").Copy
Sheets("Sheet1").Select)
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False

 

[MARK858]

Columns A to N have data but row count varies.

I will try again... what determines how the row count varies...

Are you trying to copy the range A1 to N where column A is the longest column? or are you basing the last row on another longest column ? or are you basing the last row on the longest column whichever column it is within the range?

Be specific on how the longest row is determined.