VBA: Copy to last used row in another sheet

[tl01092]

Hi, I am trying to paste from the sheet output to the input sheet in a different workbook, but only after the last used row. My code doesnt seem to work.

Sub CopynPasteWrkBk()

Dim InputFile As Workbook
Dim OutputFile As Workbook
Dim Inputpath As String
Dim Outputpath As String '
Dim ws1 As Worksheet, ws2 As Worksheet


'## Open both workbooks first:
Set InputFile = ThisWorkbook
Set OutputFile = Workbooks.Open("Z:\Datbase.xlsx")
Set ws1 = InputFile.Sheets("Output")
Set ws2 = OutputFile.Sheets("Input")

'Now, copy what you want from InputFile:
InputFile.Sheets("Output").Activate
InputFile.Sheets("Output").Range("A3:T12").Select
Selection.Copy


'Now, paste to OutputFile worksheet:
OutputFile.Sheets("Input").Activate

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'Close InputFile & OutputFile:
'OutputFile.Close savechanges:=True


'MsgBox "Data Successfully Logged"


End Sub

 

[steve the fish]

Yes that couldnt work properly. Where is it you want it pasted? Column A?

 

[tl01092]

Yes, column A
Thanks

 

[steve the fish]

Ok this will paste special into the cell after the last used cell in column A of the target worksheet:

Sub CopynPasteWrkBk()

Dim InputFile As Workbook
Dim OutputFile As Workbook
Dim Inputpath As String
Dim Outputpath As String '
Dim ws1 As Worksheet, ws2 As Worksheet, lr As Long

'## Open both workbooks first:
Set InputFile = ThisWorkbook
Set OutputFile = Workbooks.Open("Z:\Datbase.xlsx")
Set ws1 = InputFile.Sheets("Output")
Set ws2 = OutputFile.Sheets("Input")

With ws2
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    ws1.Range("A3:T12").Copy
    .Range("A" & lr + 1).PasteSpecial xlValues
End With

'Close InputFile & OutputFile:
'OutputFile.Close savechanges:=True

'MsgBox "Data Successfully Logged"

End Sub

 

[tl01092]

Hi, thanks for your help. The code works but sometimes I have data in column C and not in column A of the workbook the data is imported to. When I run the code, the data pastes over the data in column c. Can the code be modified so that it only pastes the data after the last used row from column A to C?

 

[steve the fish]

Try changing the lr line to:

lr = .Columns("A:C").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 

[tl01092]

Thanks - that worked!