'Insert offset method, in working code to copy data, to copy to next empty row
I am copying data from two workbooks to another workbook.
The code written by me works and serves the purpose.
I am having difficulty getting the syntax for using the offset method to copy to next empty row after the first paste.
With wsdly
lrowdly = Cells(Rows.Count, 2).End(xlUp).Row
.Range("A2:O" & lrowdly).ClearContents
wsb.Range("A2:O" & lrowb).Copy .Range("A2")
End With
With wsdly
lrowdly2 = Cells(Rows.Count, 2).End(xlUp).Row
lrowdly3 = lrowdly2 + 1
wsn.Range("A2:O" & lrown).Copy .Range("A" & lrowdly3)
End With
Whole program.
Sub copy_bond_dat()
Dim wbb As Workbook
Dim wbn As Workbook
Dim wbdly As Workbook
Dim wsb As Worksheet
Dim wsn As Worksheet
Dim wsdly As Worksheet
Set wbb = Workbooks("BSE_BOND.xlsm")
Set wbn = Workbooks("NSE_BOND.xlsm")
Set wbdly = Workbooks("Dly_Debt_Trnx_2022_TMP.xlsx")
Set wsb = wbb.Worksheets("BSEDATA")
Set wsn = wbn.Worksheets("NSEDATA")
Set wsdly = wbdly.Worksheets("Dly_Debt_Trnx_2022_TMP")
Dim lrowb As Long
Dim lrown As Long
Dim lrowdly As Long
Dim lrowdly2 As Long
Dim lrowdly3 As Long
With wsb
lrowb = Cells(Rows.Count, 2).End(xlUp).Row
End With
With wsn
lrown = Cells(Rows.Count, 2).End(xlUp).Row
End With
With wsdly
lrowdly = Cells(Rows.Count, 2).End(xlUp).Row
.Range("A2:O" & lrowdly).ClearContents
wsb.Range("A2:O" & lrowb).Copy .Range("A2")
End With
With wsdly
lrowdly2 = Cells(Rows.Count, 2).End(xlUp).Row
lrowdly3 = lrowdly2 + 1
wsn.Range("A2:O" & lrown).Copy .Range("A" & lrowdly3)
End With
wbdly.Close
End Sub
Solution 1:[1]
Changing the mentioned code lines to following code lines problem gets resolved.
With wsdly
lrowdly = .Cells(Rows.Count, 2).End(xlUp).Row
.Range("A2:O" & lrowdly).ClearContents
wsb.Range("A2:O" & lrowb).Copy .Range("A2")
wsn.Range("A2:O" & lrown).Copy
.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End With
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |
