Macro to copy paste Tables to Master sheet

Hi all,

Figured I would end the year with a macro question that involves copying and pasting data from multiple worksheets to one. I tried the macro recorder however, (as I am creating an nVision layout) the number of lines (data) will change month to month or whatever the user ends up putting as a date range. So, How do I create a macro that will go and get Table1 and Table2 and paste it all on the "MASTER" Worksheet?

I am attaching the following: sample of the macro I tried based on what other individuals have written and the before and after results that I hope to achieve with this macro.
I will also need to adjust the macro so that it is compatible with Excel 2003. (So do I need 2 different macros? One for 2003 Excel and One for 2010 Excel?) I am having difficulties with how to instruct it to go to the first blank cell in Column B after pasting the results from the REV_SHR worksheet on the Master sheet without embedding an actual cell location.

Sheets("MASTER").Select
Range("B4").Select
Selection.End(xlDown).Select
Range("B122").Select This is the part I think needs to be changed so that it is more flexible How do I code it so that it is "xldown + 1 row"?
ActiveSheet.Paste

Please note that I colored the sections so as to show where the data is coming from, but do not want that to be part of the macro. Any and all help is greatly appreciated!! Happy New Year too!

Please Login or Register  to view this content.

Try

Please Login or Register  to view this content.

Hi guys! I tried Jeff's macro and it worked like a charm! Thanks so much. I tried to do something similar but couldn't figure out what I was doing wrong so I went the Macro recorder route. close but still no cookies! 5 stars for Jeff!

You are most welcome and thanks for the feedback.