excel VBA - Combine sheets together (Append to bottom)

Posted on 2013-09-29
Medium Priority
Last Modified: 2013-09-30

I have 4 sheets with the same number of columns but a dynamic number of rows.

I want to add 'sheet 2' 'sheet 3' and 'sheet 4' to the bottom of 'sheet 1'.

Is there an easy way to achieve this in VBA?
Question by:mhdi
  • 2
  • 2
LVL 43

Accepted Solution

Saqib Husain, Syed earned 2000 total points
ID: 39532025
Something like

sheet2.range("A1:D" & range("A" & rows.count).end(xlup).row).copy sheet1.range("A" & rows.count).end(xlup).offset(1)
sheet3.range("A1:D" & range("A" & rows.count).end(xlup).row).copy sheet1.range("A" & rows.count).end(xlup).offset(1)
sheet4.range("A1:D" & range("A" & rows.count).end(xlup).row).copy sheet1.range("A" & rows.count).end(xlup).offset(1)

Change the D in A1:D to the last column you have

Author Comment

ID: 39532047
Thanks, that almost works. Its just not counting the rows the source sheets correctly.

Im trying to work out why, but at the moment it seems to be random.

Author Comment

ID: 39532050
Fixed it. I had to specifically reference the sheets.

sheet2.range("A" & sheet2.rows.count)
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39532398
You are right. Sorry for that.


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question