help with vba code pasting data to last row

[lilsnoop]

Hello, I was hoping someone could assist me with editing my code so it the second macro when called would paste the data underneath the data pasted from the 1st macro.

The 1st macro pulls data from a closed workbook and it will copy day from A1 to X40000. There may be only data to row 25000, but the 40000 is just in case there is more data when report ran next, etc. The macro will paste this data on the workbook the macro is open and called from and then after pasting the initial data, the macro will call a 2nd macro, which will open a different workbook a copy that data. I need the 2nd macro though to be able to find the last row of data from column A and paste this new data underneath the data from the 1st copied data. Hope this makes sense. I have bolded the part that I have tried to make it locate the last row, but I keep getting errors. Any assistance resolving this issue would be greatly appreciated!

Here is my code:

Please Login or Register  to view this content.

[cubangt]

You can determine the last row using count

'1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row

You can then replace that 40000 value with the variable "lCopyLastRow "

[lilsnoop]

cubangt-Thank you for your suggestions! Could you demonstrate what I would need to do with the code above? I apologize, but I'm vba challenged and I found this code online. the 1st macro works great, but this is the 1st time I'm trying to run it twice per se to get data stacked on top of each other from 2 separate workbooks. Thanks again!

[cubangt]

I updated your code:

So whats the purpose of the two routines? Seems you are opening one file to get the data, then pasting it in the second file, is that correct?

Please Login or Register  to view this content.

[lilsnoop]

Yes, that is correct. Due to the nature of the two reports the data has to be stacked from 2 separate reports, so this data can be analyzed correctly. Thank you for helping with the code. I just tried implementing it and it was hung up at the same code in the 2nd macro:

Please Login or Register  to view this content.

[cubangt]

Is this a valid sheet in your file?
Set sh = Sheet16

Also, what is that line doing, since right after you are closing the workbook?
sh.Cells(Rows.Count, "A").End (xlUp)

[lilsnoop]

cubangt- It is a valid worksheet, but it is labeled "Employer NCP" on the tab. The workbook that uses this macro remains open. The macro(s) are pulling data from other workbooks that are closed from worksheets titled "Report 1". So when the 1st macro is called it opens the 1st closed workbook/report and copies the data from the given range and pastes it in my open main workbook (where the macro button is on) and then that workbook that I just received the data from closes again, per the macro instruction. It should then call the 2nd macro to do the same thing, but I wasn't sure how to get the data to copy that data beneath the data just brought over from the 1st report.

Currently your code copies the 1st row of headers and then it errors on that particular last row code. Although I should have had the data visible from the 1st report before getting that error. Hope that explains it better.

[cubangt]

Well im literally working on a similar thing myself for our reports here..
And this code works great for me, i only have to open my template and click the macro button, it will then open my other file on the network and copy the data into the template.. and when i run it again and again, the new data is appended to the end of the last rows.

Please Login or Register  to view this content.

Try modifying this above code for your purposes.

[lilsnoop]

Thanks a lot cubangt!! Sorry for belated reply has been a long day.