Copy certain cells from multiple sheets [Closed]

Registration date
Thursday October 20, 2016
Last seen
October 20, 2016

I have lots of sheets that I need to copy specific cells and copy them to a summary sheet. I currently have this macro:

Private Sub CommandButton1_Click()

Dim C As Long
Dim Cell As Range
Dim NextRow As Long
Dim Rng As Range
Dim EntryWks As Worksheet
Dim DBWks As Worksheet
C = 1
Set EntryWks = Worksheets("Sheet1")
Set DBWks = Worksheets("Summary")
Set Rng = EntryWks.Range("B4,B6:B9,C19:C22,D19:D22")

NextRow = DBWks.UsedRange.Rows.Count
NextRow = IIf(NextRow = 1, 1, NextRow + 1)

For Each RA In Rng.Areas
For Each Cell In RA
C = C + 1
DBWks.Cells(NextRow, C) = Cell
Next Cell
Next RA
End Sub

It works great to copy the data from Sheet1 to Summary... My question is: Is there any way to get it to do that for all of the Sheets without explicitly calling all of them out and then in Summary to put each one on a separate row? So for instance the data from Sheet 1 gets copied to B1:N1. Can we get it so it will go through all of the sheets not named "Summary" and then start in B2:N2, B3:N3, etc?


Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!