Posts by gijsmo

Try changing the msgbox part of the code to:

Without seeing how you plan to use this in an example workbook, one non-macro method to achieve something like this is attached.

The Data sheet holds very basic information, just the year you want to work with and the month you want to query. The "has 3 pay periods" result for the month is then derived from the Calc sheet.

The Calc sheet takes the Year value from the Data sheet, determines the 1st Wed in Jan to start and through a series of calcs derives the months (month number or month name) which have 3 pay periods.

It may be possible to incorporate something like this into your current workbook.

Identify 3 pay-periods.xlsx

You are welcome, glad it worked for you.

Try the attached sample, macro code has been added to module 1, I have added a "Click me" button on the ID_List screen to run the macro.

Sample - Mike_Per VBA Question v1.xlsm

You are welcome, thanks for the likes

Without knowing exactly which columns are affected you could add some code to try and convert any numbers stored as text eg:

This would need to be added after data is copied from the recordset.

Absolutely, the attached update has a 2nd sheet called PreTerm which holds the fixed headers. Obviously you can rename and reformat the headers as required.

The PreTerm sheet is copied as a starting point for the output file. The data from the individual files is then appended below these headers.

PreTermExtractor v2xlsm.xlsm

Please note also that you may need to do some data correction. I noticed there were records in the Alldata sheet that will not match to any of the sub-categories specifically, "Handi Bus Driver" (this should probably be "Handibus Driver" by the look of it) and also "Recreation"

Also, I happened to notice that eg "Parks Manager" and "Recreation Programming Manager" appear under both Community Services and Senior Management which means any entries for these will be duplicated across the 2 corresponding sheets. I can see some others, this may be intentional.

My sample code is attached which should do what you are trying to do if I have interpreted everything in these posts so far

There are 2 main macros called TransferData and ClearSheets. The Transferdata macro does the following:

1. Read each of the 7 main categories in the Needed Info sheet, one column at a time
2. Find the matching rows in the Alldata sheet for all the values in the category (eg, Community Services has 26 sub-categories, Executive has 3 etc)
3. Copy the matching data to the corresponding category sheet (excluding B, C, E, G, H, K, L, M, N, Q columns)
4. Minor formatting (auto column widths, freeze panes)
5. Sort the data by Title and Name in ascending order

This means every time you run the macro, the data is recreated on the category sheets. As your sample data is limited, some sheets will end up with just a header row with no actual data rows when you run the Transferdata macro.

ClearSheets can be used if required, it will just delete everything in the category sheets. Not strictly required as the TransferData macro will do this anyway. I have added another button on the Summary sheet that will run this macro.

Example-Managers.xlsm

There may be a way to sort the listbox but the code in my sample allows you to at least sort the data on the worksheet, which should then be reflected in your listbox.

This sounds similar to my reply to your other post:

Search for partial value

So try changing:

to:

In terms of your other request:

Quote

I have a list box and I want to be able to sort it by clicking or double clicking on the header of each column.

I assume you are referring to the data starting at A5 on the sheet called Worksheet.

If so, I've added a Worksheet_BeforeDoubleClick event to the Sheet1 code which should enable to sort by double clicking on any of the headers in A5 to G5.

Sample is attached.

Possible Tracking Number Database Version 3-sort cols.xlsm

You are welcome, there are many ways to do the same thing in Excel.

I was taught a long time ago to write code to make it as easy as possible to follow and maintain.

You can take this many steps further as required eg, you could actually write code to find the column with the specific header containing the Yes/No values. This would make it easier again to add more data.

In my code sample I've tried to make this manageable as follows.

First there is this line of code:

This points to the header row on both the Active and Archive sheets.

Second this line of code:

So if P3 has now become say R4 you would change to:

and:

Also, the following line of code:

will automatically cater for more columns (and rows).

My aim is to make changes relatively simple to implement.

OK, there is more information in the new example book but it's still not quite clear how you want to split up the data.

You have only 7 main departments and there is a sheet for each:

However, you seem to want to somehow use Column P to split up the data into those 7 sheets.

In the new example sheet you attached, the following are the unique values in Column P on the Alldata sheet:

So there seems to be a "lookup table" missing eg which of the 7 sheets does the data for Engineering, Human Resources, IST, etc actually need to be copied to. Some rows in the sample do have a matching sheet name already eg Marketing_Communications.

What I was excepting see was something like a table with all the sub-categories on the left and the corresponding sheet name on the right eg:

The left side would of course have to include all possible sub-categories, the above example is just derived from your sample.

The coding is not too difficult but without a table like this it is difficult to start using the sample data in Column P.

Also, as per previous post, you also mention "the data needs to be appended to the last row on the appropriate sheet" when you run the macro. However, you either then need to remove the data from the Alldata sheet or add another column that flags that the data has been copied otherwise the data will get copied again if you run the macro more than once.

You now also say "I will end up removing the B, C, E, G, H, K, L, M, N, Q columns before distributing the data to other sheets." but this could be relatively easily done as part of the macro that copies the data to the 7 sheets.

I assume you are referring to the following bit of code:

If so, maybe try changing this to :

Also, make sure the cells you are writing to are formatted with the "mm/dd/yyyy" format.

You are welcome.

For a partial string match anywhere in the string maybe try the following:

This will of course match anywhere in the string, if you specifically want to only match the end of the string you could try:

Okay, that helps a bit.

I can see there are 7 sheets from Community Services through to Senior Management. However, your columns V to X only seem to cover the first 3 of these.

I am assuming the list of names under each of the columns V to X contain the "sub-categories" you were referring to. But it's not entirely clear where these sub-categories can be found in the main data. Also, I would have expected 7 of these in total with their sub-categories listed in full eg, the Executive column has no sub-categories listed and of course there are no columns for the others eg, Infrastructure, etc.

Column P seems to mostly represent the "Primary Division" which I've assumed would correspond to 1 of the 7 sheet names. However, there are items in the sample in column P like "Engineering" and "FCSS" that seem to be sub-categories but there is no corresponding primary category (and therefore no idea which sheet) these belong to. It seems column P could contain either a primary or sub-category value.

It may be an idea to hold the full list of the 7 main departments and their sub-categories in another sheet, just to separate it from the main data. Even if it is retained on the Alldata sheet, I would expect the column headers will need to match the sheet names exactly eg, at the moment cell V1 = Comm Services but the corresponding sheet is actually Community Services.

You also mention "the data needs to be appended to the last row on the appropriate sheet" when you run the macro. However, you either then need to remove the data from the Alldata sheet or add another column that flags that the data has been copied otherwise the data will get copied again if you run the macro more than once.

You mention A:R contains the data but the code is also looking at V2:V11 - what does V2:V11 contain ?

If it contains main department name then that would be more than the 7 sheets you mention.

When you say sub-category, is there a column for main department in A:R and another column representing sub-category?

Your autofilter uses a variable called strSearch but the code provides no idea of what this value could be.

A sample workbook with some (non-sensitive) data and an example of what the output should look like is always helpful.

You are welcome.