+ Reply to Thread
Results 1 to 31 of 31

VBA to name new TAB and populate with Specific wanted information from Rows

  1. #1
    Forum Contributor
    Join Date
    02-01-2008
    Location
    Perth, Australia
    MS-Off Ver
    Excel 365
    Posts
    135

    Question VBA to name new TAB and populate with Specific wanted information from Rows

    Hi All,

    I have a speadsheet which requires a combination of VBA.
    The needs of my Workbook are:

    1. When text is entered into a specific cell in the Master Sheet a new Tab is created named after this text and a second string of text i.e the date.

    2.At the same time as this TAB is created I would like only the rows with checkboxes ticked in the Master Sheet to be copied to the new TAB(Not the Checkboxes themselves) [possibly using TRUE/FALSE in "B" row].

    3.I would like the formulas, formats of these checked rows to be copied too.

    4. Additionally the first 12 rows(all headers) and the last row(subtotal row)should be copied too.

    The idea of the Workbook is to have an overall options sheet that is customised on a per customer basis with a record saved on a new tab in the same workbook.

    Any help available would be fantastic.

    Thanks

  2. #2
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243
    hi Zimbo,

    Have a look at the last reply I have posted to the below thread, can you adapt the code in it to meet your needs?

    http://excelforum.com/showthread.php?t=629940

    or does the below work...?

    Put this in the worksheet module:
    Please Login or Register  to view this content.
    & this into a standard module:
    Please Login or Register  to view this content.
    hth
    Rob
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...

  3. #3
    Forum Contributor
    Join Date
    02-01-2008
    Location
    Perth, Australia
    MS-Off Ver
    Excel 365
    Posts
    135

    Struggling

    Hi Rob,

    Thanks for your reply.

    I have had a go at inserting the code into my spreadsheet and I am getting a Run-time error '1004' (method 'range of object'_worksheet' failed)
    When I go to debug it highlights the below line.
    With .Range("_filterdatabase")

    I am firstly not sure if I understood the where to place the code description you gave me and I am not sure on the range descriptions.
    The sheet has information from cell A1:AJ167. Column A has all the checkboxes which I would like not copied to the new sheet. Once copied to the new sheet the main sheet should remain intact.

    Your help is much appreciated.

  4. #4
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243
    hi Zimbo,

    I think that since you have got the 2 macros to run all the way through to this problem line that you have copied them into the right places.

    Sorry about the error, I didn't eplain very clearly that the Master sheet needs to have an autofilter applied before the macro is run. "_filterdatabase"
    is a hidden name created by Excel when a sheet has an autofilter applied to it. Hopefully this code works for you...

    Please Login or Register  to view this content.
    If you want to see some other autofilter tips check out the site that I've commented w/in the code.

    I'm not sure if the checkboxes will be copied or not - have to wait & see...

    hth
    Rob

  5. #5
    Forum Contributor
    Join Date
    02-01-2008
    Location
    Perth, Australia
    MS-Off Ver
    Excel 365
    Posts
    135

    Red face Struggling

    Hi Rob,

    I have re-enetred the code and now have the same run-time error but it says Autofilter method of rangeclass failed and on debug has listed the following: .AutoFilter Field:=2, Criteria1:="true"
    Thanks Again
    Hows NZ tday?

  6. #6
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243
    duh me!

    I missed a "j" out of the range, change the range in the code so it reads:

    Please Login or Register  to view this content.

    NZ's good ;-) it's been a hot day but I've got some roof painting to do so I may not reply again until later - hopefully all the gremlins are fixed...

    hth
    Rob

  7. #7
    Forum Contributor
    Join Date
    02-01-2008
    Location
    Perth, Australia
    MS-Off Ver
    Excel 365
    Posts
    135

    Lightbulb Wow that did Stuff

    Hi Rob,

    Almost there!
    There are a few weird things.

    1.The new Tab works perfect.
    When the tab appears all the information is still copy selected and there is a circular reference box. This box refers to the subtotal line which is being placed in Row 10. The Header row is Row 8. Can this subtotal Row be set to appear as last Row and can the column formats be kept the same?

    2. The Original Tab is also changed to the autofilter settings with the autofilter tabs being left in all columns and can't be reset.

    Again thanks for your help. p.s good weather for painting roofs.

    Cheers

  8. #8
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243
    back again!
    The roof's not ready for another coat - it'll have to wait 'til tomorrow.


    Changing which cells are selected is nice & easy. I've set up the new code to make the selection by on the new sheet when the macro finishes (see the section just below the comment that starts with "'%%%". If you'd rather the Master sheet is still selected just change the order of the 2 lines of code.

    1)
    When the tab appears all the information is still copy selected and there is a circular reference box. This box refers to the subtotal line which is being placed in Row 10. The Header row is Row 8. Can this subtotal Row be set to appear as last Row and can the column formats be kept the same?
    Zimbo, nothing weird about some of this, you've changed the rules! ;-)
    Your first post had the header rows as rows 1-12, I've made it more flexible now by defining the "HdrRow = 8" at the top of the macro. If the header row changes from 8 just change the number in the macro.
    - hopefully what I've done will put the subtotal row in the right place now (do all of the copied rows have a value in column A?) & get rid of the circular reference, if not let me know...

    2)
    I've given you two options for the autofilter with the current one being the removal of the filter being set to the Client Name, but if you want you can remove the filters altogether by using the commented line of code that follows it in the below revised version.


    Each each change I've made is commented with "'###" or "'%%%".

    Please Login or Register  to view this content.
    Also, to save the flickering of the screen I suggest adding the below to the start & end of the code in the Master Worksheet SheetCode Module:

    Please Login or Register  to view this content.
    hth
    Rob

  9. #9
    Forum Contributor
    Join Date
    02-01-2008
    Location
    Perth, Australia
    MS-Off Ver
    Excel 365
    Posts
    135

    Lightbulb

    Hi Rob,

    This is looking better and better.
    Appologies for the rule change, was suffering from brain freeze.

    I am having a couple of issues still.

    1. the subtotal row is now placing being placed in Row 3 of new sheet and circular referance is still appearing. To answer your question - there are no values in Column A shown other than a zero in ROW 3 (Subtotal row).

    2. If I Type fresh text into the customernamecell the screen temporarily shows the autofiltertabs and loops with the creation of no new tab.

    3. If a checkbox is ticked for new option selection microsoft warning pops up and excel shuts down .

    Please Help.

    I do have one other request which is that a VB "would you like to save this sheet pops up pre new tab creation.



    Thanks again

    Cheers

  10. #10
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243
    Uh oh, "excel shutting down" sounds nasty!

    I think I'm having brain freexe too, we've gone pst my powers of visualisation now - can you please attach or send me (broro183 at hotmail) a zipped example of your worksheet with some dummy data?
    (I haven't used checkboxes before but maybe I can see what is going on)

    Rob

  11. #11
    Forum Contributor
    Join Date
    02-01-2008
    Location
    Perth, Australia
    MS-Off Ver
    Excel 365
    Posts
    135

    Bounced Back

    Hi Rob,

    Hotmail just bounced me back. Any other add I can use.

    Cheers

  12. #12
    Forum Contributor
    Join Date
    02-01-2008
    Location
    Perth, Australia
    MS-Off Ver
    Excel 365
    Posts
    135

    Red face Attached

    Hi Rob,

    Attached zip. is here.

    Also I tried a few different types of code prior to posting these are below for any ideas.

    Cheers

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by VBA Noob; 02-04-2008 at 04:00 AM.

  13. #13
    Forum Contributor
    Join Date
    02-01-2008
    Location
    Perth, Australia
    MS-Off Ver
    Excel 365
    Posts
    135

    Exclamation Checkbox duplication

    Hi Rob,

    I was just having play with the checkboxes in the spreadsheet and it appears for each cell shown with a checkbox there may be an additional 2 boxes i the cell. How this has happened I have no idea. Any suggestions on how I add a check box into a cell and when adding a new row have this checkbox refer to the new row and not the old.
    I will go through the spreadsheet and delete all doubleups and repost.

    Cheers

  14. #14
    Forum Contributor
    Join Date
    02-01-2008
    Location
    Perth, Australia
    MS-Off Ver
    Excel 365
    Posts
    135

    Red face Checkbox duplication Corrected

    Hi Rob,

    Please find attached the corrected spreadsheet.
    When I created the sheet I set up a blank checkbow in row 9 and dragged the cell to where I wanted it, when I deleted the checkboxes there were between 0 and 4 copies in each Row.
    I am totally confused as to how this happened.

    Anyway, if you are able to help me complete this it would be great.

    Cheers
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    02-01-2008
    Location
    Perth, Australia
    MS-Off Ver
    Excel 365
    Posts
    135

    Wink Excel Shut down fixed

    Hi Rob,

    Since I have changed the Checkbox error I had in my spreadsheet, your code is working and Excel is no longer having a heartattack.
    The only thing now is that the checked items are not being carried forward to the new TABS.

    Cheers

  16. #16
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243
    hi Zimbo,

    Thankyou, we're learning from each other now!
    1) I didn't know that checkboxes could link to & change cells like that and 2) I didn't know that vbyesno won't let a user cancel/[esc] out of it.
    I'm pleased you found the multiple checkboxes b/c I don't think I would have.


    Anyway, I think it's done...?

    The reason it wasn't copying any data was just down to one line of code...
    Please Login or Register  to view this content.
    this line of code is meant to find the last row of data by starting in the last row of column a & going up until it hits something in a cell but b/c the checkboxes aren't "in" the cells it went all the way up to the header row. To fix it we could change the code to check column B:
    Please Login or Register  to view this content.
    but I decided to try a Named Range instead so I've called the Last row "TheTotalRow" & this will change if you insert more rows above it. Also, I've called cell B1 the "ChangeCellToStartMacro" & have adjusted the worksheet macro accordingly.

    I've kept using the autofilter approach on column B b/c I think it will be quicker to copy all the info in one go rather than looping through the checkboxes.

    hth
    Rob
    Attached Files Attached Files

  17. #17
    Forum Contributor
    Join Date
    02-01-2008
    Location
    Perth, Australia
    MS-Off Ver
    Excel 365
    Posts
    135

    Arrow Awesome stuff

    Hi Rob,

    Almost 100%.
    I am still having the subtotal row pop up in Row 10 on the new sheet.
    It may be better if I add a true value in column B so as to have the subtotal row carry forward to the new TAB.
    I have tried removing the references to (Thetotalrow) and the subtotal formula but I keep ending up with run time errors.

    Please help

    Cheers

  18. #18
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243
    sorry Zimbo, I feel like a clown (I missed another correction) & this must be so frustrating for you?

    Try changing
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    btw, if you want to learn what the code is doing you can watch the code step through the macro line by line, by following the below steps:
    click somewhere on a line in the worksheet change macro eg "application.screenupdating = false",
    press [F9] (this inserts a breakpoint where the macro stops),
    adjust the size of the VBE window so that you can see 1/2 or most of the Excel window at hte same time,
    click back into excel & start the macro.

    When the macro gets to the above line of code (almost instantly), the VBE window will open & you can use the [F8] key to step through the code line by line. This approach helps you to understand what each line of code does & you can also see where errors happen.

    If there are any more problems let me know & I'll check again or get rid of the subtotal coding...

    hth
    Rob

  19. #19
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Zimbo,

    Please read forum rules below and start wrapping your code as per rule 5

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  20. #20
    Forum Contributor
    Join Date
    02-01-2008
    Location
    Perth, Australia
    MS-Off Ver
    Excel 365
    Posts
    135

    Thumbs up Applogies

    Sorry, Won't happen again.

    Cheers

  21. #21
    Forum Contributor
    Join Date
    02-01-2008
    Location
    Perth, Australia
    MS-Off Ver
    Excel 365
    Posts
    135

    Thumbs up Looks Awesome

    Hi Rob,

    Thanks for the great advice, I have been wanting to know how to do that.

    The new code works great thanks. I have one slight problem, I only need to show 4-5 subtotals and need them to be shown in $.
    Is there any way this can be presetand changed in the string? If not the "TRUE" value may be easier.

    On another note do you have any code I could attach to a Checkbow to show and unshow the rows that do not have any text in the descriptions Column (C).

    Cheers

  22. #22
    Forum Contributor
    Join Date
    02-01-2008
    Location
    Perth, Australia
    MS-Off Ver
    Excel 365
    Posts
    135

    Thumbs up Hide / Unhide Rows

    Hi Rob,

    Not to worry about the hide unhide check box, I peiced together this code from a few different locations. It is awesome.

    I will try to wrap it this time.

    Cheers

    Please Login or Register  to view this content.

  23. #23
    Forum Contributor
    Join Date
    02-01-2008
    Location
    Perth, Australia
    MS-Off Ver
    Excel 365
    Posts
    135

    Talking Slight Change

    Hi Rob,

    Thanks so much for your help to date.
    The sheet is doing most of what I was wanting.

    I have opted away from using checkboxes as I was ubnable to link them to individual cells, so when compressing the unused rows the checkboxes were showing up as duplicates in the cell. Although it worked it looked untidy.
    I have substituted the checkboxes with this cell change code and it seems to work quite well.
    Please Login or Register  to view this content.
    The problem is since I have done this I can't seem to get the Column H information to show up in the new TAB, also if I try and add or subtract columns in the CAR sheet it gets worse.

    If possible please help.

    Cheers
    Attached Files Attached Files

  24. #24
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243
    hi Zimbo,

    Sorry, I've been busy but it looks like you have it under control - you'll be writing bigger & badder macros in no time! :-)

    I changed the code yesterday for hiding blank rows but forgot to post the file, so here it is using an autofilter approach & you can adjust it/switch it for your version if you want.

    btw, here's a shorter another way of writing your code (2nd last post) that emphasises the "toggling":
    Please Login or Register  to view this content.
    NB: sumtotal will still include amounts that are listed on "manually" hidden rows but will not include the amounts if they are hidden by filtering.


    Have a glance & see what you think of my version (with fixed/limited sub totals & the toggle) - unfortunately, it still includes the check boxes.

    The problem is since I have done this I can't seem to get the Column H information to show up in the new TAB, also if I try and add or subtract columns in the CAR sheet it gets worse.
    Does the column H info show in the Car Sheet?
    Although..., I think the problem will be that column L relies on the value in column B (from the checkboxes) to give you a value which is carried into column L. so w/o the checkboxes nothing else changes.

    I'm not sure what you mean by the add or subtract columns but it may relate to
    Please Login or Register  to view this content.
    , if the number of columns is different you need to change the "AJ" accordingly.

    If the above doesn't help, can you please attached your zipped version w/o checkboxes & I'll see if I can figure it out?


    hth
    Rob

  25. #25
    Forum Contributor
    Join Date
    02-01-2008
    Location
    Perth, Australia
    MS-Off Ver
    Excel 365
    Posts
    135

    Talking Not Quite Under Control

    Hi Rob,

    Thanks for getting back to me. Hope you had a good Waitangi Day.

    I have attached the current version of my spreadsheet and it is looking a little messy in the Module code, although it does most of what I need.

    Cheers for the above toggle code it is much cleaner than what I had.

    You will see in the code I have moved the subtotal code into comments, I tried this to get the main sheet subtotal to carry through. This almost works but once it is carried forward the cell refernces disappear and the formula fails. I think your approach is better but is there any way I can only select certain columns to total with a $ sign shown?

    I seem to have resolved the loosing columns issue by adding the hide and unhide code(everywhere!)

    Again Thanks for your help.

    Cheers

  26. #26
    Forum Contributor
    Join Date
    02-01-2008
    Location
    Perth, Australia
    MS-Off Ver
    Excel 365
    Posts
    135

    Question File Missing

    Sorry Rob,

    File is attached here.

    Cheers
    Attached Files Attached Files

  27. #27
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243
    hi Zimbo, sorry I missed your attachment when I posted but have seen it & figured it out.
    Column F of the Car sheet ws hidden & this meant that the range didn't copy over the way you'd want it to, I have unhidden it & all seems well.



    hth
    Rob

  28. #28
    Forum Contributor
    Join Date
    02-01-2008
    Location
    Perth, Australia
    MS-Off Ver
    Excel 365
    Posts
    135

    Question Bump

    Hi All,

    I am still having a couple of problems with subtotaling information in the new tab that has been created by means of the great code that has been supplied to me by broro183.

    The problem I am having is that in the subtotal row that is being created I only need Columns F,J,R & AD to show a subtotal and to also be formatted for a currency symbol. Currently the code generates a subtotal in every column and has no format.

    Please find full code below and spreadsheet attached.

    Again any help available would be fantastic

    Please Login or Register  to view this content.
    Attached Files Attached Files

  29. #29
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243
    hi Zimbo,

    First I missed your attachment & now it seems you've missed my last attachment?

    It includes a fix for subtotalling in specific columns, have a look at it & it should work for you if you adjust the column letters in the bold text line of code below (note this is w/in a "With" statement and the variables are declared at the start of the module in my last zip attachment) & change the number format to the one you want.

    Please Login or Register  to view this content.
    hth
    Rob

  30. #30
    Forum Contributor
    Join Date
    02-01-2008
    Location
    Perth, Australia
    MS-Off Ver
    Excel 365
    Posts
    135

    Talking Whoa Nelly

    Hi Rob,

    You Sir are a legend.

    Thank you so much for your help with my project.

    I did not see your last attachment and feel very silly.
    All the functionality I was looking for is there and works perfectly.
    p.s the last zip. I posted has the alternate checkbox method and it works really well.

    Once again

    Cheers

  31. #31
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243
    hi Zimbo,

    Thanks for the feedback, I'm pleased I could hep :-)

    Nah, no need to feel silly I did the same thing & definitely no legend here, I'm just learning & as I say the best way to learn is to experience... ;-)


    I do like your new technique with the formatted a's & I hope I haven't missed anything in the last version that I posted (I know the "changecelltostartmacro" was still in B1 but since it's working for you you must have sorted that out).

    Rob

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1