Looping through sheets copying data to a master table

[Mc Culloch]

Hi

Have a workbook with 27 sheets in it. Each sheet is for a department code.

Fortuately the data on each sheet is in the same range, A6:S148. I'm looking for a quick way to copy the range from each sheet and stack them below each other in a master table, along with the Dept code (which is always in cell A2.

Any suggestions most welcome. I'vve been getting bogged down in how to loop through the worksheets and also, once on the Master sheet, how to move the active cell one row down having done End:xl down so that I don't over write the previous sheets last row of data - in fact I'm just bogged down!

Cheers

 

[Mc Culloch]

Stacked one on top of the other - don't need a space.
However, the department code is in cell A2 on each of the sheets as a header. This info needs to be copied to the master sheet as well and copieded down all the rows i.e. like its a feild in a database table.

Thanks for your help!

 

[HalfAce]

Hello Mc Culloch,
Assuming you have 27 sheets of data and you add one sheet named 'Master' at
the far left of your sheet tabs, thus making your data sheets numbers 2 through 28 from
left to right, you can try something like this.

Sub CopyFromAll27Sheets()
Dim Sht As Integer, NxtRw As Long
For Sht = 2 To 28
  NxtRw = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1
  With Sheets(Sht)
    .Range("A2").Copy Sheets("Master").Cells(NxtRw, "A").Resize(143)
    .Range("A6").Resize(143, 19).Copy Sheets("Master").Cells(NxtRw, "B")
  End With
Next
End Sub

Does that get you close?

 

[Mc Culloch]

thanks for your help. I'll give it a try and let you know how I get on

cheers

 

[Mc Culloch]

I tried adding a PasteSpecial Values to the code (some of the base data is formula driven so when posted is picking up the wrong values)

However it bugs out - I think it's something to do with the fact that the code uses the Cells Property, if I want to paste speacial do I have to Select a range? Also, if that is the case why?

Any guidance gratefully received!

Sub CopyFromAll27Sheets()
Dim Sht As Integer, NxtRw As Long
For Sht = 1 To 27
  NxtRw = Sheets("Upload").Cells(Rows.Count, "A").End(xlUp).Row + 1
  With Sheets(Sht)
    .Range("A2").Copy Sheets("Upload").Cells(NxtRw, "A").Resize(143)
    .Range("A6").Resize(143, 19).Copy Sheets("Upload").Cells(NxtRw, "B")
    ActiveSheet.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  End With
Next

 

[HalfAce]

Yeah, I wondered if there would be formulas causing this problem. (I shoulda asked. :wink: )
Instead of copying/paste-specialing we can just tell it to make the cells in Master to be
equal to the values in each of the other sheets.
(All values will be imported to the Master sheet as static values, not formulas).
Try replacing the previous code with this.

Sub CopyFromAll27Sheets()
Dim Sht As Integer, NxtRw As Long
For Sht = 2 To 28
  NxtRw = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1
  Sheets("Master").Cells(NxtRw, "A").Resize(143).Value = Sheets(Sht).Range("A2").Value
  Sheets("Master").Cells(NxtRw, "B").Resize(143, 19).Value = _
    Sheets(Sht).Range("A6").Resize(143, 19).Value
Next
End Sub

That more what you're looking for?

 

[Mc Culloch]

.Value! You learn something new everyday - I'm comming from the point of view of an application user, day to day I would just Copy & Paste Special. Once you get into VBA you realise there are lots of ways to achieve the same end result, you just have to think a bit lateraly.

Thanks for your help, using Value is going to come in very handy

Cheers

 

[HalfAce]

You're most welcome. And yes, using .Value does indeed come in handy.
I don't think I ever use PasteSpecial (as value) in vba.

Once you get into VBA you realise there are lots of ways to achieve the same end result

Yes. It's no longer a matter of if (or how) it can be done, it becomes a matter of which
is the best way to do it, with 'best' being subjective in relation to what's being done.
(and a lot of times, what was done just before and will be getting done next.)
I always enjoy seeing ways to do stuff that I already know multiple ways to do.