EXCEL VBA MACRO Assistance

July 17, 2017 at 08:30:59
Specs: Excel 2013, MACROS
One static Excel workbook with a ton of raw data that has a static number of columns (A-EK) that is always the same but the lines (1-X) is not static and always changing with the total raw data lines being most thousand.


One column (called system) in this case it’s column B on the raw data workbook holds all the different systems that raw data is pulled from & needing sorted by.


The overall goal is to create a workbook for each individual system (currently 9) & have each system sorted by what system the raw data belongs to; so for simplify sake lets call them system 1-9. So I need to pull all raw data from “system 1 in column B” to the workbook I labeled “System 1” plus the 21 columns I noted above raw data information but keeping in mind that system 1 might have 100 lines one time but 150 lines the next.


I will not be using all the columns (A-EK) & only needing a few (B, DR, E, H, Q, S, T, AL, AM, AN, AO, AR, AZ, BT, CP, DG, DH, DI, DJ, DY, & EK in this order) of them pulled over with always changing data lines. I also need to create a few custom columns for 6 of the 9 systems and put within the order above in a certain place like between columns B & DR would have 3 new columns for 6 systems but between DR & E there will be 4 new columns for all 9 of the systems. I am using the columns letters from the raw tab & with the new workbooks created I would have B information in the A column, create 3 new columns for 6 of the system workbook then DR column would become E in those 6 workbook but in the systems not needing those 3 the DR would just be in the B column.


Can someone please help get me started/finish? Trying to figure out best way to handle this headache. Currently just recording MACROS but not sure I am doing it right.


See More: EXCEL VBA MACRO Assistance

Reply ↓  Report •

#1
July 20, 2017 at 03:32:40
sorry but I find it extremely difficult to visualize the layout of a workbook/worksheets without the data being presented correctly. please can I ask you read the below link and present some sample data, only then can we begin to assist you.

https://www.computing.net/howtos/sh...

Thanks


Reply ↓  Report •

#2
July 20, 2017 at 05:30:09
I have a raw data sheet that comes loaded with 300-1,000 lines of information. Out of all these lines they have between 6-9 different systems combined in this workbook. This raw data sheet has 141 different columns (A1-EK1) but the useful information for half of the systems is only 21 of the columns & of course they are not just in order for easy copy & paste. I also have to add new columns based on research results where they are placed in between existing names form the Raw Data sheet.

I have created a MACRO that creates all the sheets I wanted, the name all the columns in order that are requested, but I am stuck on how to bring over the Raw Data information for each system,.

Column D (broad different system names) is best to find the basic name of everything called System 1 but Column A is what they want because it shows System 1, Big System 1, & Location System 1; so D lumps all System 1 into one & there is an additional worksheet called System 1 that needs the information carried over.

So A1-EK1 is the Raw Data order but let say I only want five columns of information & those five are in different order than Raw Data; e.g. A1, M1, X1, B1, & AB1 would be A, B, C, D & E on the new worksheet called System 1.

The key here is the D column which is not used in new worksheets is how to tell what all system's go into the column A. like above example about System 1. So when I locate all the System 1 in column D, I need to pull out all the information in the Raw Data columns needed (A1, M1, X1, B1, & AB1) to have a X amount of row just for System 1 & because the amount of row each month change I am not about to provide a solid number for X.

Because there are 9 different systems & because the total amount of lines vary each month; I tried to use Data Filter to sort what I need but did not work. Can you explain how to bring stuff over when there is not a pattern but based on stuff in a column?


Reply ↓  Report •

#3
July 20, 2017 at 06:21:43
Lets try again.

sorry but I find it extremely difficult to visualize the layout of a workbook/worksheets without the data being presented correctly. please can I ask you read the below link and present some sample data, only then can we begin to assist you.

https://www.computing.net/howtos/sh...

Thanks


Reply ↓  Report •

Related Solutions

#4
July 20, 2017 at 06:23:22
How do I attach samples?

Reply ↓  Report •

#5
July 20, 2017 at 06:24:33
I have looked at the link several times and does not help me figure my problem out; maybe I do not understand it.

Reply ↓  Report •

#6
July 20, 2017 at 06:32:15
The howto tells you how to present your data on this site, you cannot attach a file here, we don't allow it. What you can do is present you data manually such as

Sheet "Raw Data"


       A           B           C          D
1     Cat         Dog         Sheep    Horse
2     Tree        Leaf        Garden   Hose
3     Apple       Pear        Grapes   Peach
4
5

Then you can present your other sheets and we can see what you are working with, otherwise it will be a bit of guessing and that means coding to what we THINK is right, which means making changes when you say "No I didn't ask for that" and we don't really have time for re work. might as well gather the requirement in one and offer a solution in one.


Reply ↓  Report •

#7
July 20, 2017 at 07:03:38
Please take the following comments in the spirit in which they are intended. We want to help, but we are unable to at this point.

Please keep in mind that we can not see your workbook from where we are sitting nor are we as familiar with your requirements and processes as you are. You need to provide a lot of detail and you need to be consistent with what you are asking for. When we get unclear (or changing) requirements, we often end up wasting time by providing a solution that does not meet your needs which then has to be modified or even completely rewritten. Since we are all volunteers here, it's your job to make it as easy as possible for us to help you.

OK, that said...

Questions:

In your original post you said "The overall goal is to create a workbook for each individual system."

In your most recent post you said "I have created a MACRO that creates all the sheets I wanted."

Do you want this data moved into separate workbooks or separate worksheets?

I'll assume sheets, so I have a few questions:

1 - In your original post you said the system names were in Column B, now you say they are in Column D. Which is it?

2 - Regardless of which Column the system names are in, does each system name have an exact match to a specific Sheet Name? e.g.

D5 = System 1 (exactly, no leading or trailing characters)
Sheet(3).Name = System 1 (exactly, no leading or trailing characters)

3 - Is each run of the macro supposed to clear the data in each sheet and populate it with new or is the new data supposed to be appended to bottom of the existing data?

4 - In your original post you said something about 21 columns, now you are talking about only 5. Is that a change or are you asking for generic code that you will then modify to meet your exact needs?

5 - In your original post you said something about adding 3 columns for certain systems, 4 for others. However you did not provide any specifics, so that section was extremely confusing. In addition, you did not mention those "new columns" in your latest response. Are they still required? If so, you need to provide more detail.

re: "Can you explain how to bring stuff over when there is not a pattern but based on stuff in a column? "

That is a fairly common requirement and easily dealt with with via the following instructions, which will determine last cell with data in the specified columns:

Sub HowMuchData()
'Determine last cell with data in Column A
   lastRow_A = Range("A" & Rows.Count).End(xlUp).Row

'OR

'Determine last cell with data in Column B
   lastRow_B = Cells(Rows.Count, 2).End(xlUp).Row
End Sub

The same instructions can be used to determine the next empty cell in a column just by adding 1 to the result:

Sub NextRow()
'Determine next empty cell in Column A
   nextRow_A = Range("A" & Rows.Count).End(xlUp).Row + 1
End Sub

Generically, I would simply run down Column B (or D) and copy the data associated with each System name to the appropriate sheet, one Row at a time. That is why we need to know the "connection" between the System name and the Sheet name. If they are an exact match, determining the appropriate sheet is easy. If not, we need to include code to determine which Sheet is associated with each System. There are efficient ways to do that and inefficient ways. Of course we want to provide the most efficient code that we can, so we need more details.

Finally...

Copying the entire row is easy
Copying only certain columns from each row adds a level of complexity
Copy only certain columns and also rearranging them on the destination sheet adds another level of complexity
Copy only certain columns and also rearranging them plus inserting new columns adds another level of complexity
Copy only certain columns and also rearranging them plus inserting X new columns for certain systems and Y new columns for other systems adds another level of complexity.

I hope that you can now see why we need very specific and consistent requirements before we can offer a working solution. If you want some generic code or a few lines of code that explain how to do a specific task which you can then modify, we can do that also. Of course, modifying the generic code to make it work for your requirements depends on your level of VBA experience.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#8
July 20, 2017 at 07:11:31
re: "How do I attach samples? "

You can't attach any files to posts in this forum. One option:

After you remove any confidential or personal information from the file (or create a file with generic data)
you can upload it to zippyshare.com and then post the link back here in the forum.

We can then download the file and work on it directly instead of having to create our own workbook which might not match yours.

Of course, we will still need specific and consistent requirements.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

Ask Question