Prev: Upload Excel Workbook On SharePoint
Next: 2003 works, 2007 doesn't: "Method 'Select' of object 'Shape'Fail
From: dtoland on 4 Nov 2009 13:16
I'm getting a little frustrated with this simple problem, but don't
have a lot of time to spend reading massive Excel books to figure it
out. I hope someone can help me!
Here's the goal:

To take data from a cell in Sheet1, paste it into a cell in Sheet2.
If the designated cell already contains data, then paste into the next
empty cell in the same column.

Here's where I'm at:

'CURRENT SHEET SELECT CELL G7
Range("G7").Select
'COPY CURRENT CELL
Selection.Copy
'MOVE TO WORKSHEET "REQUEST LOG"
Sheets("REQUEST LOG").Select
'SELECT CELL C3 IF EMPTY, IF NOT, SELECT NEXT EMPTY CELL IN COLUMN
If IsEmpty(Range("C3")) Then
Range("C3").Select
End If
'PASTE COPIED DATA INTO EMPTY CELL OF COLUMN C (this is where the
problem is)
Selection.Paste
From: Patrick Molloy on 4 Nov 2009 13:22
With Sheets("REQUEST LOG").cells(rows.count,"C").End(xlup).Offset(1)
..value = range("G7").Value
End With

the first line say go to the end (bottom) of the column, go uo to the last
cell in that column, then down to the next available


"dtoland" <drewtoland(a)gmail.com> wrote in message
news:d6dbdc52-3061-4654-a939-c24b5d452287(a)d10g2000yqh.googlegroups.com...
> I'm getting a little frustrated with this simple problem, but don't
> have a lot of time to spend reading massive Excel books to figure it
> out. I hope someone can help me!
> Here's the goal:
>
> To take data from a cell in Sheet1, paste it into a cell in Sheet2.
> If the designated cell already contains data, then paste into the next
> empty cell in the same column.
>
> Here's where I'm at:
>
> 'CURRENT SHEET SELECT CELL G7
> Range("G7").Select
> 'COPY CURRENT CELL
> Selection.Copy
> 'MOVE TO WORKSHEET "REQUEST LOG"
> Sheets("REQUEST LOG").Select
> 'SELECT CELL C3 IF EMPTY, IF NOT, SELECT NEXT EMPTY CELL IN COLUMN
> If IsEmpty(Range("C3")) Then
> Range("C3").Select
> End If
> 'PASTE COPIED DATA INTO EMPTY CELL OF COLUMN C (this is where the
> problem is)
> Selection.Paste

From: Rick Rothstein on 4 Nov 2009 13:48
Assuming, as you indicated, that you are running your code from the
ActiveSheet, you can use this single line of code to copy the contents of
the G7 into your REQUEST LOG sheet into the first blank cell starting with
C3 (that is, if C3 is empty, the copy will be into C3; if C3 through, say,
C7 are not empty, then the copy will be into C8)...

Sheets("Sheet8").Columns(3).Find("", Range("C2")).Value = Range("G7").Value

Notice that you do not have to do all that Select'ing to perform this
operation.

--
Rick (MVP - Excel)


"dtoland" <drewtoland(a)gmail.com> wrote in message
news:d6dbdc52-3061-4654-a939-c24b5d452287(a)d10g2000yqh.googlegroups.com...
> I'm getting a little frustrated with this simple problem, but don't
> have a lot of time to spend reading massive Excel books to figure it
> out. I hope someone can help me!
> Here's the goal:
>
> To take data from a cell in Sheet1, paste it into a cell in Sheet2.
> If the designated cell already contains data, then paste into the next
> empty cell in the same column.
>
> Here's where I'm at:
>
> 'CURRENT SHEET SELECT CELL G7
> Range("G7").Select
> 'COPY CURRENT CELL
> Selection.Copy
> 'MOVE TO WORKSHEET "REQUEST LOG"
> Sheets("REQUEST LOG").Select
> 'SELECT CELL C3 IF EMPTY, IF NOT, SELECT NEXT EMPTY CELL IN COLUMN
> If IsEmpty(Range("C3")) Then
> Range("C3").Select
> End If
> 'PASTE COPIED DATA INTO EMPTY CELL OF COLUMN C (this is where the
> problem is)
> Selection.Paste

 | 
Pages: 1
Prev: Upload Excel Workbook On SharePoint
Next: 2003 works, 2007 doesn't: "Method 'Select' of object 'Shape'Fail