VBA coding

[Aberdham]

Hi there,

I tried to combine sheets with different columns to a new sheet. and ran the code. but it says user-type not defined.

following link to the workbook: https://1drv.ms/x/s!Aqt4VfikFsyKdP__4BbJF1LMxlM

could anyone help me to run a debug?

Thank you in advance !

 

[mumps]

Re: Formula split and update master tables into sub tables(product) - Aggregate/replace

If the WIP column does not contain a number that corresponds a number in one of the Overview columns, the macro will generate an error, so replacing the blank with a zero will not work. If you need to have a blank cell in the WIP column, I can have the macro ignore that row so it wouldn't be copied to the Overview sheet. Would that work for you?

 

[Aberdham]

Re: Formula split and update master tables into sub tables(product) - Aggregate/replace

No wonder that returns an error.

That would be perfect !!

Thank you very much !!

 

[mumps]

Re: Formula split and update master tables into sub tables(product) - Aggregate/replace

Replace the current section of code at the end of the macro with this:

For Each ws In Sheets(Array("Purchase Costs", "Revenue Costs"))
    For Each WIP In ws.Range("I2:I" & ws.Range("I" & ws.Rows.Count).End(xlUp).Row)
        If WIP <> "" Then
            WIP.EntireRow.Copy Sheets("Overview WIP " & WIP.Value).Cells(Sheets("Overview WIP " & WIP.Value).Range("B" & Sheets("Overview WIP " & WIP.Value).Rows.Count).End(xlUp).Row + 1, 1)
        End If
    Next WIP
Next ws

Don't fill the blank WIP's with zeros. Just leave the cells blank.

 

[Aberdham]

Re: Formula split and update master tables into sub tables(product) - Aggregate/replace

I’ll run the code in a bit and post back with feedback. Thank you

 

[Aberdham]

Re: Formula split and update master tables into sub tables(product) - Aggregate/replace

Everything works perfectly except for the EUR side, on the purchase costs sheet, it is not able to filter the date, they just copy everything there, I changed the date format, and it still won't get the job done. perhaps it is possible to format the date as certain format in the eur side in the code?

 

[mumps]

Re: Formula split and update master tables into sub tables(product) - Aggregate/replace

When I tried this macro on the 3 most recent files, it did exactly what you requested. At this point, I'm not sure what else I can do.

Sub CopyCols()
    Application.ScreenUpdating = False
    Dim SLIsh As Worksheet, SMIsh As Worksheet, DATAsh As Worksheet, MASTERsh As Worksheet
    Set SLIsh = ThisWorkbook.Sheets("Sales lease invoice$")
    Set SMIsh = ThisWorkbook.Sheets("Sales material invoice$")
    Set DATAsh = Workbooks("Sales Report YTD.xlsx").Sheets("DATA")
    Set MASTERsh = Workbooks("master.xlsx").Sheets("Sheet1")
    SLIsh.UsedRange.Offset(1, 0).ClearContents
    SMIsh.UsedRange.Offset(1, 0).ClearContents
    Dim beginDate As String, endDate As String
    Dim bottomA As Long, bottomB As Long, x As Long, lastRow As Long, bottomF As Long, total As Long
    Dim des As Range, rngUniques As Range, inv As Range
    x = 2
    Dim WIP As Range
    Dim ws As Worksheet
    For Each ws In Sheets
        If ws.Name = "Sales lease invoice$" Then
            bottomA = MASTERsh.Range("A" & MASTERsh.Rows.Count).End(xlUp).Row
            Intersect(MASTERsh.Rows("2:" & bottomA), MASTERsh.Range("D:D")).Copy ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1, 0)
            Intersect(MASTERsh.Rows("2:" & bottomA), MASTERsh.Range("G:G")).Copy ws.Cells(ws.Rows.Count, "E").End(xlUp).Offset(1, 0)
            Intersect(MASTERsh.Rows("2:" & bottomA), MASTERsh.Range("H:I")).Copy ws.Cells(ws.Rows.Count, "C").End(xlUp).Offset(1, 0)
            Intersect(MASTERsh.Rows("2:" & bottomA), MASTERsh.Range("J:K")).Copy ws.Cells(ws.Rows.Count, "Z").End(xlUp).Offset(1, 0)
            Intersect(MASTERsh.Rows("2:" & bottomA), MASTERsh.Range("L:L")).Copy ws.Cells(ws.Rows.Count, "L").End(xlUp).Offset(1, 0)
            Intersect(MASTERsh.Rows("2:" & bottomA), MASTERsh.Range("M:M")).Copy ws.Cells(ws.Rows.Count, "K").End(xlUp).Offset(1, 0)
            Intersect(MASTERsh.Rows("2:" & bottomA), MASTERsh.Range("P:P")).Copy ws.Cells(ws.Rows.Count, "G").End(xlUp).Offset(1, 0)
        ElseIf ws.Name = "Sales material invoice$" Then
            bottomA = DATAsh.Range("A" & DATAsh.Rows.Count).End(xlUp).Row
            DATAsh.Range("A1:A" & bottomA).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1:A" & bottomA), Unique:=True
            Set rngUniques = DATAsh.Range("A2:A" & bottomA).SpecialCells(xlCellTypeVisible)
            DATAsh.Range("A2:A" & bottomA).SpecialCells(xlCellTypeVisible).Copy ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1, 0)
            DATAsh.Range("B2:C" & bottomA).SpecialCells(xlCellTypeVisible).Copy ws.Cells(ws.Rows.Count, "E").End(xlUp).Offset(1, 0)
            DATAsh.Range("L2:L" & bottomA).SpecialCells(xlCellTypeVisible).Copy ws.Cells(ws.Rows.Count, "K").End(xlUp).Offset(1, 0)
            DATAsh.Range("P2:P" & bottomA).SpecialCells(xlCellTypeVisible).Copy ws.Cells(ws.Rows.Count, "Y").End(xlUp).Offset(1, 0)
            DATAsh.Range("S2:S" & bottomA).SpecialCells(xlCellTypeVisible).Copy
            ws.Cells(ws.Rows.Count, "L").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            If DATAsh.FilterMode Then DATAsh.ShowAllData
            bottomF = SMIsh.Range("F" & SMIsh.Rows.Count).End(xlUp).Row
            For Each des In SMIsh.Range("F4:F" & bottomF)
                des = "PO" & des
            Next des
            For Each inv In rngUniques
                DATAsh.Range("A1:S" & bottomA).AutoFilter Field:=1, Criteria1:=inv
                ws.Cells(ws.Rows.Count, "G").End(xlUp).Offset(1, 0) = WorksheetFunction.Sum(DATAsh.Range("I2:I" & bottomA).SpecialCells(xlCellTypeVisible))
                
                If DATAsh.FilterMode Then DATAsh.ShowAllData
            Next inv
        End If
    Next ws
End Sub

 

[Aberdham]

Re: Formula split and update master tables into sub tables(product) - Aggregate/replace

I believe it is the date formating is playing up.

Is it also possible to maintain the history data and update the new entries instead of overwrites them?

 

[mumps]

Re: Formula split and update master tables into sub tables(product) - Aggregate/replace

I was hoping we could take this one step at a time. Don't run the complete macro that includes the prompt for the dates. Place the macro from Post #46 in a separate module and run it from there. Have a look at the results and see if they are what you want and let me know.

 

[Aberdham]

Re: Formula split and update master tables into sub tables(product) - Aggregate/replace

Great, I will post back

thank you.

 

[Aberdham]

Re: Formula split and update master tables into sub tables(product) - Aggregate/replace

Hi Mumps,

I did a few attempt to run the code but it indicates variable not defined?

bottomA = MASTERsh.Range("A" & MASTERsh.Rows.Count).End(xlUp).Row

I believe we Forget to set the Input wb as "Revenue" ?