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!

    Hello,

    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,
    Veed
    Attached Files Attached Files

  2. #2
    Code:
    Public Sub SalesSummary()
    Const FORMULA_BRAND As String = _
        "=INDEX(sales!R1C3:R<sales>C3,MATCH(1,(sales!R1C2:R<sales>C2=RC2)*(sales!R1C5:R<sales>C5=RC5),0))"
    Const FORMULA_TYPE As String = _
        "=INDEX(sales!R1C4:R<sales>C4,MATCH(1,(sales!R1C2:R<sales>C2=RC2)*(sales!R1C5:R<sales>C5=RC5),0))"
    Const FORMULA_SALES As String = _
        "=IFERROR(INDEX(sales!R1C6:R<sales>C6,MATCH(1,(sales!R1C2:R<sales>C2=RC2)*(sales!R1C5:R<sales>C5=RC5)*(sales!R1C7:R<sales>C7=R1C),0)),"""")"
    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
        
            .UsedRange.ClearContents
            
            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)
            .Columns("A:E").EntireColumn.AutoFit
        
            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("F:F").Cut
            .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
  •