Forum Discussion

Haris66's avatar
Haris66
Copper Contributor
Apr 19, 2024

VBA copy data from another workbook (specific range) into a different workbook to a specific Sheet.

Hi All,

I am in a little bit of a pickle here and would appreciate any insight or help on this.

 

I Create the invoices of our customers on excel which is based on the data , Like Orders, Receipts , Storage, Freight Etc.

 

Each of the above mentioned file has sheet for individual customer data.

 

I would like to Automatically Copy the data from Order file for Customer A from Column A to AE and open the invoice file for Customer A and paste the data into order data Sheet.

 

This needs to happen to for each customer. I am having at 30 customers now and constantly growing.

 

Do let me know if you require any further information to help me create the VBA for this.

 

BI & Data Analysis
developer
excel
Macros and VBA
Power BI
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Apr 22, 2024

    Haris66 

    To accomplish this task using VBA, you will need to write a macro that loops through each customer, opens their respective order file, copies the data from the specified range, and then pastes it into the corresponding sheet in the invoice file.

    Here is a basic outline of the steps you will need to follow:

    1. Loop through each customer.
    2. Open the order file for the current customer.
    3. Copy the data from columns A to AE in the order file.
    4. Open the invoice file for the current customer.
    5. Paste the copied data into the appropriate sheet in the invoice file.

    Here is a sample VBA code that demonstrates how to achieve this:

    Vba code is untested, please backup your file first.

    Sub CopyDataToInvoice()
    Dim customerList As Variant
    Dim customer As Variant
    Dim orderFilePath As String
    Dim invoiceFilePath As String
    Dim orderWorkbook As Workbook
    Dim invoiceWorkbook As Workbook
    
    ' Define the list of customers
    customerList = Array("CustomerA", "CustomerB", "CustomerC") ' Add all your customers here
    
    ' Loop through each customer
    For Each customer In customerList
        ' Define the file paths for order and invoice files
        orderFilePath = "Path\to\Order\File\" & customer & "_Orders.xlsx"
        invoiceFilePath = "Path\to\Invoice\File\" & customer & "_Invoice.xlsx"
        
        ' Open the order file
        Set orderWorkbook = Workbooks.Open(orderFilePath)
        
        ' Copy data from columns A to AE in the order file
        orderWorkbook.Sheets("SheetName").Range("A:AE").Copy
        
        ' Close the order file
        orderWorkbook.Close False
        
        ' Open the invoice file
        Set invoiceWorkbook = Workbooks.Open(invoiceFilePath)
        
        ' Paste the copied data into the appropriate sheet in the invoice file
        invoiceWorkbook.Sheets("OrderData").Range("A1").PasteSpecial Paste:=xlPasteValues
        
        ' Close the invoice file
        invoiceWorkbook.Close True
    Next customer
End Sub

    Make sure to replace "Path\to\Order\File\", "Path\to\Invoice\File\", "CustomerA", "CustomerB", "CustomerC", "SheetName", and "OrderData" with the actual paths, customer names, and sheet names in your files.

    Also, ensure that you save the Excel files as macro-enabled (.xlsm) files if they contain VBA code. The text, steps and code were created with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

Resources