VBA Assistance With Copying & Pasting

[Tommy1115]

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 Nf_Rows As Integer
Nf_Rows = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
Range("S1").Select
ActiveCell.FormulaR1C1 = Nf_Rows

    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

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

 

[MagicSteve]

Here's an example of copying with VBA
In this case I copied a small block in cells F3 through G6 to F9 through G12

Sub TestBoy()

Dim mySheet As Worksheet
Set mySheet = ThisWorkbook.Sheets("Main")
Dim rg As Range
Dim destRg As Range

Set rg = mySheet.Range("F3:G6")
Debug.Print rg.Address
Set destRg = mySheet.Range("F9:G12")
Debug.Print destRg.Address

rg.Copy Destination:=destRg

End Sub

Don't use select when copying. Everyone does that because that's how the macro recorder does it.
You don't need to use "Destination:=" but it makes the code easier to read.
you could use
rg.Copy "A5"
and it would copy the block to A5 with spill.

 

[Tommy1115]

Thank you for that. That helps a little bit.
The issue is, the starting and ending ranges I copy from will always be different so the example above won't work completely.

I have used this formula - Range("A5:A" & LASTLINE).Select - and it selects from A5 to my last line. The issue is, the starting point will not be A5 every time.
I tried Range("A" & COLA : "A" & LASTLINE).Select but it did not work. Both cells have to be dynamic is the issue.

 

[MARK858]

Try...

Range("A" & COLA & ":A" & LASTLINE).Select

or if that is the range being copied (without the Select)

Range("A" & COLA & ":A" & LASTLINE).Copy

 

[Peter_SSs]

@Tommy1115
First a couple of things relating to forum use:

• I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
  
  
• When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. It will also stop you from getting those strange emojis in your code. My signature block below has more details. I have added a new section in post 1 with the tags so that you can see the difference.

Are you only copying data from column A? Apart from not telling us if it is just one column or several columns, you mention copy and paste but you have not told us where you want to paste.
It has also been mentioned above that you do not need to select to copy (or paste) and doing so slows your code.
In any case, see if these snippets help at all.

For putting the last row (based on column A) into S1
There is no need for any 'Select's or creation of variables to hold the value as it can be put directly into S1

With Sheets("Master data")
  .Range("S1").Value = .Cells(Rows.Count, 1).End(xlUp).Row
End With

For copying the new Rows (column A only).
Note that

• There is a check in the code so that if LASTLINE is not greater than COLA then it means the code has been run when no data has been added since cell S1 was last updated so the copy is aborted.
• If new data has been added then 1 has to be added to COLA otherwise the last row of the old data would also be copied.
• Since no information about where to paste the new data has been given, I have not added a 'Destination:=' section like in the post #2 code.

Dim COLA As Long, LASTLINE As Long

With Sheets("Master Data")
  COLA = .Range("S1").Value
  LASTLINE = .Cells(Rows.Count, 1).End(xlUp).Row
  If LASTLINE > COLA Then
    .Range("A" & COLA + 1, .Range("A" & LASTLINE)).Copy '<- No "Destination:=" added yet
  End If
End With

 

[MARK858]

@Tommy1115 I notice that you have marked my reply as a solution, if nothing else you should really add the sheet name to it to make it a bit more reliable

Sheets("Master data").Range("A" & COLA & ":A" & LASTLINE).Copy

I would also have a look at the points @Peter_SSs raised in the bottom half of post 5