Hello I created a program that automatically scrapes data from a website and paste/formats it into an excel sheet. However I would like that data to then be transferred into another excel Workbook (One that already exists). The macro below transfers the data into another excel workbook but it keeps pasting the transferred data over the data that's been already been previously transferred. How can I get it to transfer and paste the new data starting with the next blank row under the already previously transferred data. I saw in a previous post that:
LastRow = Target.Range("A10000").End(xlUp).Row + 1,
Can do this but I'm not sure how to implement this line in the macro generated vba that I already have. Is there a way to do this without having to write another vba module to implement this? All help is appreciated!
Sub Transfer()
Range("A2:G34").Select
Selection.Copy
Workbooks.Open Filename:= _
"Book1.xlsx"
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save
Range("C6").Select
End Sub
So I figured it out, and I didn't use a macro recording because I couldn't get that to 100% work for my project but after research and trial and error the code below worked exactly how I wanted it too. This works for transferring two different excel workbooks or just sheets! I hope this helps someone.
Sub Transfer()
Worksheets("Source data Sheet Name").Range("A2:G100").Copy
Workbooks.Open Filename:= _
"File Destination for target"
LastRow = Sheets("Target Sheet Name").Cells(Rows.Count, "A").End(xlUp).Row
ActiveSheet.Paste Destination:=Worksheets("Target Sheet Name").Range("A" & LastRow)
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub