copy/paste in the next column
 

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

copy/paste in the next column
 

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

Change to........

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


Gord Dibben MS Excel MVP

On Fri, 29 Jan 2010 15:06:15 -0800 (PST), Nicawette
wrote:

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

copy/paste in the next column
 

You had 2 mistakes. Next avail column should be +1 and range(2,lc) needed to
be CELLS(2,LC). Try this to copy the values in sheet1!a9 to the next avail
col in sheet 2

Option Explicit
Sub copy_resultSAS()
Dim LC As Long
Dim ds As Worksheet
Dim ss As Worksheet
Set ds = Sheets("sheet2")
Set ss = Sheets("sheet1")
LC = ds.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column + 1
'data to copy is located in sheet1!A1:D9
ds.Cells(2, LC).Resize(9, 4).Value = _
ss.Cells(1, 1).Resize(9, 4).Value
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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

copy/paste in the next column
 

thanks, it works

copy/paste in the next column
 

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