Copying certain rows to another sheet

[Bob]

Hi,
I'm hoping someone can help me with this problem.
I have 5 sheets of information all in one workbook.
All identical set up.
Column A - Name -
Column B - Company
Column C - Current - marked with a "c" if record is current.
Other columns over to column T with various numerical values but could be
blank.

I want to go to Sheet 1 and filter the list by the "c" in column C
Then I want to copy only the visible cells in columns A to T and starting at
row 7
And stopping at the first blank row

Then move to a 6th sheet called "Current" and paste the copied data

I then want to move to Sheet 2 and filter the list by the "c" in column C
Copy only the visible cells
Then move to the 6th sheet called Current

Find the first blank row under what was pasted previously and paste the
copied data

And so on for 5 sheets.

I can easily do it manually with AutoFilter and Go To > Visible Cells Only
tools but I need to be able to get it done with the click of 1 button.

I'm OK at understanding and adapting code but I'm just a beginner at writing
it from scratch.

Thanks

Bob

 

[marcus]

Hi Bob

I hope I have understood correctly. This code filters data on
criteria in Column 3 with criteria of "C", then copies the data to the
first non blank cell in the Current sheet. I hope this helps, you
should be able to manipulate it as you wish. Good luck.

Marcus

Sub CopytoSheet()

Dim s As Worksheet
Dim x As Long
Dim y As Long

For Each s In Sheets
If s.Name <> "Current" Then
s.Activate
s.AutoFilterMode = False
'Check for filter on page.
If Not ActiveSheet.AutoFilterMode Then
ActiveSheet.Rows("7:7").AutoFilter
End If
Selection.AutoFilter Field:=3, Criteria1:="C" ' filter criteria
y = Range("a65536").End(xlUp).Row + 1
Range("A8:T" & y).Copy
Sheets("Current").Select
x = Range("A65536").End(xlUp).Row + 1
Range("A" & x).Select
ActiveSheet.Paste

End If
Next
End Sub

 

[Bob]

Thats perfect, thanks Marcus

I had to change a few minor things because its so hard to explain exactly
whats required without writing pages, but I really had no idea where to
start.

I added some nested If's because there are actually some other sheets in the
workbook

For Each s In Sheets
If s.Name <> "Current" Then
If s.Name <> "Clients" Then
If s.Name <> "FILTER" Then

Thanks again

Bob

 

[marcus]

Hi Bob

Pleased to be of assistance. You kept your question short and to the
point.

You can reduce your IF statements futher by using this code.

If (s.Name) <> "Current" And (s.Name) <> "Clients" And (s.Name) <>
"FILTER" Then

Take care

Marcus