Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: vba to condense similar cells into new cell, with total overall quantity.

  1. #11
    New Member
    Join Date
    Feb 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba to condense similar cells into new cell, with total overall quantity.

    hi Squidd, I dont mind if sheet 3 is visible. Our system generates excel files, which we format and turn into price lists, cut sheets, etc. By default, they only have sheet 1. If possible, id like the macro to create a sheet 3 so we dont have to manually add it each time. This would be numerous times per day.

  2. #12
    New Member
    Join Date
    Feb 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba to condense similar cells into new cell, with total overall quantity.

    Alrighty, I got it working. I ended up taking our price list template, and adding sheet 3 to that. this way when the macros we use create the price list, it creates this cut sheet you've been helping with onto the pricelist excel. I used the updated code you sent last night, thanks for all the help Dave.

  3. #13
    Board Regular
    Join Date
    Jan 2009
    Location
    uk bexhill sussex
    Posts
    1,617
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba to condense similar cells into new cell, with total overall quantity.

    Ok, good stuff.

    you are welcome.

    im sure we could create what you desire without to much problem, but if youre happy with how its working then we can leave it.

    let me know if you want the code changed.

    one last thing, the only thing that MAY cause you a problem. The whole code is based around case sensitivity. so check that you will not have a problem, for instance, oak and OAK or Oak are all different.

    you may have to test it to see how it reacts. only thought of it last night.

    cheers

    Dave
    Some one always knows more than me,thats why I am here.

  4. #14
    New Member
    Join Date
    Feb 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba to condense similar cells into new cell, with total overall quantity.

    Hey Squidd,
    Your code has been working great. I need some help getting to the next version of this. Here's the summary since its been a while. The macro scrapes the main sheet, and copies all rows with info on col. E. This is then pasted onto sheet "profiles," and all duplicates are deleted. I have also added 5% to account for lumber cutoffs.

    Would it be possible to have this all happen on the main page? We have been turning to sheet "profiles" for the sum, but are now wanting it to all be on the same page. We do not need all the untallied lumbers on the main page, just the total length for each type of lumber. I played around with the code but since there are other parts on sheet 1, I have been having trouble. Since the macro is working great, i tried to find an addon so i could delete all items on sheet1 that are represented on sheet 2 (profiles). Then, the items on sheet 2 would need to be pasted back onto sheet 1 as a single line item for each lumber type. I have not been successful in that.


    this is what info each column has
    A: line item# (not needed for this macro)
    B: Qty each (ideally the macro will make this qty 1, with total length in col e for all similar lumber profiles)
    c: description (macro case sensitivity doesn't matter since the computer spits them out in same format every time)
    D: sku#
    e: length

    below is the code i am currently working with-- slight modifications from your helpful code.

    ' create profile sheet to paste total qty per type of profile
    Sheets.Add.Name = "Profiles"
    Sheets("Quote Sheet").Activate


    ' copy lumber profiles to profilesheet.


    Sheets("Profiles").Columns("A:D").ClearContents
    Range("C1:E" & Range("A" & Rows.Count).End(xlUp).Row).Copy
    Range("'Profiles'!A1").PasteSpecial
    With Sheets("Profiles").Columns("A:D")
    .Range("A1:C" & Range("'Profiles'!A" & Rows.Count).End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlNo
    .SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
    End With
    ' duplicate col c to col d
    Sheets("Profiles").Range("C2:c30").Copy
    Sheets("Profiles").Range("d2:d30").Paste
    For a = 2 To Range("'Profiles'!A" & Rows.Count).End(xlUp).Row
    total1 = 0
    word1 = Range("'Profiles'!A" & a)
    For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    If Range("C" & i) = word1 Then total1 = total1 + Range("B" & i) * Range("E" & i) * 1.05
    If Range("d" & i) = word1 Then total1 = total1 + Range("B" & i) * Range("E" & i)
    Next
    Range("'Profiles'!C" & a) = total1
    Range("'Profiles'!d" & a) = total1 / 1.05


    Next
    Sheets("Profiles").Activate
    For B = Range("'Profiles'!A" & Rows.Count).End(xlUp).Row To 2 Step -1
    If Range("'Profiles'!C" & B).Value = 0 Then Rows(B).EntireRow.Delete
    Next B
    ' copy profile col C, to make duplicate in col H to rename to system values
    Sheets("Profiles").Range("b:b").Copy Destination:=Sheets("Profiles").Range("f:f")

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com