VBA Copy Column data based on multiple dynamic criteria

Hi,

This is my first post here, so thank you for having me. I have been using these forums as a source for my issues since I started with VBA 3 months ago, and have written the majority of a program thanks to forums like these

I have a workbook with 5 sheets with approximately 30 columns per sheet. Each sheet uses identical column headers, but they differ in their range location by sheet.

I have a 6th sheet with individual user preferences stored in it. This sheet has a unique userID and then all of the 30 columns in the other 5 sheets with either a "1" or a blank value in it. The "1" indicates that this user has data for the column in the subsequent 5 sheets.

What I need to do, is for a particular user ("Smith123"), loop through his/her user preferences sheet and determine which columns have a "1" in them. The code then needs to loop through the subsequent 5 sheets and copy only those corresponding columns for the user into a new workbook. The user is a Public String that is entered in a user form when the individual opens the workbook. Let's call this variable a "UserID".

I cannot accomplish manually because there are too many users.

Sample:

Sheet1 - User Preferences
UserID Apples Oranges Kiwi Plums
Smith123 1 1 1
John 234 1 1

Sheet2 - Database Totals
UserID Apples Oranges Kiwi Plums
Smith123 75 23 54
John234 19 4

Sheet3 - Average Consumption
UserID Apples Oranges Kiwi Plums
Smith123 7.5 2.3 5.4
John234 1.9 .4

In this example, the code needs to know for UserID "Smith123" to loop through Sheet2,3,4 etc. and copy only the columns that have a "1" in them based on the Sheet1 preferences (so Apples, Organges, and Plums). The desired end-state is a new workbook with a sheet "Database Totals" and Smith123 values for Apples, Oranges, and Plums (as well as one for Average Consumption etc) and the flexability to use the variable "UserID" to run this macro for any user (e.g. do not hard code for Smith123).

I uploaded a sample workbook for your reference. The columns in the "User Preference" sheet are static and do not move, but the columns in the other sheets can move.

Any thoughts?

Your sample shows the "user preferences" sheet and the two output sheets ; "DataBase Tools" and "Average Consumption" but it does not show any of the numbered sheets you speak of i.e. "Sheet2,3,4 etc." Could you give an example of what these sheets look like. Also what will the exact names of These sheets you want to collect from be called ?

My apologies if my first post was not entirely clear, I think I gave too much information. Let's just work from the example I posted.

The attachment is the current state:
"User Preferences" sheet
"Database Totals" sheet
"Average Consumption" sheet

The data in this workbook is a good example of what I'm working with - different columns are populated with data based on the UserId column. To reiterate, these columns can change places so I need to compare column headers and not just look by their range. The "User Preference" sheet captures, by UserId, which column will have data in it.

The desired end-state should be a new workbook with the two sheets:
"Database Totals" sheet
"Average Consumption" sheet

And only columns with data in it. So, for userID "Smith123" these columns would be "Apples", "Oranges", and "Plums" and the subsequent data in those columns.

Ok - I'm getting pretty far along with this. But when I try to find the last row used after filtering, my code fails. How can I find the last row / only using visible cells?

I am addressing 1 -3 in my last post here:
1. Filter Worksheet("User_Preferences") by UserId ("Smith123" in my example) - Always in Range A.
2. With the visible cells, hide any columns that are blank
3. Copy the visible column headers into new workbook, creating two new sheets "Userview DT" & "Userview AC"

I figured out that I don't need to hide the columns that are blank because I can just take the column headers that have the value of 1 in them.

Please Login or Register  to view this content.

It actually does work - I just needed to declare "Lastrow" as an Integer. I also removed the variable "Categoryvalue" and just made the cells equal to each other.

Please Login or Register  to view this content.