Results 1 to 2 of 2

Thread: Need help in automating data from multiple sheets into a single sheet

  1. #1

    Need help in automating data from multiple sheets into a single sheet

    Register for a FREE account, and/
    or Log in to avoid these ads!


    I am new to excel and trying to figure out the best way to put this together. I have mockup data (file attached) from two tabs - sales and inventory. I would have to combine sales and inventory data and put it together in Summary tab. Mockup data is small but real data runs into thousands of rows. Hence planning to automate this.

    Any help of this would be highly appreciated.

    Thanks in advance,
    Attached Files Attached Files

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version
    Public Sub SalesSummary()
    Const FORMULA_BRAND As String = _
    Const FORMULA_TYPE As String = _
    Const FORMULA_SALES As String = _
    Dim wsSumm As Worksheet
    Dim wsSales As Worksheet
    Dim wsInv As Worksheet
    Dim lastsales As Long
    Dim lastrow As Long
    Dim lastcol As Long
    Dim i As Long
        Application.ScreenUpdating = False
        Set wsSumm = Worksheets("Summary")
        Set wsSales = Worksheets("sales")
        lastsales = wsSales.Range("A1").End(xlDown).Row
        Set wsInv = Worksheets("inventory")
        With wsSumm
            wsInv.Range("A1:D7").Copy .Range("A1")
            .Columns("C:C").Resize(, 2).Insert Shift:=xlToRight
            lastrow = .Range("A1").End(xlDown).Row
            .Range("C1:D1").Value = Array("Brand", "Type")
            .Range("C2").FormulaR1C1 = Replace(FORMULA_BRAND, "<sales>", lastsales)
            .Range("C2").FormulaArray = .Range("C2").Formula
            .Range("C2").AutoFill .Range("C2").Resize(lastrow - 1)
            .Range("D2").FormulaR1C1 = Replace(FORMULA_TYPE, "<sales>", lastsales)
            .Range("D2").FormulaArray = .Range("D2").Formula
            .Range("D2").AutoFill .Range("D2").Resize(lastrow - 1)
            wsSales.Range(wsSales.Range("G2"), wsSales.Range("G2").End(xlDown)).Copy
            .Range("G2").PasteSpecial Paste:=xlPasteAll
            .Range(.Range("$G$2"), .Range("$G$2").End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo
            .Range(.Range("$G$2"), .Range("$G$2").End(xlDown)).Sort Key1:=.Range("G2"), Order1:=xlAscending, Header:=xlNo
            .Range(.Range("$G$2"), .Range("$G$2").End(xlDown)).Copy
            .Range("G1").PasteSpecial Paste:=xlPasteAll, Transpose:=True
            lastcol = .Range("A1").End(xlToRight).Column
            .Columns(lastcol + 1).Insert Shift:=xlToRight
            .Range("F2").FormulaR1C1 = Replace(FORMULA_SALES, "<sales>", lastsales)
            .Range("F2").FormulaArray = .Range("F2").Formula
            .Range("F2").AutoFill Destination:=.Range("F2").Resize(lastrow - 1), Type:=xlFillDefault
            .Range("F2").Resize(lastrow - 1).AutoFill Destination:=.Range("F2").Resize(lastrow - 1, lastcol - 6), Type:=xlFillDefault
        End With
        Application.ScreenUpdating = True
        Application.CutCopyMode = False
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts