Pasting Data in VBA

J

Jako

I have a Worksheet "Audit" filled with many Rows of data.

Not all cells contain text (Some cells are empty) but Column C wil
always have text in it.

I want to select and paste all rows (running A to J) to a WorkShee
"Temp" that have a string of text "John Miles".
The WorkSheet "Temp" will already have previous data in it so i want t
copy the Rows to the next empty Row.

Then on WorkSheet "Temp" i want to count how many Rows there ar
containing the name "John Miles". Then i also want to count how man
Rows have an empty cell in Column A.

Please can someone advise me of how to do this.

Thanks in advance
 
K

keepITcool

Use Auto or Advanced Filter.
Then use Goto Special Visible cells..
Copy
activate the temp sheet
pastespecial with skipblanks checked.

Now do the same with macro recorder running..

Then edit your code to make your cell references more flexible.
search this news group for lastcell or last empty row

I could ofcourse have given you the full code..
but alas.. you wouldn't learn anything..

if you're halfway and cant get it to work..
post back with what you've got and I'll help you along..


hth

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Jako wrote :
 
W

William

Jacko

I'm assuming the name John Miles can be in any of the cells in columns A to
J, not just column C.

Sub test()
Application.ScreenUpdating = False
Dim r As Range, c As Range, i As Long, j As Long
Dim r1 As Range, r2 As Range
i = 0
j = 0
With Sheets("Audit")
Set r = .Range(.Range("C1"), _
..Range("C" & Rows.Count).End(xlUp))
For Each c In r
Set r1 = Application.Intersect(c.EntireRow, _
..Range("A:J"))
If Application.CountIf(r1, "John Miles") > 0 Then
If r2 Is Nothing Then
Set r2 = r1
Else
Set r2 = Union(r2, r1)
End If
End If
Next c
End With
If r2 Is Nothing Then Exit Sub
r2.Copy Sheets("Temp").Range("C65000").End(xlUp).Offset(1, -2)
With Sheets("Temp")
Set r = .Range(.Range("C1"), _
..Range("C" & Rows.Count).End(xlUp))
For Each c In r
If Trim(c.Offset(0, -2)) = "" Then j = j + 1
Set r1 = Application.Intersect(c.EntireRow, _
..Range("A:J"))
If Application.CountIf(r1, "John Miles") > 0 Then i = i + 1
Next c
End With
Application.ScreenUpdating = True
MsgBox "In the 'Temp' sheet there are " & i & " rows " & _
"containing the name 'John Miles' and " _
& j & " blank cells in Column A."
End Sub


--
XL2002
Regards

William

[email protected]

| I have a Worksheet "Audit" filled with many Rows of data.
|
| Not all cells contain text (Some cells are empty) but Column C will
| always have text in it.
|
| I want to select and paste all rows (running A to J) to a WorkSheet
| "Temp" that have a string of text "John Miles".
| The WorkSheet "Temp" will already have previous data in it so i want to
| copy the Rows to the next empty Row.
|
| Then on WorkSheet "Temp" i want to count how many Rows there are
| containing the name "John Miles". Then i also want to count how many
| Rows have an empty cell in Column A.
|
| Please can someone advise me of how to do this.
|
| Thanks in advance.
|
|
| ---
|
|
 
P

Pete

Jako

" I can't seem to get this to work."

That doesn't give me too much to work on I'm afraid - why doesn't it work?
--

Regards

Pete

| William
|
| I can't seem to get this to work.
|
|
| ---
|
|
 
J

Jako

I am getting error 1004 at this code.

Set r = .Range(.Range("C1"), _
Range("C" & Rows.Count).End(xlUp))

Any ideas appreciated
 
W

William

Jako

There is a full stop at the beginning of the word "Range" - it should
read.....

Set r = .Range(.Range("C1"), _
..Range("C" & Rows.Count).End(xlUp))

--
XL2002
Regards

William

[email protected]


| I am getting error 1004 at this code.
|
| Set r = .Range(.Range("C1"), _
| Range("C" & Rows.Count).End(xlUp))
|
| Any ideas appreciated.
|
|
| ---
|
|
 
J

Jako

Thanks William but when i put in the full stops i get an error syain
"Expected identifier or bracketed expression"
 
D

Dave Peterson

I cut the offending lines and pasted them to a new procedure. I put the logical
line on one physical line and the snipped worked ok for me:

Dim r As Range
With Worksheets("sheet1")
Set r = .Range(.Range("C1"), .Range("C" & .Rows.Count).End(xlUp))
End With

(Ps. I like a dot in front of .rows.count, so I added it. It isn't really
necessary.)
 
Top