CODEWITHSUNDEEP
excelvba
Akshay Chari
Akshay Chari

Reputation: 43

VBA Macro that transfer data from 1 excel to another

I am new to excel macro vba please help.... My below code is to transfer data from 1 excel to another. However, when I execute the macro it transfers only 1 row i.e. A2 and B2 cell values into another excel.

This the main excel from where the data goes to another

Output excel where it transfers only 1 row..

My VBA code....

Private Sub CommandButton1_Click()

Dim InvoiceNumber As String
Dim ForwarderCode As String
Dim wb As Workbook

Worksheets("SampleFile").Select
InvoiceNumber = Range("A2")
Worksheets("SampleFile").Select
ForwarderCode = Range("B2")

Set wb = Workbooks.Open("C:DestinationPath.xlsm")
Worksheets("Sheet1").Select
Worksheets("Sheet1").Range("A2").Select
RowCount = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Sheet1").Range("A1")
.Offset(RowCount, 0) = InvoiceNumber
.Offset(RowCount, 1) = ForwarderCode
End With

wb.Save

End Sub

Please help it should transfer the entire data present in column A and B....

Upvotes: 0

Views: 269

Answers (1)

FaneDuru
FaneDuru

Reputation: 42256

If you want copying from A1 to C last filled cell, try the next code, please:

Private Sub CommandButton1_Click___()
 Dim shSF As Worksheet, sh1 As Worksheet
 Dim wb As Workbook, lastRow As Long

 Set shSF = Worksheets("SampleFile")
 lastRow = shSF.Range("A" & Rows.count).End(xlUp).row

 Set wb = Workbooks.Open("C:DestinationPath.xlsm")
 Set sh1 = wb.Worksheets("Sheet1")
 
 shSF.Range("A1:C" & lastRow).Copy Destination:=sh1.Range("A1")

 wb.Save
End Sub

Upvotes: 1

Related Questions