Try changing this:

"Set destrange1 = Sheets("sheet2").Range(2, LastCol)"

To this:

"Set destrange1 = Sheets("sheet2").Cells(2, LastCol + 1)"


You had a syntax boo boo. Note also the added + 1 to your LastCol value.
That will move the starting poing to the next empty column. Otherwise you
would just overwrite the same column continuously.



"Nicawette" wrote in message
...
Hi
I am not a specialist in VBA but I have created a macro to copy a
block of cells from sheet1 and add these data in the first available
column ( e.g. without data) in sheet 2, starting always with the same
row.
The code below revert with a 1004 run time error for this line of code
"Set destrange1 = Sheets("sheet2").Range(2, LastCol)"

s.o. can help me to correct the code?

Thank you

code :

Sub copy_result()
Dim sourceRange As Range
Dim destrange1 As Range
Dim LastCol As Long


' search the last column in sheet2

LastCol = Sheets("sheet2").Cells.Find(What:="*",
SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column

' the data to copy are located in cells A1:D9 of sheet1

Set sourceRange = Sheets("sheet1").Range("A1:D9")

' The data should be copied in the first column available of sheet 2
and the row will be always row 2

Set destrange1 = Sheets("sheet2").Range(2, LastCol)
sourceRange.Copy
destrange1.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
destrange1.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
_
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("sheet1").Select
Range("A1").Select
End Sub