Paste to next empty row in another workbook

[AawwYeahh]

I have recorded a macro to select the required data IE (A66:BE66)
Open existing workbook IE Database
Paste values
Close

My problem is I need to be able to paste to last empty row not static row IE (110)

current code


Sub databasetest1()
' databasetest1 Macro
ActiveWindow.SmallScroll Down:=51
Range("A66:BE66").Select
Selection.Copy
ChDir "C:\Users\ay\South\users"
Workbooks.Open FileName:="C:\Users\ay\South\users\[DATABASE].xlsx"
ActiveWindow.SmallScroll Down:=102
Rows("110:110").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 2
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

Any help would be appreciated!

[jolivanes]

Re: "paste to last empty row"
I assumed that you meant first empty row below used range.


Change references, like Sheet Names, where required.

Sub Or_Maybe_So()
Dim wb As Workbook
Application.ScreenUpdating = False
Set wb = Workbooks.Open("C:\Users\ay\South\users\[DATABASE].xlsx")
With wb.Sheets("Sheet3") '<---- Sheet in the opened workbook where you want data to paste
.Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(, 57).Value = ThisWorkbook.Sheets("Sheet1").Range("A66:BE66").Value '<---- Change Sheet Name to actual
End With
wb.Close True
Application.ScreenUpdating = True
End Sub

[AawwYeahh]

THANK YOU! (this worked like a charm!:hi:

[jolivanes]

Thank you for letting us know and good luck