Hello all, Need some assistance.
BACKGROUND: Every month a New Customer Report generates and new customers get added to the bottom of the Excel sheet. I manipulate the report some and send out to staff.
I want to automate this and what I am struggling with is the code top copy and paste NEW rows.
In cell S1 is the last row from the previous report which is generated via this code:
Sheets("Master Data").Select
Dim N
f_Rows As Integer
N
f_Rows = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
Range("S1").Select
ActiveCell.FormulaR1C1 = N
f_Rows
Here is where I am stumped. I can figure out what the last line of the current report is (see code below) and I can get to the next "new" cell in a column (code below). But what I cannot figure out is how to copy and paste all new data. My code below allows me to copy only 1 cell and not the range, if that makes sense.
For instance:
If the last report had 100 lines, 100 would show up in cell S1 per the code above
If the new report has 150 lines, I want to copy the ones that are new, so rows 101 - 150
' Finds the last line of the current report
Sheets("Master Data").Select
Dim LASTLINE As Long
LASTLINE = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
' Selects the next "NEW" cell
COLA = Range("S1").Value
Range("A" & COLA).Select
Selection.Copy
Thanks,
Tom
BACKGROUND: Every month a New Customer Report generates and new customers get added to the bottom of the Excel sheet. I manipulate the report some and send out to staff.
I want to automate this and what I am struggling with is the code top copy and paste NEW rows.
In cell S1 is the last row from the previous report which is generated via this code:
Sheets("Master Data").Select
Dim N
![Er... what? o_O o_O](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f635.png)
N
![Er... what? o_O o_O](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f635.png)
Range("S1").Select
ActiveCell.FormulaR1C1 = N
![Er... what? o_O o_O](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f635.png)
VBA Code:
Sheets("Master Data").Select
Dim No_Of_Rows As Integer
No_Of_Rows = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
Range("S1").Select
ActiveCell.FormulaR1C1 = No_Of_Rows
For instance:
If the last report had 100 lines, 100 would show up in cell S1 per the code above
If the new report has 150 lines, I want to copy the ones that are new, so rows 101 - 150
' Finds the last line of the current report
Sheets("Master Data").Select
Dim LASTLINE As Long
LASTLINE = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
' Selects the next "NEW" cell
COLA = Range("S1").Value
Range("A" & COLA).Select
Selection.Copy
Thanks,
Tom
Last edited by a moderator: