CODEWITHSUNDEEP
excelvba
10101
10101

Reputation: 2402

Copy cells values in to next empty row to another workbook vba

I have two separate Excel files. In one of these in Sheet1 is stored infomration about orders and order numbers. Now every time I make a new order I want this information be collected from my order and inserted in to so called "database" workbook. It should identify the last empty row in column A:A in C:\Users\user\Desktop\Order_number.xlsx and insert new values from range ("C6,C17,C10,H18,B32,G32,H6,H9") to the next empty row. Here is the code I came up to but there is some mistake and it is not working. How it can be fixed?

    Sub TransferValues465()

Dim wsMain As Worksheet: Set wsMain = ThisWorkbook.ActiveSheet
Dim wsData As Worksheet: Set wsData = Workbooks.Open("C:\Users\user\Desktop\Order_number.xlsx").Sheets("Sheet1")
Dim rngToCopy As Range: Set rngToCopy = wsMain.Range("C6,C17,C10,H18,B32,G32,H6,H9")
Dim c As Long
Dim ar As Range
Dim cl As Range

Dim LastRow As Long

Dim rngDestination As Range

With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
End With

'Get the last row in Database sheet:
LastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
Set rngDestination = wsData.Cells(LastRow + 1, 1).Resize(1, 25).Offset(0, 0)

For Each ar In rngToCopy.Areas
    For Each cl In ar
        c = c + 1
        'I used this next line for testing:
        '  rngDestination.Cells(c).Value = cl.Address
        rngDestination.Cells(c).Value = cl.Value
    Next
Next

End Sub

Upvotes: 0

Views: 3482

Answers (1)

Shai Rado
Shai Rado

Reputation: 33672

A few corrections:

1) Set wsData = Workbooks("C:\Users\user\Desktop\Order_number.xlsx").Sheets("Sheet1") will not work. Either use Set wsData = Workbooks("Order_number.xlsx").Sheets("Sheet1") if the workbook is open. Or you need to open the workbook first.

2) I am not famliar on using Application.WorksheetFunction.CountA(wsData.Range("A:A")) to get the last row. To get the last row in Column A (with the possibility of skipping balnk cells in the middle) use wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row.

3) My preference is to use Copy >> PasteSpecial xlPasteValues with cl.Copy and the following line wsData.Range("A" & C).PasteSpecial xlPasteValues.

Code

Option Explicit

Sub TransferValues465()

Dim wsMain As Worksheet
Dim wbData As Workbook
Dim wsData As Worksheet
Dim rngToCopy As Range
Dim C As Long
Dim ar As Range
Dim cl As Range

Dim LastRow As Long
Dim rngDestination As Range

Set wsMain = ThisWorkbook.ActiveSheet

Application.DisplayAlerts = False
' you need to open the workbook
Set wbData = Workbooks.Open("C:\Users\user\Desktop\Order_number.xlsx")
Set wsData = wbData.Sheets("Sheet1")
Set rngToCopy = wsMain.Range("C6,C17,C10,H18,B32,G32,H6,H9")

'Get the last row in Database sheet:
LastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row

C = 1
For Each cl In rngToCopy
    cl.Copy
    wsData.Cells(LastRow + 1, C).PasteSpecial xlPasteValues
    C = C + 1
Next cl    

wbData.Close True '<-- close and save the changes made
Application.DisplayAlerts = True '<-- restore settings

End Sub

Upvotes: 1

Related Questions