Posts by Judie12345

    KjBox

    I apologize for my ignorance, as I have not posted for probably 8 years, nor have I done any coding lately, so I'm actually a struggling sole here, and unfamiliar to user changes to the website. I love this website, and everyone has always been so helpful in the past. I will also NOT be ignorant by commenting on you "your Rolling Eyes", that made it clear to me of my ignorance. This code stops and is not working. I will post my whole code if it helps for anyone willing to help me. I will apologize again, as I cannot see this: click </> on the menu bar. Perhaps I did not make it clear. This may be more helpful:

    Copy a cell value from one worksheet to the first blank cell in column C on another worksheet & paste the value all the way down to the last used row IN THE WORKSHEET. This is a correction to above. Thank you.

    Here is my coding so far:


    Sub Summarize()

    Dim btn As Long 'bottom row

    Dim Rght As Long 'far right column

    Dim LastRow As Long


    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    'CLEAR ALL DATA CLEAR ALL DATA CLEAR ALL DATA

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


    ' If AutoFiler is on, Turn Off '????????????

    Sheets("SUMMARY SHEET").Select

    If ActiveSheet.AutoFilterMode Then

    ActiveSheet.AutoFilterMode = False

    End If


    ' Clear the Summary Sheet items, except headers

    Sheets("SUMMARY SHEET").Select

    Range("A2").Select

    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

    Selection.Delete Shift:=xlUp



    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    'COPY THE ANNUAL REPORTS DATA COPY THE ANNUAL REPORTS DATA

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    ''''''''''''''''''''''''''''

    'COPY THE STATES DATA

    ''''''''''''''''''''''''''''


    ' Select Annual Reports & Copy States

    Sheets("Annual Report").Select


    'Find the last row in column A

    Range("A6000").End(xlUp).Select

    btn = ActiveCell.Row


    'Activate cell A

    Range("A1").Activate

    Rght = ActiveCell.Column


    'Move down to row 3 in column A

    Range(Cells(3, 1), Cells(btn, Rght)).Select


    'Copy the STATES data

    'Now copy the selected range

    Selection.Copy

    FileNm = ActiveWorkbook.Name

    ThisWorkbook.Activate

    Sheets("SUMMARY SHEET").Activate

    ActiveSheet.Range("A6000").Select

    ActiveSheet.Range("A6000").End(xlUp).Select

    ActiveCell.Offset(1, 0).Select

    ActiveSheet.Paste


    ''''''''''''''''''''

    'COPY THE DUE DATES

    ''''''''''''''''''''


    ' Select Annual Reports & Copy Due Daates

    Sheets("Annual Report").Select


    'Find the last row in column A

    Range("A6000").End(xlUp).Select

    btn = ActiveCell.Row


    'Activate cell A

    Range("J1").Activate

    Rght = ActiveCell.Column


    'Move down to row 3 in column J

    Range(Cells(3, 10), Cells(btn, Rght)).Select


    'Copy the DUE DATES data

    'Now copy the selected range

    Selection.Copy

    FileNm = ActiveWorkbook.Name

    ThisWorkbook.Activate

    Sheets("SUMMARY SHEET").Activate

    ActiveSheet.Range("B6000").Select

    ActiveSheet.Range("B6000").End(xlUp).Select

    ActiveCell.Offset(1, 0).Select

    ' ActiveSheet.PasteSpecial

    ActiveSheet.Range("B6000").Select

    ActiveSheet.Range("B6000").End(xlUp).Select

    ActiveCell.Offset(1, 0).Select

    ActiveSheet.PasteSpecial

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

    :=False, Transpose:=False

    ''''''''''''''''''''

    'COPY THE FILE TYPES

    ''''''''''''''''''''

    Sheets("DO NOT DELETE").Select

    Range("A7").Select

    Selection.Copy

    Sheets("SUMMARY SHEET").Activate

    Range("C2").PasteSpecial

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

    Range("C2").Copy Range("C3:C" & LastRow)



    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    'COPY THE FRANCHISE REPORTS DATA COPY THE FRANCHISE REPORTS DATA

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    ''''''''''''''''''''''''''''

    'COPY THE STATES DATA

    ''''''''''''''''''''''''''''


    ' Select Annual Reports & Copy States

    Sheets("Franchise").Select


    'Find the last row in column A

    Range("A6000").End(xlUp).Select

    btn = ActiveCell.Row


    'Activate cell A

    Range("A1").Activate

    Rght = ActiveCell.Column


    'Move down to row 3 in column A

    Range(Cells(3, 1), Cells(btn, Rght)).Select


    'Copy the STATES data

    'Now copy the selected range

    Selection.Copy

    FileNm = ActiveWorkbook.Name

    ThisWorkbook.Activate

    Sheets("SUMMARY SHEET").Activate

    ActiveSheet.Range("A6000").Select

    ActiveSheet.Range("A6000").End(xlUp).Select

    ActiveCell.Offset(1, 0).Select

    ActiveSheet.Paste


    ''''''''''''''''''''

    'COPY THE DUE DATES

    ''''''''''''''''''''


    ' Select Annual Reports & Copy Due Daates

    Sheets("Franchise").Select


    'Find the last row in column A

    Range("A6000").End(xlUp).Select

    btn = ActiveCell.Row


    'Activate cell J

    Range("J1").Activate

    Rght = ActiveCell.Column


    'Move down to row 3 in column J

    Range(Cells(3, 10), Cells(btn, Rght)).Select


    'Copy the DUE DATES data

    'Now copy the selected range

    Selection.Copy

    FileNm = ActiveWorkbook.Name

    ThisWorkbook.Activate

    Sheets("SUMMARY SHEET").Activate

    ActiveSheet.Range("B6000").Select

    ActiveSheet.Range("B6000").End(xlUp).Select

    ActiveCell.Offset(1, 0).Select

    ' ActiveSheet.PasteSpecial

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

    :=False, Transpose:=False

    ''''''''''''''''''''

    'COPY THE FILE TYPES

    ''''''''''''''''''''

    Sheets("DO NOT DELETE").Select

    Range("A8").Select

    Selection.Copy

    Sheets("SUMMARY SHEET").Activate

    ActiveSheet.Range("C6000").Select

    ActiveSheet.Range("C6000").End(xlUp).Select

    ActiveCell.Offset(1, 0).Select

    ActiveSheet.PasteSpecial

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

    :=False, Transpose:=False

    [xpost][/xpost]


    Hello

    It has been a number of years since I have used VBA, and just can't figure out this SIMPLE solution.


    What I have so far is:


    This works so far in Column C. But what I want it to do, is copy that value ALL THE WAY DOWN to the LAST USED ROW VALUE IN Column A.

    Can someone help.


    Thank you.

    Re: SubTotal/PageBreak in Worksheet


    dr - any chance I could just email you directly? In the wee hours of the morning I was able to add subtotals, page breaks,-- just can't get the logo to insert after page breaks. Let me know -- I don't know if its proper with this forum, but I could email you what I have so far, which is a little better than what I posted around midnight last night. Thanks. It's getting late here. I'm certain I will dream tonight -- and I know already what I will dream about -- insert logo HOW? Thanks. You've been great

    Re: SubTotal/PageBreak in Worksheet


    You know dr -- I was just thinking of this whole mess. And it is probably my fault for setting this code up the way I have -- I'm so fresh to this stuff(self-taught - basically) I don't know any better.


    And frankly, I am a little shocked, that I have not had more responses, because you guys are so smart out there--and I believe there is a way to do this. I hope you all remember that there are a few of us newbies out here with stupid challenges, and that when we receive help we truly learn from them. Sometimes, maybe the experienced coders (which MOST of you are )-- just want the harder challenges -- do you think?


    I'm not sure this thing can be solved. I have spent hours researching the internet, reading my 20+ books -- there has just got to be a way.


    I remain positive. Thanks for letting me vent dr. You are a good person and I appreciate that help you have already given me, and your time spent.

    Re: SubTotal/PageBreak in Worksheet



    Oh dr thank you so much for responding. I am so unsure what I am doing.


    Yes the logo process should happen on both worksheets.


    What I want to do is go through the data on the info page, add it to the appropriate worksheets (whether or not it is MLW or NON) add subtotals and page breaks, and add the logo when the page break happens or is set.


    I can't believe you responded. I was up til 1 am. and up at 5am trying to figure this out. I thank you so much for any advice you can give.

    Re: find blank cell/insert data


    I was thinking of something like this, but I don't even know if this is possible.



    anything? from anyone

    Any help would be appreciated:


    I need to search B5:B until blank cell is found.


    If blank cell is found go to the following row below and copy A1:G4.


    Then go to next blank cell and repeat to the end of data rows.


    Thanks

    Hello all --


    After each pagebreak, is there a way to copy range "A1:G4"? Thanks




    Code
    'subtotal and page break worksheet NON
        wbNew.Sheets("WGC").Select
        Range("A4").Select
        Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5, 6), _
        Replace:=True, PageBreaks:=True, SummaryBelowData:=True
        Rows(5).Delete
        'deletes the grand total line
        GrandRow = Range("A65536").End(xlUp).Row
        Rows(GrandRow).Delete

    Re: SubTotal/PageBreak in Worksheet


    Well, I apologize. I had to delete all sorts of sensitive information, formulas, etc. to get this w/b to upload. I tried to include the "logo" sheet with the TESTA workbook, but try as I might (deleting picture logo, formulas, I could not get that sheet under 51KB). So if you could just add a "logo" sheet to the TESTA w/b with data in cells B1:G4 that should do it.


    The macro JBNewSheets is my original macro. The NewSheets is rbrhodes'. Maybe you will be able to see my problem clearer, although by removing so much data, the final product sure looks like a big mish-mosh.


    Anyway -- again, any help is appreciated and thanks for trying.

    Re: SubTotal/PageBreak in Worksheet


    Rbrholdes (dr):
    First of all, thanks so much for giving this a look and consideration. Well, I will say, that was pretty good.


    Here's what happened. Your right -- I have no code in there to do what I want -- because I didn't know how to do it.


    1. The code that "I/me/Judie" posted, already placed the new information to the MLW/NON worksheets. All of the company data is already in the sheet(as of that code).


    2. I need further code to now loop through the data in MLW/NON and find where a new company starts, add totals, a page break, and the logo (maintaining the row heights in the Logo located in the A1:G4 cells already in the sheet).


    3. This is what your code is doing: It is adding a total to the bottom of all the company, the logo (formating not kept--I know I forgot to mention that), and then a page break.


    Dave is probably right -- I don't have this set up right, but don't know how to now fix it.


    Thanks so much for giving it a GREAT effort.
    Judie


    P.S. No more tears -- excuse me while I go bang my head against the wall. :D

    Re: SubTotal/PageBreak in Worksheet


    QUOTE:


    While Excel is not a true database Application, data within it should be laid out in a database format. By this I mean, you should reserve one Worksheet to enter all related raw data in a classic table format. That is, headings across row 1 of the table and all data laid out in continuous rows directly underneath their appropriate headings.


    END QUOTE:


    I believe I have a raw data table format in my worksheet "Info" which has headers. I do not copy the headers to the new workbook (worksheets MLW and NON) because my logo has the headers embedded in it. So, may I ask -- what would you suggest?

    Re: SubTotal/PageBreak in Worksheet


    Quote from Dave Hawley

    Or, better still, set-up your data properly so you dont constantly run into problems.


    Mr. Hawley, you just brought tears to my eyes. To you I may be a newbie that struggles with "problems". I love this forum and have received outstanding answers to my "problems". I believe my data is set up "properly". Six months from now -- when I am VBA wiser, I may look back and agree with you. But right now -- I don't know any better, and that is why I am posting questions here -- with the hope that someone can help me, instead of replying with "crisp" responses.


    If there is anyone that can make a suggestion to resolve my problem. I would appreciate. Again, this forum is outstanding.

    Re: One Last Situation - SubTotal/PageBreak in newsheet


    Ok, I am fairly certain I need help. I tried to record a mcro by selecting the Excel "subtotals" menu so I could select "sum" and pagebreaks", but excel couldn't recognize where my column headers were. (They are in row 3, but row 4 has blank data).


    Here is what the posted code does:


    1. Copies a logo into sheets "MLW" and "NON"
    A,B,C,D,E,F,G
    Blank,Logo,Blank,Co.Name,Blank,Blank,Formula
    Blank,Gray,Gray,Gray,Gray,Gray,Gray
    Company,Date,Name,Time,Item1,Item2,Comments
    Blank,Gray,Gray,Gray,Gray,Gray,Gray


    Blank = Blank cell
    Gray = Blank cell colored gray for decorative purposes, as this is an invoice
    NOTE: The whole header/Logo section is in A1:G4


    2. Then the code loops through the data in wb "Info" and if it finds "MLW" in row AH,, it adds it to the wb"MLW", otherwise anything else is added to wb"NON". (NOTE: THE DATA HAS PREVIOUSLY BEEN SORTED)


    3. At this point, I now need to insert totals for Item1(E3) and Items2(F3) and add a pagebreak after for each company change. Then I need to copy A1:G4 (the invoice logo header) and continue down the rows placing totals/pagebreak/A1:G4 header/logo.


    I hope this makes sense. I am so frustrated, and anything you can help with is so appreciated. Thank you

    Re: One Last Situation - SubTotal/PageBreak in newsheet


    Hi Nori. I think I was not clear in my explanation yesterday. The code in this post is what I ended up with by using your code to copy and insert two new sheets and what squeegedog gave me. This works just great, but now with the copied data in sheets MLW and NON I need to use code to find where the pagebreaks go and add subtotals. I posted a Thank you to you in the "copy certain criteria to new workbook" post. Thanks again.

    The last thing to do to perfect this code is to insert subtotals for columns E and F and pagebreaks starting with cell A5. I need to do this in the new worksheets created as "MLW" and "NON. Any help would be appreciated. I have searched the posts, but I think I have a unique situation herel, as I have created new sheets from my original worksheet. I'm also a newbie -- so I apologize for my lack of knowledge.


    Re: Copy certain criteria to new workbook


    I ran it in my original workbook. I'll try it in test. We have to leave for dinner. So I will try it in test when we return home. Norie I so appreciate your help, and I will get back in an hour or so. Thank you again. I will talk with you soon.