Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy/paste in the next column
thanks, it works
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and Paste down the column | Excel Programming | |||
Copy/paste Column | Excel Programming | |||
Save column J only using copy/paste & temporary copy | Excel Programming | |||
Copy Cell and Paste in Same Column Only | Excel Programming | |||
Copy Column and Paste | Excel Programming |