Pasting Data in VBA
 

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

--
Message posted from http://www.ExcelForum.com

Pasting Data in VBA
 

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 :

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.


---
Message posted from http://www.ExcelForum.com/

Pasting Data in VBA
 

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



"Jako " wrote in message
...
| 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.
|
|
| ---
| Message posted from http://www.ExcelForum.com/
|

Pasting Data in VBA
 

William

I can't seem to get this to work

--
Message posted from http://www.ExcelForum.com

Pasting Data in VBA
 

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

"Jako " wrote in message
...
| William
|
| I can't seem to get this to work.
|
|
| ---
| Message posted from http://www.ExcelForum.com/
|

Pasting Data in VBA
 

I am getting error 1004 at this code.

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

Any ideas appreciated

--
Message posted from http://www.ExcelForum.com

Pasting Data in VBA
 

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




"Jako " wrote in message
...
| I am getting error 1004 at this code.
|
| Set r = .Range(.Range("C1"), _
| Range("C" & Rows.Count).End(xlUp))
|
| Any ideas appreciated.
|
|
| ---
| Message posted from http://www.ExcelForum.com/
|

Pasting Data in VBA
 

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

--
Message posted from http://www.ExcelForum.com

Pasting Data in VBA
 

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.)


"Jako <" wrote:

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

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Pasting Data in VBA
 

Works a treat !!!

Many thanks Dave and William

--
Message posted from http://www.ExcelForum.com