Prev: Excel 2007 returns data as 2007 table instead of range - how to turn off.
Next: How to reference a cell into a Dialog Box ?
From: Wes_A on 7 Mar 2010 22:04
Excel 2007 running on XP Pro:
I am trying to copy rows in a range of rows from sheet 1 to sheet 2.
I only want to copy & paste those rows on sheet 1 that contain a value (as
apposed to a formula or formatting) in the first cell of each row.
Note that the rows may have blank cells between values on the row.
These rows should be selected and pasted as values only into sheet 2
(including those cells in the selected rows that are blanks.)
The new data should be pasted immediately following any that were previously
pasted there.
Can anyone assist?
From: JLGWhiz on 7 Mar 2010 22:31
This was my interpretation of the post:

Sub copyStuff()
Dim lr As Long, sh As Worksheet, sh2 As Worksheet
Dim c As Range, rng As Range, lr2 As Long
Set sh = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("A2:A" & lr)
For Each c In rng
If c.Value <> "" And c.Value > 0 Then
lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
c.EntireRow.Copy
sh2.Range("A" & lr2 + 1).PasteSpecial Paste:=xlPasteValues
End If
Next
Application.CutCopyMode = False
End Sub




"Wes_A" <WesA(a)discussions.microsoft.com> wrote in message
news:66A888A5-8913-41F1-88D3-F2A9CCCEF3D3(a)microsoft.com...
> Excel 2007 running on XP Pro:
> I am trying to copy rows in a range of rows from sheet 1 to sheet 2.
> I only want to copy & paste those rows on sheet 1 that contain a value (as
> apposed to a formula or formatting) in the first cell of each row.
> Note that the rows may have blank cells between values on the row.
> These rows should be selected and pasted as values only into sheet 2
> (including those cells in the selected rows that are blanks.)
> The new data should be pasted immediately following any that were
> previously
> pasted there.
> Can anyone assist?


From: OssieMac on 7 Mar 2010 23:25
My interpretation here is that you want to include any rows where the column
A cell contains a value but not if it contains a Formula or Formatting. (I
interpretted the Formatting as Number formatting.)

If my interpretation is correct then try the following. I kept the If/EndIf
tests separate so they are easy to delete if you don't want some of the tests.

Sub CopyData()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rngColA As Range
Dim c As Range

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

With ws1
'Following assumes column headers and
'data starts on row 2.
Set rngColA = .Range(.Cells(2, "A"), _
.Cells(.Rows.Count, "A").End(xlUp))
End With

For Each c In rngColA
'Test if empty cell
If IsEmpty(c.Value) Then
GoTo endForEach 'Empty cell column A so skip
End If

'Test number format
If c.NumberFormat <> "General" Then
GoTo endForEach 'Formatted so skip
End If

'Test for formula
If Left(c.Formula, 1) = "=" Then
GoTo endForEach 'Is formula so skip
End If

c.EntireRow.Copy
ws2.Cells(Rows.Count, "A") _
.End(xlUp).Offset(1, 0) _
.PasteSpecial Paste:=xlPasteValues

endForEach:
Next c
Application.CutCopyMode = False
ws2.Select
Range("A1").Select
End Sub


--
Regards,

OssieMac


 | 
Pages: 1
Prev: Excel 2007 returns data as 2007 table instead of range - how to turn off.
Next: How to reference a cell into a Dialog Box ?