Transpose data from multiple worksheets to a single column on another worksheet

[Crughp]

Good Afternoon,

I am trying to take data off of multiple worksheets and then put it into a single column on another worksheet in the same workbook. I can use the transpose function to move a column from a single worksheet to another worksheet, but I need to do that from 58 sheets on data that can change from time to time. I would like to automate the process so as data in 1 sheet is updated it will update the master list as well. I have been unable to figure this out as of yet.

 

[My Aswer Is This]

You have to enter a new value in column A of the sheets. It does not copy all the previously entered data.

I told you to put the script in all the sheets except for master sheet.

 

[My Aswer Is This]

I asked this question in a previous post.
so if I enter "Alpha" in Range("A3") of sheet 5 you want "Alpha" entered in column A of sheet named master. Is this correct?
So we just enter "Alpha" into the first empty cell in column A of sheet named "Master"
Is this correct.

And you said yes that is what you want.
But it sounds like now this may not be what you want.

The script I provided automatically runs when you manually enter a value in column A of any sheet. If you put my code in that sheet.
So I'm surprised you asked what activates the script? The script runs when you enter a value in column A like I mentioned in my earlier question.

 

[TotallyConfused]

Hello Crughp

I see you are a new member, so I'd like to WELCOME you to MrExcel. This place is a gold mine of information, so I hope you'll enjoy your stay here. The person that has been helping you hasn't been here for awhile, so maybe I can answer your questions.

I have data in all of the fields of all of the sheets. However there is nothing populating in the "Master" sheet.

The code you were given will ONLY work on any new data that has been entered in column 'A' of one of those 58 sheets. Any data already in one of those sheets is ignored by this code. You will note that the name of the code is 'change' which means you have to physically enter the data NOW before anything will happen. The moment you type something into column 'A' of one of those sheets, this code will insert that item into your "Master" sheet. Data entered by a formula will also be ignored.

If you want the data already in one of those sheets to also be placed in "Master", you will have to use VBA code to do so. I think you mentioned 'Transpose', and while I'm not totally familiar with that command, it may work. I'm not sure.

Also looking at the Macro, the only sheet name I am seeing listed is the "Master" sheet. I am assuming that is because the macro is imbedded in all of the other sheets. Is that correct? I am trying to understand the how this works as well so I can maybe modify to use it for something else in the future.

Yes, that is correct. The code tells Excel where to PUT that sheet's column 'A' data. Each section of code applies to only that one sheet. That's the reason you were asked to copy that code into ALL the other sheets.

Since you are trying to understand how this code works, and I commend you for that, here is something you may try as an experiment if you want to be able to enter data in ANY column of one of those 58 sheets, and still have it placed in "Master" column 'A'. Delete (preferably comment out) the following two lines of code:

If Target.Column = 1 Then
End If

This action will only apply to the sheets where you modify the code, and as I mentioned, any data already there is ignored.

One of the best ways to learn Excel and VBA, is to set up a blank workbook, enter some dummy data and a sample code and then play with the code. Change something and see what happens. WARNING only do this with some random data not your actual data. Most times once data has been changed, it is impossible to recover the original.

I hope what I've said will be of help to you.

TotallyConfused

 

[Crughp]

Good afternoon,

I have attached the error that I am getting with the code. I understand everything stated above and have a couple more questions. Since it will only Transpose "new" data, will this also work on "pasted" data. For example, if I copy data out of say a CSV and put it on the worksheet in question, will the macro see that as new data? I am guessing yes, but I am not 100% sure so I thought I would ask. Also would it remove data that was also removed from the source column? So does it every verify that was it there is the most recent data?

For more information on what I am trying to do here, is I am taking csv exports out of AD, and then comparing them exports out of DHCP and out inventory management system in a single document. It is a little involved, with 58 AD groups with computers in them, 30 or so DHCP exports, and an everchanging inventory management system. I would like the ability to "tune the data" as needed by being able to update a specific AD group or DHCP range, and then recompile without having to create a new sheet to do this every time. I hope all of this is making sense. I want the formula to be a working formula that I can play around with in multiple data sets within the same workbook if that makes sense.

 

[TotallyConfused]

Hello Crughp

I see you have typed an apostrophe after the word 'Value'. Delete that apostrophe and that should stop the error message.

As far as whether the code will verify that the data typed is the most recent data, no that code will not do that, well...not really, but in a way I guess you could say it does. What it does do, is the second you type something, the code will be activated and do whatever it is supposed to do. So in that sense it does process the most recent data because that is what you just then typed in. If after you type something and the code has acted upon that data, then you realize what you typed was wrong, just go back and retype the correct information. This will re-activate the code, so now everything is updated.

You asked whether this code will remove data. I went back and looked at the code and as it's written, I don't see anything there that will remove any data. It simply copies what is in the first column over to your 'Master' sheet.

Whether or not the code will be activated if you paste something, I'm not sure. I've only used that type of code a time or two and it was for very simply reasons. I don't want to guess and risk giving you wrong information. While I certainly don't want to discourage you from asking questions here in the forum, I'd suggest you set up a blank workbook, place that code in one of the sheets and then try pasting something. WARNING only do this with some random data and not your actual data. Most times once data has been changed, it is impossible to recover the original. That way you can see for yourself what will happen. As soon as I get a chance, that's what I'm going to have to do to discover the answer to your question.

I hope this helps.

TotallyConfused