Update data from copy-paste in excel is located on bellow the old data. How can I change the locatio-CodePudding

I got some problem on excel macro and the code like this :

Sub Backup_button1()

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

    'set variable for copy and destination sheets
    Set wsCopy = Workbooks("Form Input SAP.xlsm").Worksheets("7-9")
    Set wsDest = Workbooks("File Backup.xlsx").Worksheets("7-9")
    
    '1. Find last used row in the copy range based on data in column
    lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "H").End(xlUp).Row
    
    '2. Find first blank row in the destination range based on data in colom Offset property move down 1 row
    lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "H").End(xlUp).Offset(1).Row
    
    '3. Copy & Paste Data
    wsCopy.Range("H4:N" & lCopyLastRow).Copy
      wsDest.Range("B" & lDestLastRow).PasteSpecial Paste:=xlPasteValues
      
    
End Sub

Those code is used to copy some data in excel workbooks and paste to different workbooks. If there some old data in destination file, it will make an update bellow the old data. The code is work properly, but I want to change update location not in bellow old data but it will located in cells after the old data. Any advice?

CodePudding user response：

Change your code:

Const DestRow = 4

'2. Find first blank column in the destination range
lDestLastCol = wsDest.Cells(DestRow, wsDest.Columns.Count).End(xlToLeft).Column

'3. Copy & Paste Data
wsCopy.Range("H4:N" & lCopyLastRow).Copy
wsDest.cells(DestRow, lDestLastCol 1).PasteSpecial Paste:=xlPasteValues

Step 2 will give you the last column in use, Step 3 will copy the data into the next free column. I have assumed that in the destination sheet your data also starts at row 4, else you have to change the const definition.