Pointing in the right direction

[mattless1]

Hi Everyone,

i'm looking for some advice.

i have a workbook which i use as my invoicing. Before i save the workbook i would like to copy the data from different cells lets say A5,B9,C1,R4, and a few others into a master workbook on a row, but all on one line as order one & the same for order two on row A2 and so on.

Hope that makes sense?

what would is the best way to copy the data across to a master sheet cell by cell and move to the next row for order 2.

Thanks in advance.
Kind Regards,
Larry.

 

[shknbk2]

You can use this code for each invoice to add to the next row. It may not be a final solution, but it might get close. Any difference with what you need will have to be fleshed out based on more information.

You need to modify to correctly identify the Master workbook's filename, the sheet name in the Master that will store the list, and the cells you want to copy from.

Sub CopyToMaster()
    Dim wMaster As Workbook, wMasterStr As String
    Dim shInvoice As Worksheet
    Dim shMaster As Worksheet, shMasterStr As String
    Dim aCells() As String
    Dim sourceCells As String
    Dim nextRow As Range
    Dim i As Integer
    
    'Name of Master workbook
    [COLOR=#0000ff][B]wMasterStr [/B][/COLOR]= "Master.xlsx"
    
    'Name of sheet in Master Workbook
    [COLOR=#0000ff][B]shMasterStr [/B][/COLOR]= "Sheet1"
    
    'Cells you want to copy from and paste into single row
    [COLOR=#0000ff][B]sourceCells [/B][/COLOR]= "A5,B9,C1,R4"
    
    aCells = Split(sourceCells, ",")
    Set shInvoice = ActiveSheet
    For Each wMaster In Workbooks
        If wMaster.Name = wMasterStr Then
            Exit For
        End If
    Next
    
    If wMaster Is Nothing Then
        MsgBox "Please open the master workbook first.", , wMasterStr & " not open"
        Exit Sub
    End If
    
    Set shMaster = wMaster.Sheets(shMasterStr)
    Set nextRow = shMaster.Cells(shMaster.Rows.Count, 1).End(xlUp)
    If nextRow.Value <> "" Then
        Set nextRow = nextRow.Offset(1, 0)
    End If
    nextRow.Value = nextRow.Row
    For i = 0 To UBound(aCells)
        shInvoice.Range(aCells(i)).Copy nextRow.Offset(0, i + 1)
    Next
    
End Sub

 

[mattless1]

Hi Shknbk2,

Many thanks for your reply, i'll give this a try. I might need to come back and ask a few more questions.

i appreciate you taking the time to help me out.

Kind Regards,
Mattless

 

[mattless1]

Hi Shknbk2,

i have tested the code and it works perfect.
is there a way i can update the workbook without it being open as this will be on a main server and the invoice sheet is in a different location.

I must say i'm very please with the code you gave me. Once i adapted it to what i needed it is perfect.

A massive thanks to you.

Kind Regards,
Mattless

 

[shknbk2]

You're very welcome, and thanks for the feedback.

is there a way i can update the workbook without it being open

Do you mean that the code should open the workbook if it is not open (as opposed to the code right now showing the messagebox)?
If so, I think all we'll need is the location of the Master and some code to open it before updating.

 

[mattless1]

Thanks again,
Im currently using this code below on a separate macro to open it, but would like it to close after data import.

Can ithe code below be inserted into the code above.

Sub OpenWorkbookToVariable()

    Dim wb As Workbook
    Set wb = Workbooks.Open("Z:\Master.xlsx")
 
    wb.Save

Kind Regards,
Mattless

 

[shknbk2]

It can, but it might be cleaner to keep using the code you have and call the code above after opening Master. Plus, it eliminates the code in my procedure from having to find Master.

Sub OpenWorkbookToVariable()
    Dim wb As Workbook
    Set wb = Workbooks.Open("Z:\Master.xlsx")
    CopyToMaster wb
    wb.Save
    wb.Close
End Sub

Sub CopyToMaster(wMaster As Workbook)
    Dim wMasterStr As String
    Dim shInvoice As Worksheet
    Dim shMaster As Worksheet, shMasterStr As String
    Dim aCells() As String
    Dim sourceCells As String
    Dim nextRow As Range
    Dim i As Integer
    
    'Name of sheet in Master Workbook
    shMasterStr = "Sheet1"
    
    'Cells you want to copy from and paste into single row
    sourceCells = "A5,B9,C1,R4"
    
    aCells = Split(sourceCells, ",")
    Set shInvoice = ActiveSheet
    
    Set shMaster = wMaster.Sheets(shMasterStr)
    Set nextRow = shMaster.Cells(shMaster.Rows.Count, 1).End(xlUp)
    If nextRow.Value <> "" Then
        Set nextRow = nextRow.Offset(1, 0)
    End If
    nextRow.Value = nextRow.Row
    For i = 0 To UBound(aCells)
        shInvoice.Range(aCells(i)).Copy nextRow.Offset(0, i + 1)
    Next
End Sub