Find used range to copy

Question

I am creating a sheet to track social media channels and using VBA to automate a copy/paste for a selection of the data.

This is my first ever go with VBA so I'm struggling a little...

I have written this code:

Sub Copy_Results_LeukaTwitter()
Sheets("Twitter_LeukaPasteSheet").Select
    Range("B2:B50").Select
    Selection.Copy
    Sheets("SocialResults").Select
    Range("b2").Select
    ActiveSheet.Paste
    Range("a2:a50") = "Twitter - @leadleukaemia"
Sheets("Twitter_LeukaPasteSheet").Select
    Range("F2:F50").Select
    Selection.Copy
    Sheets("SocialResults").Select
    Range("G2").Select
    ActiveSheet.Paste
Sheets("Twitter_LeukaPasteSheet").Select
    Range("G2:G50").Select
    Selection.Copy
    Sheets("SocialResults").Select
    Range("F2").Select
    ActiveSheet.Paste
Sheets("Twitter_LeukaPasteSheet").Select
    Range("H2:H50").Select
    Selection.Copy
    Sheets("SocialResults").Select
    Range("H2").Select
    ActiveSheet.Paste
Sheets("Twitter_LeukaPasteSheet").Select
    Range("D2:D50").Select
    Selection.Copy
    Sheets("SocialResults").Select
    Range("c2").Select
    ActiveSheet.Paste
    End Sub

Now I want to make sure that only the rows with data are copied and that the data is then pasted on the next available row in "SocialResults" I'm at a loss as to how to do this....

Here's a screen shot of the data I'm pasting from Twitter_LeukaPasteSheet

Please view this resource https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba - also give us a screenshot of your data in "twitter_leukaPasteSheet" — Badja, Mar 14 '19 at 14:50

score 1 · Answer 1 · answered Mar 14 '19 at 16:04

You should not be using copy/paste as that is very inefficient. This code will first look to see what the last used row is on your Social Results sheet and add 1 (because that is the row the new data should start inserting). Then instead of copy/paste you can just set the cells equal to what you want. This is much faster as it doesn't require going to those other sheets.

Sub Copy_Results_LeukaTwitter()
    Dim lngRow As Long
    Dim shtTo As Worksheet, shtFrom As Workbook

    'Sets the sheet that the data will be copied to and from
    Set shtTo = Sheets("SocialResults")
    Set shtFrom = Sheets("Twitter_LeukaPasteSheet")

    'finds the last row in that sheet with data and adds 1; assuming that column A has data (if not change the column name)
    lngRow = shtTo.Cells(shtTo.Rows.Count, "A").End(xlUp).Row + 1

    'set values
    shtTo.Range("B" & lngRow & ":B" & lngRow + 49).Value = shtFrom.Range("B2:B50").Value
    shtTo.Range("A" & lngRow & ":A" & lngRow + 49).Value = "Twitter - @leadleukaemia"
    shtTo.Range("G" & lngRow & ":G" & lngRow + 49).Value = shtFrom.Range("F2:F50").Value
    shtTo.Range("F" & lngRow & ":F" & lngRow + 49).Value = shtFrom.Range("G2:G50").Value
    shtTo.Range("H" & lngRow & ":H" & lngRow + 49).Value = shtFrom.Range("H2:H50").Value
    shtTo.Range("C" & lngRow & ":C" & lngRow + 49).Value = shtFrom.Range("D2:D50").Value

End Sub

Perfect answer here - very efficient – Badja Mar 14 '19 at 16:08 — Badja, Mar 14 '19 at 16:08

Find used range to copy

1 Answers1