Posts by VbaNoExcel

VbaNoExcel · Aug 2nd 2021

Hi Roy

I know i used tables first to solve this and then i got told not to use tables. I know it sounds silly but we are not allowed to use table as it has limitations when using the links and using tables for various models that we have built in my organization. we have been asked not to use tables to ensure all teams follow same format. some rules are hard to explain!

I was wondering if you or anyone could help this with VBA codes so that i can automate this process.

VbaNoExcel · Jul 31st 2021

Hello Good friends and folks

I have this file (attached here) that I have (kind of) automated based on my (little) excel skills. However, I was wondering if anyone with a kind heart would be able to help me automate the formulae and the file itself with VBA. I want to automate the formula-driven sheets with the freedom of increasing rows and columns (i mean to say adding or deleting rows and columns).

Many thanks in advance for your kind help.

VbaNoExcel · Jul 15th 2021

hi mumps, many thanks for your kind help

VbaNoExcel · Jul 13th 2021

hey mumps, here is the file again. i got rid of the column A and Column AT in "data" tab and attached the new look file here for your convenience.

VbaNoExcel · Jul 13th 2021

hi mumps

many thanks for your kind help. Working like a charm. one small thing.............In my effort to reduce some repeat commands, i realized that the formula/ macro for Column A and AT in "data" tab can just be in Column B and Column AU of "output" tab. is there any way to incorporate this change in the 2nd Macros that you sent? (this way i will not need the 1st macros and just one macro will deliver everything neatly).

VbaNoExcel · Jul 12th 2021

hello good people
i have attached my file here. currently i am pulling data in "File_1.xlsx" (file attached) tab named "Output" tab by formula but as i add more data, i can see that the file will become larger and slower. therefore, wondering if you folks could help me to automate by writing macros to pull data into "OUTPUT" tab from "Data" tab (you can see how i pulling data with Sumproduct formula) . My request is below:

I need a Macro that will do exactly what I am doing with the "SUMPRODUCT" formula from cell F5 to AU9 in the "OUTPUT" tab.

I need to have the flexibility of adding/ deleting more rows or columns as I add/ delete more/ some rows for "Cost_Centre", "Accounts", "Business_Line".

Is it possible to automate the formula in this Column B?

VbaNoExcel · Nov 17th 2017

hi JOLIVANES

It is working!!!!!!!!!!!!!!!!!!! many thanks, bro!!!!!!!!!!!!!!!!!!!! u rock and hats off to you.....I will come back and ask you for help again in case it stops working when i change the number of rows and columns.

Quote from jolivanes

You could try this

Code

Sub AAAAA()
Dim sh1 As Worksheet, i As Long, ColArr, j As Long, c As Range, lr As Long


ColArr = Array(4, 7, 8)
Set sh1 = Worksheets("Consolidated")


Sheets("Product1").Range("A1:H1").Copy sh1.Range("A1")


For i = 3 To ThisWorkbook.Sheets.Count '<--- Change 3 to the sheet from where you start (probably 2)
Sheets(i).UsedRange.Offset(2).Copy sh1.Cells(Rows.Count, 1).End(xlUp).Offset(1)
Next i


lr = sh1.Cells(sh1.Rows.Count, 1).End(xlUp).Row


For j = LBound(ColArr) To UBound(ColArr)
With sh1
For Each c In .Range(.Cells(1, ColArr(j)), .Cells(lr, ColArr(j)))
If Len(c) < 1 Then c.Value = c.Offset(, -1).Value
Next c
End With
Next j


End Sub

Display More

VbaNoExcel · Nov 17th 2017

hi JOLIVANES

many thanks. let me try and i will come back if i stumble upon anything. just wondering if the codes also take care fo flexibility for rows and columns. I mean, i might add more rows and columns or have less rows and columns depending on situation. will this hadle that as well? sorry for not being clear in the first time.

Quote from jolivanes

You could try this

Code

Sub AAAAA()
Dim sh1 As Worksheet, i As Long, ColArr, j As Long, c As Range, lr As Long


ColArr = Array(4, 7, 8)
Set sh1 = Worksheets("Consolidated")


Sheets("Product1").Range("A1:H1").Copy sh1.Range("A1")


For i = 3 To ThisWorkbook.Sheets.Count '<--- Change 3 to the sheet from where you start (probably 2)
Sheets(i).UsedRange.Offset(2).Copy sh1.Cells(Rows.Count, 1).End(xlUp).Offset(1)
Next i


lr = sh1.Cells(sh1.Rows.Count, 1).End(xlUp).Row


For j = LBound(ColArr) To UBound(ColArr)
With sh1
For Each c In .Range(.Cells(1, ColArr(j)), .Cells(lr, ColArr(j)))
If Len(c) < 1 Then c.Value = c.Offset(, -1).Value
Next c
End With
Next j


End Sub

Display More

VbaNoExcel · Nov 17th 2017

Quote from nilem

On all sheets are always 3 Sub_Product_Groups, and 3 Customer_SubGroups, right?

Hi Milem
The number of products and Subproducts will go up or down based on each Salesperson's Sales target. For example, One Excel file may have 10 products and 20 subproducts...this means, l like to have dynamic columns and rows for each tab so that i can use it universally for everyone.

VbaNoExcel · Nov 16th 2017

just writing again to check if this can be solved with kind help from you guys......

VbaNoExcel · Nov 8th 2017

Hello folks, I was wondering if it is possible to help at all....

VbaNoExcel · Nov 7th 2017

Hello Good People

Just need a BIG help for a task. I have attached the Excel Sheet here.

I have multiple worksheets (Product1, Product 2, Product3 and new worksheets will be added as I add new products) and also have multiple sub categories (e.g. a Product has Multiple sub products and a Customer Group has multiple Sub Customer Groups which varies based on products, this will also extend/ change based on many/ few sub categories) .

I want to show all the tabs' information in one "Consolidated" tab where the rows and columns are dynamic based on Sub categories and number of rows.

If in the "Product" tab, any "Sub Product" or "Sub Customer" category is blank…then for the "Consolidated" tab, it should be filled by the previous category's description like those highlighted in green in the "Manual_Consolidated" tab.

I have done the "Manual_Consolidated" tab to show you guys what i want to accomplish in the "Macros_Consolidated" tab.

I hope i do not sound too confusing....happy to clarify again if needed.

Many thanks in advance for your kind help.

VbaNoExcel · Nov 3rd 2016

Re: How to transpose with multiple conditions

Quote from AlanSidman;780419

below I have added comments to the code to explain exactly what is happening.

Hi AlanSidman

Many thanks, this really helps, easy to understand (at least for me). have a great day, Sir!

VbaNoExcel · Nov 2nd 2016

Re: How to transpose with multiple conditions

Quote from AlanSidman;780373

Simple answer is NO. VBA is a logical step by step programming language. Of course you can always record a macro for simple issues, but this one involves looping through a spreadsheet, copying data and then pasting special transposed. It then does a loop to fill the blank spaces and formats certain columns. If you are interested in learning VBA, then I would start with some simple tasks, record the macro and then modify it for more advanced features.

Hi AlanSidman

Okay, got it, will try again to see if i can understand it, wish there were easy codes for people like me : )

VbaNoExcel · Nov 1st 2016

Re: How to transpose with multiple conditions

Hi

Is there any easy way to write these codes, i mean to say without any fancy command like formatting, etc. As i am new to VBA World, i just want to have the codes that are required to finish the job, nothing more, the original code that i got does the job beautifully but i hardly understand it. is it possible to get an easy VBA "Sub-End Sub" Code to complete this job.

Quote from AlanSidman;780292

Ok. give this a try

Code

Option Explicit




Sub transp()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Raw_Data")
    Set s2 = Sheets("Template_VBA")
    Dim lr As Long
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    Dim lrT As Long, i As Long
    Dim rng As Range
    Set rng = s1.Range("C3:AL3")
    Application.ScreenUpdating = False
    With s1
        For i = 4 To lr
            lrT = s2.Range("D" & Rows.Count).End(xlUp).Row
            .Range("A" & i & ":B" & i).Copy s2.Range("A" & lrT + 1)
            rng.Copy
            s2.Range("C" & lrT + 1).PasteSpecial xlPasteValues, , , True
            .Range("C" & i & ":AL" & i).Copy
            s2.Range("D" & lrT + 1).PasteSpecial xlPasteValues, , , True
        Next i
    End With
    Application.CutCopyMode = False




    With s2
        lrT = .Range("D" & Rows.Count).End(xlUp).Row
        For i = 3 To lrT
            .Range("C2:C" & lrT).NumberFormat = "mm/dd/yyyy"
            .Range("D2:D" & lrT).NumberFormat = "$#,##0."
            If .Range("A" & i) = "" Then
                .Range("A" & i) = .Range("A" & i - 1)
                .Range("B" & i) = .Range("B" & i - 1)
            End If
        Next i
    End With
    Application.ScreenUpdating = True
    MsgBox "complete"
End Sub

Display More

VbaNoExcel · Nov 1st 2016

Re: How to transpose with multiple conditions

Hello AlanSidman

It is working! I have to make some changes to fit to real data but i think i can handle it. Many thanks again for your help!!

VbaNoExcel · Nov 1st 2016

Re: How to transpose with multiple conditions

Hello AlanSidman

Many thnaks for your quick and kind help. Let me run this and i will come back with the result.

VbaNoExcel · Oct 31st 2016

Hi, i am new to VBA and can't get my head work to figure out simple things for VBA.

i have a data sheet from which i want to show the Data and $$ for Same Product with Same Color in one column rather than in different rows. So, what i have now is below table:

FYI, i have attached the original File here if you could spare some time for the whole file.

[TABLE="width: 609"]

[tr]

[td][/td]

[td]

Sold_On/ Amount

[/td]

[td]

Sold_On/ Amount

[/td]

[td]

Sold_On/ Amount

[/td]

[td]

Sold_On/ Amount

[/td]

[/tr]

[tr]

[td]

Color

[/td]

[td]

Product

[/td]

[TD="align: right"]1/1/2014[/TD]
[TD="align: right"]2/1/2014[/TD]
[TD="align: right"]3/1/2014[/TD]
[TD="align: right"]4/1/2014[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]$14,108[/TD]
[TD="align: right"]$19,375[/TD]
[TD="align: right"]$18,319[/TD]
[TD="align: right"]$11,370[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]$19,992[/TD]
[TD="align: right"]$11,263[/TD]
[TD="align: right"]$11,632[/TD]
[TD="align: right"]$5,545[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Socks

[/td]

[TD="align: right"]$13,614[/TD]
[TD="align: right"]$18,986[/TD]
[TD="align: right"]$17,273[/TD]
[TD="align: right"]$17,886[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Bandpipe

[/td]

[TD="align: right"]$6,936[/TD]
[TD="align: right"]$1,818[/TD]
[TD="align: right"]$13,509[/TD]
[TD="align: right"]$3,038[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Wagon

[/td]

[TD="align: right"]$17,146[/TD]
[TD="align: right"]$8,041[/TD]
[TD="align: right"]$15,384[/TD]
[TD="align: right"]$15,725[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Train

[/td]

[TD="align: right"]$1,812[/TD]
[TD="align: right"]$12,081[/TD]
[TD="align: right"]$9,921[/TD]
[TD="align: right"]$8,938[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Horn

[/td]

[TD="align: right"]$5,200[/TD]
[TD="align: right"]$3,778[/TD]
[TD="align: right"]$13,833[/TD]
[TD="align: right"]$3,309[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Sweater

[/td]

[TD="align: right"]$2,766[/TD]
[TD="align: right"]$12,916[/TD]
[TD="align: right"]$16,832[/TD]
[TD="align: right"]$6,103[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Wafer

[/td]

[TD="align: right"]$2,109[/TD]
[TD="align: right"]$10,388[/TD]
[TD="align: right"]$8,722[/TD]
[TD="align: right"]$10,043[/TD]

[/tr]

[tr]

[td]

Blue

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]$18,017[/TD]
[TD="align: right"]$18,779[/TD]
[TD="align: right"]$13,261[/TD]
[TD="align: right"]$15,591[/TD]

[/tr]

[tr]

[td]

Blue

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]$17,144[/TD]
[TD="align: right"]$16,396[/TD]
[TD="align: right"]$8,211[/TD]
[TD="align: right"]$7,476[/TD]

[/tr]

[tr]

[td]

Blue

[/td]

[td]

Socks

[/td]

[TD="align: right"]$7,237[/TD]
[TD="align: right"]$6,707[/TD]
[TD="align: right"]$1,358[/TD]
[TD="align: right"]$10,067[/TD]

[/tr]

[tr]

[td]

Blue

[/td]

[td]

Bandpipe

[/td]

[TD="align: right"]$4,693[/TD]
[TD="align: right"]$19,866[/TD]
[TD="align: right"]$8,730[/TD]
[TD="align: right"]$5,143[/TD]

[/tr]

[tr]

[td]

Blue

[/td]

[td]

Wagon

[/td]

[TD="align: right"]$4,647[/TD]
[TD="align: right"]$7,614[/TD]
[TD="align: right"]$2,572[/TD]
[TD="align: right"]$1,812[/TD]

[/tr]

[tr]

[td]

Blue

[/td]

[td]

Train

[/td]

[TD="align: right"]$17,675[/TD]
[TD="align: right"]$217[/TD]
[TD="align: right"]$148[/TD]
[TD="align: right"]$4,965[/TD]

[/tr]

[tr]

[td]

Blue

[/td]

[td]

Horn

[/td]

[TD="align: right"]$15,810[/TD]
[TD="align: right"]$324[/TD]
[TD="align: right"]$12,806[/TD]
[TD="align: right"]$8,502[/TD]

[/tr]

[tr]

[td]

Blue

[/td]

[td]

Sweater

[/td]

[TD="align: right"]$17,516[/TD]
[TD="align: right"]$14,160[/TD]
[TD="align: right"]$19,169[/TD]
[TD="align: right"]$18,934[/TD]

[/tr]

[tr]

[td]

Blue

[/td]

[td]

Wafer

[/td]

[TD="align: right"]$12,210[/TD]
[TD="align: right"]$10,408[/TD]
[TD="align: right"]$19,003[/TD]
[TD="align: right"]$16,886[/TD]

[/tr]

[/TABLE]

What i wan to do is below (i have done it manually, silly me, as I can't figure out the VBA to do it):

[TABLE="width: 384"]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]1/1/2014[/TD]
[TD="align: right"]14108[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]2/1/2014[/TD]
[TD="align: right"]19375[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]3/1/2014[/TD]
[TD="align: right"]18319
[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]4/1/2014[/TD]
[TD="align: right"]11370[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]5/1/2014[/TD]
[TD="align: right"]3025[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]6/1/2014[/TD]
[TD="align: right"]17140[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]7/1/2014[/TD]
[TD="align: right"]8688[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]8/1/2014[/TD]
[TD="align: right"]15518[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]9/1/2014[/TD]
[TD="align: right"]13092[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]10/1/2014[/TD]
[TD="align: right"]7811[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]11/1/2014[/TD]
[TD="align: right"]19962[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]12/1/2014[/TD]
[TD="align: right"]2687[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]2198[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]2/1/2015[/TD]
[TD="align: right"]13660[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]3/1/2015[/TD]
[TD="align: right"]6092[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]4/1/2015[/TD]
[TD="align: right"]2307[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]5/1/2015[/TD]
[TD="align: right"]19864[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]6/1/2015[/TD]
[TD="align: right"]15256[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]7/1/2015[/TD]
[TD="align: right"]16101[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]8/1/2015[/TD]
[TD="align: right"]18928[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]9/1/2015[/TD]
[TD="align: right"]6507[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]10/1/2015[/TD]
[TD="align: right"]1807[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]11/1/2015[/TD]
[TD="align: right"]5512[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]12/1/2015[/TD]
[TD="align: right"]17524[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]1/1/2016[/TD]
[TD="align: right"]15540[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]2/1/2016[/TD]
[TD="align: right"]10616[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]3/1/2016[/TD]
[TD="align: right"]19571[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]4/1/2016[/TD]
[TD="align: right"]7746[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]5/1/2016[/TD]
[TD="align: right"]18545[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]6/1/2016[/TD]
[TD="align: right"]9065[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]7/1/2016[/TD]
[TD="align: right"]15921[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]8/1/2016[/TD]
[TD="align: right"]11860[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]9/1/2016[/TD]
[TD="align: right"]2991[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]10/1/2016[/TD]
[TD="align: right"]7884[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]11/1/2016[/TD]
[TD="align: right"]8290[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]12/1/2016[/TD]
[TD="align: right"]9462[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]1/1/2014[/TD]
[TD="align: right"]19992[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]2/1/2014[/TD]
[TD="align: right"]11263[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]3/1/2014[/TD]
[TD="align: right"]11632[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]4/1/2014[/TD]
[TD="align: right"]5545[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]5/1/2014[/TD]
[TD="align: right"]12444[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]6/1/2014[/TD]
[TD="align: right"]8074[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]7/1/2014[/TD]
[TD="align: right"]7390[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]8/1/2014[/TD]
[TD="align: right"]18958[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]9/1/2014[/TD]
[TD="align: right"]6413[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]10/1/2014[/TD]
[TD="align: right"]7494[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]11/1/2014[/TD]
[TD="align: right"]14962[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]12/1/2014[/TD]
[TD="align: right"]13126[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]18971[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]2/1/2015[/TD]
[TD="align: right"]7933[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]3/1/2015[/TD]
[TD="align: right"]8224[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]4/1/2015[/TD]
[TD="align: right"]4454[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]5/1/2015[/TD]
[TD="align: right"]7900[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]6/1/2015[/TD]
[TD="align: right"]19197[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]7/1/2015[/TD]
[TD="align: right"]15770[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]8/1/2015[/TD]
[TD="align: right"]10173[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]9/1/2015[/TD]
[TD="align: right"]6322[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]10/1/2015[/TD]
[TD="align: right"]1380[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]11/1/2015[/TD]
[TD="align: right"]19965[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]12/1/2015[/TD]
[TD="align: right"]3163[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]1/1/2016[/TD]
[TD="align: right"]5585[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]2/1/2016[/TD]
[TD="align: right"]17032[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]3/1/2016[/TD]
[TD="align: right"]16448[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]4/1/2016[/TD]
[TD="align: right"]1686[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]5/1/2016[/TD]
[TD="align: right"]3470[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]6/1/2016[/TD]
[TD="align: right"]4732[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]7/1/2016[/TD]
[TD="align: right"]2336[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]8/1/2016[/TD]
[TD="align: right"]14799[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]9/1/2016[/TD]
[TD="align: right"]9654[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]10/1/2016[/TD]
[TD="align: right"]1856[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]11/1/2016[/TD]
[TD="align: right"]17276[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]12/1/2016[/TD]
[TD="align: right"]18381[/TD]

[/tr]

[/TABLE]
and so on for all the products.

Would you guys be help me at all?
FYI, i have attached the original File here if you could spare some time for the whole file.

VbaNoExcel · Jun 30th 2016

Re: How to sum rows in same column and make it dynamic

Hi Cytop

Many thanks. I finally understood your codes. took me long time. Many thanks for your kind assistance.

VbaNoExcel · Jun 4th 2016

Re: How to sum rows in same column and make it dynamic

hi cytop

many thanks. let me understand it again. i am trying to understand line by line. BIG Thank You again!

Posts by VbaNoExcel

Possible to Automate?

Possible to Automate?

Need Macros to replace the Formula. Please Help if you can.

Need Macros to replace the Formula. Please Help if you can.

Need Macros to replace the Formula. Please Help if you can.

Need Macros to replace the Formula. Please Help if you can.

Consolidate All tabs in one with Dynamic Rows/ colums

Consolidate All tabs in one with Dynamic Rows/ colums

Consolidate All tabs in one with Dynamic Rows/ colums

Consolidate All tabs in one with Dynamic Rows/ colums

Consolidate All tabs in one with Dynamic Rows/ colums

Consolidate All tabs in one with Dynamic Rows/ colums

How to transpose with multiple conditions

How to transpose with multiple conditions

How to transpose with multiple conditions

How to transpose with multiple conditions

How to transpose with multiple conditions

How to transpose with multiple conditions

How to sum rows in same column and make it dynamic

How to sum rows in same column and make it dynamic