Thread
:
copy/paste in the next column
View Single Post
#
3
Posted to microsoft.public.excel.programming
Don Guillett[_2_]
external usenet poster
Posts: 1,522
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
Reply With Quote
Don Guillett[_2_]
View Public Profile
Find all posts by Don Guillett[_2_]