Macro to pull selected rows from multiple sheets to a summary sheet

Hanford

New Member
Joined
Jul 31, 2013
Messages
5
I'm definitely not a programmer and would rate my Excel skills as average at best.

That said, I'm looking for help creating a macro to pulled selected rows from multiple worksheets and paste them all in a summary worksheet. For example, if column A has "Strategy" or "Distribution" in it, I want to pull that into Sheet 5. I want to do this for Sheets 1-3.

I want columns A - H to be pulled into the summary sheet only. The number of rows on sheets 1-3 will vary and continue to grow.

Anything anyone can do to help would be greatly appreciated.

Thanks!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Do you mean if a cell (in column a) has the word strategy or distribution?

if so try the Instr function.
 
Upvote 0
not familiar with that function but I'm not sure it will resolve my issue completely. I want to combine 3 different sheets into one summary sheet but only pull certain rows based on what is in a certain column. It definitely needs to be a macro as the ultimate end-user of this spreadsheet only has a very basic knowledge of Excel.
 
Upvote 0
It won't let me attach the spreadsheet. I'm willing to share it though. Let me see if I can explain it better.

In my Workbook, I have 4 sheets - Central, Eastern, Western, and All Geo Zones. Each of the sheets have the exact same headers:

Column A - Inititive Type
B - Customer
C - Project
D - Description
E - Approach
F - Timing
G - Priority
H - Project Status
I - Contact
J - Next steps

Each sheet has 2 header rows that I don't want to have pulled over so the data rows start on Row 3

In Column A - Initiative Type, there are several different options to select from a drop down list:

Customer - Distribution
Customer - Event
Customer - Strategy
Capability
Other
Complete

My goal is to take all rows from the Central, Eastern, and Western Sheets that have Customer* (any of the customer options listed above) or Capability in Column A and insert those rows into the All Geo Zones sheet. Ideally, I would have only columns A - H be pulled into the All Geo Zone sheet but that's secondary to main goal of consolidation.

Just like the region sheets, the All Geo Zone sheet has two header rows so I would want the data to start on row 3, (A3).

Hopefully this clears things up. Thank you for your help.
 
Upvote 0
From the central eastern and western sheets, do you just want to copy those rows, or do you want to cut them (and place in all geo zones)
 
Upvote 0
Code:
Sub Hanford()'
'
'
 
Dim i, Totrows As Integer
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Central")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Eastern")
Dim ws3 As Worksheet: Set ws3 = ThisWorkbook.Sheets("Western")
Dim ws4 As Worksheet: Set ws4 = ThisWorkbook.Sheets("All Geo Zones")
 
 
Application.ScreenUpdating = False
 
ws1.Activate
Totrows = Range(Cells(3, 1), Cells(3, 1).End(xlDown).Address).Rows.Count + 2
 
For i = 3 To Totrows
If Cells(i, 1) = "Customer - Distribution" Or Cells(i, 1) = "Customer - Event" Or Cells(i, 1) = "Customer - Strategy" Or Cells(i, 1) = "Capability" Or Cells(i, 1) = "Other" Or Cells(i, 1) = "Complete" Then
Rows(i).Copy
'activates All Geo Zones worksheet
 
ws4.Activate
Rows(3).Select
Selection.Insert Shift:=xlDown
ws1.Activate
'Copy row and paste it to the other worksheet
End If
 
Next i
 
 
ws2.Activate
Totrows = Range(Cells(3, 1), Cells(3, 1).End(xlDown).Address).Rows.Count + 2
 
For i = 3 To Totrows
If Cells(i, 1) = "Customer - Distribution" Or Cells(i, 1) = "Customer - Event" Or Cells(i, 1) = "Customer - Strategy" Or Cells(i, 1) = "Capability" Or Cells(i, 1) = "Other" Or Cells(i, 1) = "Complete" Then
Rows(i).Copy
'activates All Geo Zones worksheet
 
ws4.Activate
Rows(3).Select
Selection.Insert Shift:=xlDown
ws2.Activate
'Copy row and paste it to the other worksheet
End If
 
Next i
 
 
ws3.Activate
Totrows = Range(Cells(3, 1), Cells(3, 1).End(xlDown).Address).Rows.Count + 2
 
For i = 3 To Totrows
If Cells(i, 1) = "Customer - Distribution" Or Cells(i, 1) = "Customer - Event" Or Cells(i, 1) = "Customer - Strategy" Or Cells(i, 1) = "Capability" Or Cells(i, 1) = "Other" Or Cells(i, 1) = "Complete" Then
Rows(i).Copy
'activates All Geo Zones worksheet
 
ws4.Activate
Rows(3).Select
Selection.Insert Shift:=xlDown
ws3.Activate
'Copy row and paste it to the other worksheet
 
End If
 
Next i
 
Application.ScreenUpdating = True
 
'
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top