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!
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

schmidt1962

New Member
Joined
May 31, 2013
Messages
25
Do you mean if a cell (in column a) has the word strategy or distribution?

if so try the Instr function.
 

Hanford

New Member
Joined
Jul 31, 2013
Messages
5
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.
 

schmidt1962

New Member
Joined
May 31, 2013
Messages
25
Any way you can attach a workbook so I can look at it? It is hard to visualize your goal
 

Hanford

New Member
Joined
Jul 31, 2013
Messages
5

ADVERTISEMENT

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.
 

schmidt1962

New Member
Joined
May 31, 2013
Messages
25
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)
 

Hanford

New Member
Joined
Jul 31, 2013
Messages
5

ADVERTISEMENT

I want to copy only
 

schmidt1962

New Member
Joined
May 31, 2013
Messages
25
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
 

Hanford

New Member
Joined
Jul 31, 2013
Messages
5
You're amazing! I can't thank you enough. It works great. Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,126,939
Messages
5,621,720
Members
415,853
Latest member
Newlife72

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
Top