Log In
Register
Home
September 28, 2018
Offline
Hi,
I have a Macro that is supposed to copy data from NewData#1 tab and NewData#2 tab and summarize in the SummaryData tab. I used Relative References to record this Macro. However, when i added new lines in the #1 and #2 tabs "see them in yellow" only new lines from tab#2 are reflected in the SummaryData tab after i run the Macro. Could you please help me fix the code in order to summarize all lines from both tabs even after new lines are added? Worksheet is attached
Thanks a lot
Nadia
See AnswerJuly 13, 2016
Offline
Hello Nadia,
Your Macro looks great, but there are a couple of lines causing you issues.
Whilst it does successfully copy all data from both tabs, when it is pasting the second lot of data, it is pasting over the top of those last two lines from the first set. The reason for this is an errant line in the code: "Range("A17").Select". You also have a line before that which is hunting for the bottom of the list: "Range(Selection, Selection.End(xlDown)).Select" however a small adjustment will prevent that from being a selection of a range and instead a location of the first empty cell in the column (which of course uses the offset like you had in your code)
I have removed "Range("A17").Select" from your code and adjusted the xlDown/Offset code below, so I believe it will work now.
'
Range("A4").Select
Sheets("NewData#1").Select
Range("A4").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SummaryData").Select
ActiveSheet.Paste
Sheets("NewData#2").Select
Range("A4").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SummaryData").Select
Range("A4").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Cheers,
Adrian
Answers Post
1 Guest(s)
