Posts by adfo

  • VBA to Create new workbook, worksheets, folder

    Thank you very much royUK

    I think we are getting there !!. It is almost working perfectly except for these two little glitches as follows:

    1- For some reason, it is still coping with the formulas, which would potentially remain linked to the original workbook, the matter that I need to avoid by copying just the values and formatting

    2- I need to limit the copying to those 3 worksheets. In this context, it is working as such with the individual stations' option, but it is still copying the other sheets within the "one Workbook option"


    Please have a look at the attached final revision as a start, in which I added few formulas (highlighted in RED) and a couple of worksheets for testing... Please check the OUTPUT files for each option

    I know how much effort you did already, which I appreciate... Thanks again RoyUK!!!

  • VBA to Create new workbook, worksheets, folder

    Hi RoyUK...
    Please try the workbook that I attached in my response above, then look at the first_Sheet of any of the output files...
    Formulas still show in the formula bar as I click on any cell that has numbers. Please let me know if I am missing anything... Thanks again

  • VBA to Create new workbook, worksheets, folder

    Thank you very much RoyUK


    While it does the copy very well, however, the output files in the C: drive still have the formulas (!). I reattached the solution filled with some numbers in First_Sheet as a test. The same cells in the output files still contain the formulas.


    The reason I need to remove the formulas is that the actual worksheet that I have contains many formulas linked to other worksheets not included in this example. I needed to save a copy of the final results for each station as a value only; not worrying about links to external sheets which would not be the same for each station...

    Thanks a lot!

  • VBA to Create new workbook, worksheets, folder

    Thanks RoyUK,

    Yes, you are right!... What you mentioned is just one option, the other option as stated in my post is to create an independent workbook for each station...

    I need to have both features and will choose what I will use depends on the scenario that I have. If one workbook with (let's say) 30 or 40 worksheets, realistically this would not be favorable. On the other side, if I have just two stations, it could be feasible...

    The other benefit that I am looking for is that I am learning the programming techniques in doing both options...

    Thanks for jumping in, and I hope I can get this resolved... All the best :)

  • VBA to Create new workbook, worksheets, folder

    I have a workbook (attached below), includes three (3) worksheets, which will be used as a template, to be copied into a new workbook and/or worksheets. The number of new workbooks and/or worksheets will be based on a table for "stations" as attached in the "Start" Worksheet.


    I intend to add two (2) buttons, which I need to automate as follows:


    DESIRED FUNCTIONALITY


    Button 1 click:


    1- Create New Folder with the ORDER NAME cell


    2- Under that folder, I need to create a new workbook for each station (i.e. workbook's name is "Station 1" for Station 1 and so on),

    then copy all the 3 worksheets templates below (i.e. First_Sheet, Second_Sheet, and Third Sheet). Copy the values and the formatting NOT the formulas


    3- Repeat step 2 for the rest of the stations until the last station.


    4- Desired outcome: A new workbook for EACH station, each contains a copy of the 3 worksheets.

    In this particular example, I expect 7 workbooks as an output for the seven stations, but there could be any number of stations up to 30


    Button 2 click:


    Exactly Same as Button 1, EXCEPT:


    Create just ONE WORKBOOK includes ALL the worksheets for ALL the stations in the same workbook


    Worksheets naming convention will likely be something like Station 1_First_Sheet, etc.


    I appreciate any help; preferably a working, efficient, and fast running code, which will definitely make my weekend !!:)
    Create New WorkBook_Worksheets.xlsx

  • How to make a shape/button floats with scrolling down in VBA?

    After 10 minutes of internet research, I could not get the answer, then I decided to post here!


    I use the following code in the Worksheet_SelectionChange event, which only triggers when (as you expect) the selection changes!


    What should I do in order to make it work as I scroll down using the scroll sidebar?


    Thanks

    Code
                With Me.Shapes("Shape1")
            .Top = ActiveWindow.VisibleRange.Top + 250
            .Left = ActiveWindow.VisibleRange.Left + 630

            End With

  • VBA to generate and draw vertical and horizontal lines following some rules

    I have ground & ceiling data given in X, Y coordinates, which I plotted
    I want to split the area in between vertical and horizontal lines (constituting building blocks) and plot all; following some rules:


    # Vertical Lines
    Shall be every 4 feet apart. The line length is an even number with 2 feet increment (i.e. 2,4,6, etc).
    The vertical lines should NOT intersect with the ceiling. At least 2 feet needs to be left before it hits the ceiling.



    # Horizontal lines
    shall be every 4 feet up. The lines are an even number with 4 feet increment (i.e. 4, 8,12, …to whatever it takes)
    The lines should not intersect with the ceiling side slopes, at least 5 feet needs to be left sideways


    The lines should end up creating blocks (rectangles) in an organized format.

    I manually crated some lines (with formulas) for illustration purpose (attached) but I appreciate if I can accomplish this in VBA.

    It would be fantastic (but optional) to provide the number of blocks and the surface area to be covered, but again this is optional!

    Thanks for your help!


    04092021.xlsx

  • Macro for a wall building planner tool

    I need a macro to determine how many stacked concrete blocks I need to build a wall for a given height? The concrete block sizes are only available in either 2 feet, 4 feet, or 6 feet high.

    What I need to achieve is:
    If the input wall height, let's say, is 12 feet high, I expect the "output" for possible concrete units sizes to build the wall, and to make the 12 feet high (from top to bottom, where row 1 is the top) are the following:

    option 1:
    Row 1 = 2 feet
    Row 2 = 4 feet
    Row 3 = 6 feet -- (so the total is 12 feet)

    option 2:
    Row 1 = 4 feet
    Row 2 = 4 feet
    Row 3 = 4 feet

    I am attaching the spreadsheet with an explanation of more rules to follow. Wall building planner.xlsm

    Wall building planner.xlsm

    I posted this in another forum yesterday, but did not receive answers as I write:

    https://www.excelforum.com/exc…d-a-wall.html#post5493975
    I appreciate any help.

  • Need VBA code to sum data from 2 tables by matching a field

    Re: Need VBA code to sum data from 2 tables by matching a field


    Quote from royUK;724127

    Here's an example using the data formatted as actual Excel Table with an added column to add the department using VLOOKUP. The data report is then easily created using a PivotTable. No VBA is needed at all.


    PivotTable Report


    Thanks royUK! seems the pivot table is a good idea. I wish if I can learn more about it. Thanks a lot!

  • Need VBA code to sum data from 2 tables by matching a field

    I need a VBA code to create table that sums total sale and quantity by department getting information from 2 worksheets:
    Worksheet "Data": Lists the "Employee", "quantity", and "cost"
    Worksheet "Department": Lists/ defines "Employee" & "Department"
    I need to fill a table in a third worksheet "Report", summing the "Total quantity", and the "Total Cost" by "Department" using the data in the above worksheets. I am attaching example file. Thanks.


    forum.ozgrid.com/index.php?attachment/61977/

  • code for sorting pivot table through VBA is driving me crazy !!

    Re: code for sorting pivot table through VBA is driving me crazy !!


    Quote from Derk;722524

    An interesting problem. It took awhile to figure out. The main routine PivotTableStaff_Totals leaves the pivot table in manual update. Normally this would be reset when the code stops running. Since your Event macro adds another bit of code to run (Macro1) the manual update is still in effect. It needs to be turned off so the sort can function on an updated table.
    The following works.


    Thank you very much! This has resolved the problem!! .. I appreciate it! You made my Friday and the entire weekend!! :)

  • code for sorting pivot table through VBA is driving me crazy !!

    I am maintaining an existing excel file that was developed using a pivot table and a chart. I was asked to have the table and the chart be sorted automatically when the worksheet opened (or activated). I could get this to work by clicking a button and call macros for ascending or descending sort. I wanted to eventually get rid of the button and have the chart sort automatically when I move out then back to the page again. I add the code that sorts the table (or call the macro) in the worksheet_activate event itself right after I create the chart, it blanks the chart out, and all values disappear!!. The code works when I click the button, but the same code does not work when I call the macro within the code. I am using Excel 2007, not sure if this is a glitch, or am I doing something wrong? I am attaching a sample file. I appreciate any help.. Thanks
    forum.ozgrid.com/index.php?attachment/61736/

  • Need VBA code to split quantities based on availability in another table

    Re: Need VBA code to split quantities based on availability in another table


    Thanks AAE, I already had links to cross-post in my original post above .. Thought this is commonly the requirements .. It was solved through one of them .. thought to mark my status here as such .. the solution exists already and can be found by clicking on the link above ..