View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
Don Guillett[_2_] Don Guillett[_2_] is offline
external usenet poster
 
Posts: 1,522
Default 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