Cat(append) multiple worksheets into one

A

Arman

I have an Excel spreadsheet that I open from Access VBA and need to
merge/append 4 worksheets in that spreadsheet into one single worksheet in a
new workbook. Can someone point me to the right direction?
Thank you.
 
K

Klatuu

It would take a lot of code to show the detail; however, the concept is
straight forward.

Create the new work book.

Select the old work book.
Select the usedrange first worksheet.
Copy it.

Select the new work book.
Paste the copied data.

Now the trick is you have to keep track of where the next row is so you can
positon your cursor there to paste the next sheet.

Repeat for the rest of the old worksheets.
 
A

Arman

Hi Klatuu,

Thank you very much for you quick response.

Couple of questions, please see inline:

Klatuu said:
It would take a lot of code to show the detail; however, the concept is
straight forward.

Create the new work book.

Select the old work book.
Select the usedrange first worksheet.
Click to expand...
I need to copy the whole worksheet, do I still need to worry about the range?
Copy it.

Select the new work book.
Paste the copied data.

Now the trick is you have to keep track of where the next row is so you can
positon your cursor there to paste the next sheet.
Click to expand...
This is the part where I need the most help. I just need to paste my second
worksheet right after where the first one ends (better if there is one empty
row in between the two). How do I determine this without keeping the row
count in each worksheet? Is this even possible.
Repeat for the rest of the old worksheets.
Click to expand...

Thanks again!
 
K

Klatuu

Answers below
--
Dave Hargis, Microsoft Access MVP


Arman said:
Hi Klatuu,

Thank you very much for you quick response.

Couple of questions, please see inline:


I need to copy the whole worksheet, do I still need to worry about the range?
Click to expand...

For the first worksheet, probably not; however, copying the succeeding
worksheets may be a problem. My guess it the current worksheet would
overwrite the previous sheet. I haven't tested it, so I don't know for sure.
This is the part where I need the most help. I just need to paste my second
worksheet right after where the first one ends (better if there is one empty
row in between the two). How do I determine this without keeping the row
count in each worksheet? Is this even possible.
Click to expand...

Here is how you can determine the number of rows used in the worksheet:
lngXlRows = xlSheet.UsedRange.Rows.Count - 1
This will return the last row in the range
 
Top