Append Data to Bottom When Using Combine & Merge Macro

[wealthistime]

Hello,

I am using the below code to combine workbooks into a single sheet. The macro works perfectly for combinging the sheets, however, I can only run the code once. If I run the macro a second time, the data from the first run is overwritten. I would like it to be such that you can run the macro over and over again, and each set of new data will be appended to the next empty row. Unfortunately, I just haven't been able to get this to work. Any help is appreciated. Thanks!

A Sample of the destination workbook ("Invoice Workbook") and the source data ("TSR Template1-Test1") are in the attached zip.

Please Login or Register  to view this content.

[pike]

wealthistime,
with your code you need to move the row counter inside the loop

Please Login or Register  to view this content.

[wealthistime]

Hi Pike,
Sorry but I can't seem to make the adjustment. I modified the code but now i only get five rows. When I go through it, it's copying Range A:BO and pasting it over the same range.

In short, my code is not going to the next blank row. Any ideas? here is the modified code:

Please Login or Register  to view this content.

[pike]

replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

[wealthistime]

How do I get the code you provided to do a paste value instead of a paste? This step is necessary upon each iteration. If I can get it to paste value then it will be perfect! Thanks so much for your help thus far!

[pike]

Hi wealthistime
try...

Please Login or Register  to view this content.

[wealthistime]

I'm afraid that doesn't work at all. The code you provided before (below) works perfectly...except i need to do a paste value and not a paste...:

Please Login or Register  to view this content.

[pike]

untested but try

Please Login or Register  to view this content.

[wealthistime]

Woo hoo! That did the trick allright. I had tried something similar but didnt have it correctly written. Thanks so much! this will be a huge help. I wll post final code when i have some other tweaks perfected. Thank you!

[pike]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

can you please add a location to your profile so we know what time zone you are in

[wealthistime]

FINAL CODE:

Please Login or Register  to view this content.