VBA: Copy to last used row in another sheet

tl01092

New Member
Joined
Jan 10, 2020
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,143
Office Version
  1. 365
Platform
  1. Windows
Yes that couldnt work properly. Where is it you want it pasted? Column A?
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,143
Office Version
  1. 365
Platform
  1. Windows
Ok this will paste special into the cell after the last used cell in column A of the target worksheet:

VBA Code:
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

New Member
Joined
Jan 10, 2020
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Oct 20, 2009
Messages
8,143
Office Version
  1. 365
Platform
  1. Windows
Try changing the lr line to:

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

Watch MrExcel Video

Forum statistics

Threads
1,118,134
Messages
5,570,364
Members
412,321
Latest member
Yusuf_A
Top