Transferring Selected Data from Multiple Worksheets to One
Solved/Closed
cpogata
cpogata
- Posts
- 5
- Registration date
- Friday July 25, 2014
- Status
- Member
- Last seen
- August 5, 2014
cpogata
- Posts
- 5
- Registration date
- Friday July 25, 2014
- Status
- Member
- Last seen
- August 5, 2014
Related:
- Transferring Selected Data from Multiple Worksheets to One
- Transfer specific data from one worksheet to another for reports - Guide
- Transfer Excel data from one sheet to another: VBA - Guide
- How to copy data to multiple worksheets in Excel - Guide
- VBA - Transferring select Data to new sheet ✓ - Forum - Excel
- Vba split data into multiple worksheets based on column ✓ - Forum - Excel
2 replies
cpogata
Aug 5, 2014 at 11:27 AM
- Posts
- 5
- Registration date
- Friday July 25, 2014
- Status
- Member
- Last seen
- August 5, 2014
Aug 5, 2014 at 11:27 AM
I keep getting this error "Application Defined or Object Defined Error"
On the code line of Sheets("Overview").Range(Cells......etc.
On the code line of Sheets("Overview").Range(Cells......etc.
TrowaD
Aug 4, 2014 at 11:33 AM
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Aug 4, 2014 at 11:33 AM
Hi Cpogata,
So all your sheets are projects with the project name in A1 and header in row 2.
For the code it doesn't matter how many sheets you have, so you can add as many as you want.
I named the result sheet Overview. If you want a different name then don't forget to alter the code (best done by Ctrl+H; find and replace).
Before running the code create the Overview sheet.
On the Overview the project names are placed in column E and header in row 1.
When the code is run:
- The Overview sheet is cleared, except for it's header in row 1.
- All sheets except the Overview sheet are scanned for "High Priority" and "Low Priority" in column A. When found placed in the Overview sheet.
Here is the code:
Best regards,
Trowa
So all your sheets are projects with the project name in A1 and header in row 2.
For the code it doesn't matter how many sheets you have, so you can add as many as you want.
I named the result sheet Overview. If you want a different name then don't forget to alter the code (best done by Ctrl+H; find and replace).
Before running the code create the Overview sheet.
On the Overview the project names are placed in column E and header in row 1.
When the code is run:
- The Overview sheet is cleared, except for it's header in row 1.
- All sheets except the Overview sheet are scanned for "High Priority" and "Low Priority" in column A. When found placed in the Overview sheet.
Here is the code:
Sub RunMe() Dim ws As Worksheet Dim lRow As Long Sheets("Overview").Range(Cells(2, "A"), Cells(Rows.Count, "E")).ClearContents For Each ws In Worksheets If ws.Name = "Overview" Then GoTo Skip ws.Activate lRow = Range("A" & Rows.Count).End(xlUp).Row For Each cell In Range("A3:A" & lRow) If cell = "High Priority" Or cell = "Low Priority" Then Range(Cells(cell.Row, "A"), Cells(cell.Row, "D")).Copy Sheets("Overview").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Range("A1").Copy Sheets("Overview").Range("E" & Rows.Count).End(xlUp).Offset(1, 0) End If Next cell Skip: Next ws End Sub
Best regards,
Trowa
Aug 5, 2014 at 11:33 AM
Aug 5, 2014 at 11:39 AM
So I'm using a button click to do this. Is that why or am I supposed to place the code somewhere else?
Aug 5, 2014 at 11:43 AM
Dim ws As Worksheet
Dim lRow As Long
Sheets("Overview").Range(Cells(2, "A"), Cells(Rows.Count, "E")).ClearContents
For Each ws In Worksheets
If ws.Name = "Overview" Then GoTo Skip
ws.Activate
lRow = Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Range("A3:A" & lRow)
If cell = "High Priority" Or cell = "Low Priority" Then
Range(Cells(cell.Row, "A"), Cells(cell.Row, "D")).Copy
Sheets("Overview").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
Range("A1").Copy Sheets("Overview").Range("E" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next cell
Skip:
Next ws
End Sub
This is exactly what I put after I made an Overview sheet.
Aug 5, 2014 at 11:44 AM
Aug 5, 2014 at 11:48 AM
Careful with personal information.