Writing a macro and trying to copy several cells from sheet 1 and then
paste into sheet 2 while not pasting over existing data. Is there a
way to auto select the next blank cell in the column in sheet 2 and
paste. Say if A1 is not blank, then auto select A2 instead and so on.

I'm using sheet 1 as the data entry sheet and then sheet 2 to collect
the data for creating charts, etc. Don't want to paste over existing
data.

Tom

Tom

Sub findbottom()
Dim rng1 As Range
Set rng1 = Selection
rng1.Copy Destination:=Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)

Application.CutCopyMode = False
End Sub


Gord Dibben MS Excel MVP

On 27 Jan 2007 16:35:41 -0800, "Tom" wrote:

Writing a macro and trying to copy several cells from sheet 1 and then
paste into sheet 2 while not pasting over existing data. Is there a
way to auto select the next blank cell in the column in sheet 2 and
paste. Say if A1 is not blank, then auto select A2 instead and so on.

I'm using sheet 1 as the data entry sheet and then sheet 2 to collect
the data for creating charts, etc. Don't want to paste over existing
data.

Tom

Range("A" & Rows.Count).End(xlUp).Offset(1,0).Select
then do the paste.

there are two times this will not work properly:
#1 - the column is empty, it will paste at A2 instead of A1
#2 - the column is full and there's no room to paste at all.
#3 - you're so close to the end of the sheet that the paste fails because
there are too many rows to be pasted.

To cover #1 and #2:
On Error Resume Next
Range("A" & Rows.Count).End(xlUp).Offset(1,0).Select
If Err<0 Or Not(IsEmpty(ActiveCell)) Then
Err.Clear
On Error Goto 0
MsgBox "No Room to Paste"
Exit Sub
End If
On Error Goto 0
'continue with paste operation

P.S. for Excel 2007, use Rows.CountLarge instead of Rows.Count

"Tom" wrote:

Writing a macro and trying to copy several cells from sheet 1 and then
paste into sheet 2 while not pasting over existing data. Is there a
way to auto select the next blank cell in the column in sheet 2 and
paste. Say if A1 is not blank, then auto select A2 instead and so on.

I'm using sheet 1 as the data entry sheet and then sheet 2 to collect
the data for creating charts, etc. Don't want to paste over existing
data.

Tom

Also, see Chip Pearson's response to this question which is pretty much the
same one you've just asked:
http://www.microsoft.com/office/comm...9-59691916f5c3

"Tom" wrote:

Writing a macro and trying to copy several cells from sheet 1 and then
paste into sheet 2 while not pasting over existing data. Is there a
way to auto select the next blank cell in the column in sheet 2 and
paste. Say if A1 is not blank, then auto select A2 instead and so on.

I'm using sheet 1 as the data entry sheet and then sheet 2 to collect
the data for creating charts, etc. Don't want to paste over existing
data.

Tom

If it is possible that the cell in the first Column a of the selection is empty then look here
http://www.rondebruin.nl/copy1.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Gord Dibben" <gorddibbATshawDOTca wrote in message ...
Tom

Sub findbottom()
Dim rng1 As Range
Set rng1 = Selection
rng1.Copy Destination:=Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)

Application.CutCopyMode = False
End Sub


Gord Dibben MS Excel MVP

On 27 Jan 2007 16:35:41 -0800, "Tom" wrote:

Writing a macro and trying to copy several cells from sheet 1 and then
paste into sheet 2 while not pasting over existing data. Is there a
way to auto select the next blank cell in the column in sheet 2 and
paste. Say if A1 is not blank, then auto select A2 instead and so on.

I'm using sheet 1 as the data entry sheet and then sheet 2 to collect
the data for creating charts, etc. Don't want to paste over existing
data.

Tom

Below is what I tried to run but I get a compile error: Sub or
Function not defined at line 6 Lr =

Sub copy_1_Values_PasteSpecial()
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long
Application.ScreenUpdating = False
Lr = LastRow(Sheets("Log")) + 1
Set sourceRange = Sheets("Temp").Range("C7:W12")
Set destrange = Sheets("Log").Range("C" & Lr)
sourceRange.Copy
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

On Jan 27, 8:15 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
Also, see Chip Pearson's response to this question which is pretty much the
same one you've just asked:http://www.microsoft.com/office/comm....mspx?dg=micro...



"Tom" wrote:
Writing a macro and trying to copy several cells from sheet 1 and then
paste into sheet 2 while not pasting over existing data. Is there a
way to auto select the next blank cell in the column in sheet 2 and
paste. Say if A1 is not blank, then auto select A2 instead and so on.

I'm using sheet 1 as the data entry sheet and then sheet 2 to collect
the data for creating charts, etc. Don't want to paste over existing
data.

Tom- Hide quoted text -- Show quoted text -

that is because your code is looking for a separate function named lastrow.
If you don't supply one, then you can just use

Sub copy_1_Values_PasteSpecial()
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long
Application.ScreenUpdating = False
Lr = Sheets("Log").Cells(rows.count,"C").End(xlup).row + 1
Set sourceRange = Sheets("Temp").Range("C7:W12")
Set destrange = Sheets("Log").Range("C" & Lr)
sourceRange.Copy
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy


"Tom" wrote in message
oups.com...
Below is what I tried to run but I get a compile error: Sub or
Function not defined at line 6 Lr =

Sub copy_1_Values_PasteSpecial()
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long
Application.ScreenUpdating = False
Lr = LastRow(Sheets("Log")) + 1
Set sourceRange = Sheets("Temp").Range("C7:W12")
Set destrange = Sheets("Log").Range("C" & Lr)
sourceRange.Copy
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

On Jan 27, 8:15 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
Also, see Chip Pearson's response to this question which is pretty much
the
same one you've just
asked:http://www.microsoft.com/office/comm....mspx?dg=micro...



"Tom" wrote:
Writing a macro and trying to copy several cells from sheet 1 and then
paste into sheet 2 while not pasting over existing data. Is there a
way to auto select the next blank cell in the column in sheet 2 and
paste. Say if A1 is not blank, then auto select A2 instead and so on.

I'm using sheet 1 as the data entry sheet and then sheet 2 to collect
the data for creating charts, etc. Don't want to paste over existing
data.

Tom- Hide quoted text -- Show quoted text -