How can I populate data from other sheets onto my main sheet, one below the other?

[roxnoxsox]

Hi, sorry if this has a really simple solution; I'm new to learning VBA and stuck on this issue :/

On my main sheet (named 'Prices') I want to populate A2 to D2 down with the data listed on A2 to D2 down of my other 5 sheets. The data on these 5 sheets will vary in length so if I add new things to it, I'd like the VBA to just copy all the way to the end without having to specify a range. I need a code which will copy these one after the other? So as soon as sheet2 has been copied over, sheet3 will just copy into the next blank cell underneath this.

Does that make sense at all? Sorry if I'm not clear! Please let me know if you need more specific clarification! All help is much appreciated :D

[Kenneth Hobs]

Add the sheet names to the array and you will be set.

Sub ken()  Dim r As Range, rr As Range, rrr As Range, s() As String, i As Integer
  s() = Split("Sheet2,Sheet3", ",")
  
  For i = 0 To UBound(s)
    Set r = Worksheets("Prices").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Set rr = Worksheets(s(i)).Range("A2", Worksheets(s(i)).Range("A" & Rows.Count).End(xlUp)).Resize(, 4)
    r.Resize(rr.Rows.Count, rr.Columns.Count).Value = rr.Value
  Next i
End Sub

[roxnoxsox]

Thank you! This worked perfectly and seems to make sense so hopefully I can adapt this formula to use again in the future Many thanks!!

[roxnoxsox]

UPDATE: I've come across an error - I have dates in column D on my sheets. Most of these are copying over fine (ie. exactly as they are entered). However, some are swapping to American format? So I might have: 12/06/2015 entered but when I run the macro, it copies over as 06/12/2015.

Any idea why this is happening or how to fix it? I've looked in the cells where this is happening and it appears to be exactly the same as the others - same format, no spaces or time or anything?

[snb]

Or

Sub M_snb()
  For each sh in sheets
    sn=sh.cells(2,1).resize(,4)
    for j=1 to 4
      if isdate(sn(1,j)) then sn(1,j)=1*sn(1,j)
    next
    if sh.name<>"Prices" then sheets("Prices").cells(Rows.Count,1).End(xlUp).Offset(1).resize(,4)=sn
  Next 
End Sub

[Kenneth Hobs]

If you want to copy more than just the value then Copy should suffice.

Sub kenC()  
  Dim r As Range, rr As Range, ws As Worksheet
  
  For Each ws In Worksheets
    If ws.Name <> "Prices" Then
      Set r = Worksheets("Prices").Range("A" & Rows.Count).End(xlUp).Offset(1)
      Set rr = ws.Range("A2", ws.Range("A" & Rows.Count).End(xlUp)).Resize(, 4)
      rr.Copy r.Resize(rr.Rows.Count, rr.Columns.Count)
    End If
  Next ws
  
  Worksheets("Prices").Columns("A:D").AutoFit
  Application.CutCopyMode = False
End Sub

[roxnoxsox]

Thanks Kenneth, that worked just the trick !

[roxnoxsox]

Hi there, sorry to reopen this thread again.

I've tried readjusting the formula myself but I'm getting stuck. How could I adjust this to work so that I wanted to copy everything from A7-D7 down on my separate sheets and paste into A2-D2 on my 'Prices' sheet?

[snb]

Couldn't open your sample workbook....

[Kenneth Hobs]

Changing A2 to A7 does not suffice?