Copy and paste data to another open workbook

[KAOS137]

I am trying take copied data and paste it into another workbook with code.

Here is what I am working with:

Private Sub CommandButton1_Click()

If TextBox1.Value = "" Or TextBox2.Value = "" Or TextBox3.Value = "" Then
Exit Sub
End If
Range("H3") = TextBox1.Value
Range("I3") = TextBox2.Value
Range("J3") = TextBox3.Value
lastrow = Range("A3").End(xlDown).Row
Range("H3:J3").AutoFill Destination:=Range(Range("H3"), Range("J" & lastrow))

Range("A3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Here is where I get stuck at...

End Sub


The other workbook would be specified in "TextBox4" of the same userform I use to add data to the page. I will be adding more data from multiple workbooks to the same workbook referenced in "TextBox4", so I need it to find the next empty cell in row "A" and paste the data as values.

Please let me know if you have any questions and thanks for any help.

 

[jmacleary]

If you add the following 4 lines where your text is red

workbooks(TextBox4.Value).select
lRow = Cells(Rows.Count, 1).End(xlUp).Row+1
range("A"&format(lRow)).select
Selection.PasteSpecial Paste:=xlPasteValues

it should work.

 

[KAOS137]

Okay, So I have had to rethink this and would like some guidance on a new approach.

Here is what I am hoping to do:

I have a website that I open excel reports from. I need to copy the tables of each report and then paste them on top of each other in a new workbook creating one long report. Since I have to open each report individually, all 146 of them every two weeks, I need the macro to be on the quick access tool bar. With that said, here are the nuts and bolts:

In the open report, I would like to select "A3:I3" and a variable range down.
Then Copy
Then activate the new workbook, typically named Book1 (I rename and save it after completing the process)
Select cell "A1", or the next empty cell down
paste as values
reactivate original workbook
select cell A3

Thanks for any response and let me know if there are questions.