Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying certain rows to another sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying certain rows to another sheet
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying certain rows to another sheet
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" wrote in message oups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying certain rows to another sheet
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying rows from one sheet to another.... | Excel Worksheet Functions | |||
copying rows from next sheet over | Excel Discussion (Misc queries) | |||
Automatically copying specified rows from a sheet to another | Excel Programming | |||
Copying rows to a new sheet | Excel Programming | |||
Copying specific rows from one sheet to another | Excel Programming |