PDA

View Full Version : [SOLVED] How can I populate data from other sheets onto my main sheet, one below the other?



roxnoxsox
06-05-2015, 06:49 AM
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
06-05-2015, 09:56 AM
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
06-08-2015, 06:21 AM
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
06-15-2015, 03:29 AM
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
06-15-2015, 03:38 AM
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
06-15-2015, 05:44 AM
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
06-15-2015, 09:04 AM
Thanks Kenneth, that worked just the trick :D !

roxnoxsox
08-20-2015, 03:54 AM
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
08-20-2015, 03:56 AM
Couldn't open your sample workbook....

Kenneth Hobs
08-20-2015, 05:18 AM
Changing A2 to A7 does not suffice?