Posts by j0di12

    I want to create a macro that can automatically:


    -copy all the excel sheets in a folder
    -paste these excel sheets in a new folder



    I've found various information about how to move copy and paste data from one worksheet to another, or how to combine data into one 'master' spreadsheet, but nothing about copying workbooks and pasting them in a new folder.


    Any suggestions or tips?

    Thanks, I think possibly the error came from the fact that I was checking it while running some other worksheets at the same time ? It seems to work fine now.


    I've updated my code so that if a user selects "yes" but the checklist is incomplete, a message box appears to remind the user to complete the checklist.



    I want to prevent a user from closing the workbook when a checklist is incomplete. I've included my excel sheet for reference.


    I've done some research on the internet, but what I have tried doesn't seem to work. Any suggestions would be much appreciated.


    I have a table of raw data that shows by function and type the number of people required for each month of the year until Jan 19. An example of this 'Raw Data Table' is shown below. (The Rows A to C and Columns 1 to 7 are just there for a reference)...
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 409"]

    [tr]


    [td][/td]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [/tr]


    [tr]


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

    [td]

    Function

    [/td]


    [td]

    Type

    [/td]


    [td]

    Jan-18

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]2[/TD]

    [td]

    Speciality

    [/td]


    [td]

    Baker

    [/td]


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

    [/tr]


    [tr]


    [TD="align: right"]3[/TD]

    [td]

    Speciality

    [/td]


    [td]

    Butcher

    [/td]


    [TD="align: right"]2[/TD]

    [/tr]


    [tr]


    [TD="align: right"]4[/TD]

    [td]

    Speciality

    [/td]


    [td]

    Cheesemonger

    [/td]


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

    [/tr]


    [tr]


    [TD="align: right"]5[/TD]

    [td]

    Grocery

    [/td]


    [td]

    Packer

    [/td]


    [TD="align: right"]5[/TD]

    [/tr]


    [tr]


    [TD="align: right"]6[/TD]

    [td]

    Grocery

    [/td]


    [td]

    Helper

    [/td]


    [TD="align: right"]6[/TD]

    [/tr]


    [tr]


    [TD="align: right"]7[/TD]

    [td]

    Grocery

    [/td]


    [td]

    Driver

    [/td]


    [TD="align: right"]2[/TD]

    [/tr]


    [/TABLE]

    At the moment, if I wanted to create a summary table that summarises the total # of bakers and cheesemongers, and the total # of drivers, I have to do that manually....
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 404"]

    [tr]


    [td]

    Function

    [/td]


    [td]

    Type

    [/td]


    [td]

    Jan-18

    [/td]


    [/tr]


    [tr]


    [td]

    Speciality

    [/td]


    [td]

    Baker, Cheesemonger

    [/td]


    [td]

    =C2+C3

    [/td]


    [/tr]


    [tr]


    [td]

    Grocery

    [/td]


    [td]

    Driver

    [/td]


    [td]

    =C7

    [/td]


    [/tr]


    [/TABLE]

    I thought there could be a way that I could create this summary table by using a user form. So, the user would select which function they were interested in, and then the type of people in that function they wanted to summarize. The Marco would then find their selection, the corresponding data, and add them together.


    Ideally, I would like a user to be able to create any summary table they want by using the userform instead of manually creating one!


    Eventually, it would 'print' out the table on the active spreadsheet. This should print in a specified location (eg, starting in Column A, Row 35).


    Any advice on where I could begin code wise? I'm fairly new to VBA so not really sure how I could go about doing something like this.....

    Hi,


    Thanks for getting back to me. I tried this in my current code and it came up with an error saying 'Object Not Found'. I've done some quick research into this type of error, and one of the main things it mentioned was that this usually occurs when a variable hasn't been defined properly....


    Maybe the formula isn't 100% correct?


    Thanks for your help.

    How do I add a new column in a new table with all the corresponding formulas?


    So, for example, I have three named tables on one worksheet. The purpose of this worksheet is to compare how people I need in different parts of a store according to a) data from a table and b) the store manager.


    Data Table -> This table shows the # of people needed from January 18 to January 19. This data is stored in another sheet in the same workbook called RawData.


    Manager Table -> This table shows # of people needed from January 18 to January 19.


    Demand Table -> This table shows the total # of people needed according to the data table and the manager table.


    I would like to create a button that when pressed calls a marco which
    -> Adds a new column at the end of each of the named tables
    -> Applies correct formulas to the newly created column


    Currently, I can
    -> Add a new column at the end of each of the named tables (but very clumsily)
    -> Apply a new heading (but you have to manually change to correct month/year format)
    -> Fill new column with numbers and 'easy' formulas like C4 + D4 etc.


    Any suggestions on how I can improve this?


    My code is:


    I have a table of raw data on a worksheet called "Raw Data" like the one shown below:
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 539"]

    [tr]


    [td]

    Store

    [/td]


    [td]

    Product

    [/td]


    [td]

    Merchant Price

    [/td]


    [td]

    Store Price

    [/td]


    [td]

    Date Purchased

    [/td]


    [td]

    Quantity Purchased

    [/td]


    [/tr]


    [tr]


    [td]

    Store 1

    [/td]


    [td]

    Bread

    [/td]


    [TD="align: right"]£0.30[/TD]
    [TD="align: right"]£1.00[/TD]
    [TD="align: right"]27/02/2018[/TD]
    [TD="align: right"]10[/TD]

    [/tr]


    [tr]


    [td]

    Store 2

    [/td]


    [td]

    Potatoes

    [/td]


    [TD="align: right"]£1.32[/TD]
    [TD="align: right"]£2.00[/TD]
    [TD="align: right"]06/02/2018[/TD]
    [TD="align: right"]2[/TD]

    [/tr]


    [tr]


    [td]

    Store 3

    [/td]


    [td]

    Ice cream

    [/td]


    [TD="align: right"]£0.70[/TD]
    [TD="align: right"]£1.00[/TD]
    [TD="align: right"]15/02/2018[/TD]
    [TD="align: right"]7[/TD]

    [/tr]


    [/TABLE]

    I have created a userform that allows a user to automatically filter the raw data by store and product. This filtered data is then pasted into a new worksheet called "Filtered Data". So, for example, if the user wanted to filter by Store 1and Bread, the following table would be posted into the "Filtered Data" worksheet.
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 539"]

    [tr]


    [td]

    Store

    [/td]


    [td]

    Product

    [/td]


    [td]

    Merchant Price

    [/td]


    [td]

    Store Price

    [/td]


    [td]

    Date Purchased

    [/td]


    [td]

    Quantity Purchased

    [/td]


    [/tr]


    [tr]


    [td]

    Store 1

    [/td]


    [td]

    Bread

    [/td]


    [TD="align: right"]£0.30[/TD]
    [TD="align: right"]£1.00[/TD]
    [TD="align: right"]27/02/2018[/TD]
    [TD="align: right"]10[/TD]

    [/tr]


    [/TABLE]

    I was wondering if it would be possible to add or remove columns automatically using some sort of macro.


    So for example, I don't need to know the purchase date, but would like to know how much revenue I make from my purchases. To do this, I want to add a column to the Left of 'Store Price' called 'Profit per item'. This is calculated by subtracting 'Store Price' from 'Merchant Price'. I would then remove the 'Date Purchased' column. This would be replaced by a revenue column called 'Total Profit'.


    The resulting table is shown below: [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 669"]

    [tr]


    [td]

    Store

    [/td]


    [td]

    Product

    [/td]


    [td]

    Merchant Price

    [/td]


    [td]

    Store Price

    [/td]


    [td]

    Profit per item

    [/td]


    [td]

    Quantity Purchased

    [/td]


    [td]

    Total Profit

    [/td]


    [/tr]


    [tr]


    [td]

    Store 1

    [/td]


    [td]

    Bread

    [/td]


    [TD="align: right"]£0.30[/TD]
    [TD="align: right"]£1.00[/TD]
    [TD="align: right"]£0.70[/TD]
    [TD="align: right"]10[/TD]
    [TD="align: right"]£7.00[/TD]

    [/tr]


    [/TABLE]

    Ideally, what I would eventually like to do is:


    -> User filters data using userform
    -> VBA 'reads' this filtered data, and then deletes or adds columns/rows as appropriate.
    -> It then prints the modified table into the 'Filtered Data' spreadsheet.
    -> The user must wait until the modified table is 'printed' into the 'Filtered Data' spreadsheet before being able to make any alterations to the worksheet.

    Thanks, rabsofty! My userform is now working like I expected it to.


    I was wondering how I could improve this code so that instead of transferring the data from my listbox into an empty row on Sheet 1, I could specify a specific cell where it should be pasted into.


    -> Currently: Data from listbox is pasted into the first empty row in Sheet 1
    -> Goal: Specify where the data should be pasted into in Sheet 1


    I have done this by slightly altering rabsofty's code above:


    Code
    'Replace # with appropriate number
    Range(Cells(ROW #, COLUMN #), Cells(ROW # + ListBox2.ListCount -1, COLUMN #)).Value = ListBox2.List


    Is there a neater way to do this?

    Is it possible to have a dynamic criteria range when using the advanced filter in Excel?


    For example:


    I have the following criteria, as shown in the table below. At the moment, I only have two criteria:


    -> The store must be Store 1
    -> AND the product must be Brown bread
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 175"]

    [tr]


    [td]

    Stores

    [/td]


    [td]

    Products

    [/td]


    [/tr]


    [tr]


    [td]

    Store 1

    [/td]


    [td]

    Brown bread

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]


    However, I could add in extra criteria.... so that


    -> The store must be Store 1, 2 OR 3
    -> AND product must be Brown bread


    Etc etc
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 175"]

    [tr]


    [td]

    Stores

    [/td]


    [td]

    Products

    [/td]


    [/tr]


    [tr]


    [td]

    Store 1

    [/td]


    [td]

    Brown bread

    [/td]


    [/tr]


    [tr]


    [td]

    Store 2

    [/td]


    [td]

    Brown bread

    [/td]


    [/tr]


    [tr]


    [td]

    Store 3

    [/td]


    [td]

    Brown bread

    [/td]


    [/tr]


    [tr]


    [td]

    ETC

    [/td]


    [td]

    ETC

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]

    Is there a way I make a dynamic criteria range so that way as I add/remove criteria, the advanced filter picks this up?


    At the moment, I have been manually filtering the data using the data tab in excel, but am planning to 'record' the advanced filter so that way I make a macro from it.

    Hi Rab,


    Please find attached the spreadsheet with my code. Hopefully this will help explain what I'm attempting to do.


    Basically, I have some raw data about different products which are sold in different quantities throughout the year across various stores.


    The userform allows a user to choose multiple stores and products.


    The stores and products they selected then get 'transfered' to a table.


    This table is then used as the criteria for an advanced filter sort. I will add in a macro later so that way this is done automatically.

    I am currently teaching myself VBA and how to create my own userforms.


    I have been following these handy tutorials here (link: http://www.excel-easy.com/vba/…-list-box-selections.html).


    This tutorial walks you through how to create a userform which allows a user to select some items in List Box 1 and then add/ remove them from List Box 2. I want to add some additional functionality to this userform by allowing a user to 'print' or 'transfer' their selections in List Box 2 to a table in a sheet.


    I have tried to 'transfer' the data into the first empty row on Sheet1 by using this code (ref link: http://www.excel-easy.com/vba/userform.html)



    But a) it does not work when I click 'OK' and b) I suspect it's missing something in order to transfer all the selections in List Box 2


    Any help on this topic would be much appreciated.