Columns to columns

snowblizz

Well-known Member
Joined
Mar 16, 2009
Messages
1,123
The create a code module in the destination workbook open it, then open the VBEditor (alt+F11), from the menu pick "Insert" and then "Module". You'll see a a new "folder" and an object called "Module1", it should also open the window to place the code. It's basically the same thing that shows up when you record a macro.

The code expects that all items (rows) are in all sources (it only copies the column of data, so if it has the wrong number of rows it will be all messed up), so you'd need to do as you say and insert them and give them a 0 value if they are not present in any of the source sheets.

To add source sheets modify this line:
Code:
WkBArray = Array(Workbooks("Book1").Sheets("Sheet1"), Workbooks("Book1").Sheets("Sheet2")) 'add source sheets here
to e.g.
Code:
WkBArray = Array(Workbooks("Book1").Sheets("Sheet1"),  Workbooks("Book2").Sheets("Sheet1"),Workbooks("Book3").Sheets("Sheet1"),Workbooks("Book4").Sheets("Sheet1"),Workbooks("Book5").Sheets("Sheet1"),Workbooks("Book5").Sheets("Sheet2"),Workbooks("Book5").Sheets("Sheet3"),Workbooks("Book5").Sheets("Sheet4"),Workbooks("Book5").Sheets("Sheet5")) 'add source sheets here
Though will the destination workbk/worksheet update when it is opened by itself?
No, not by itself. It can be made to do it, however... I guess I should have asked earlier, but how would you like it to work. As the code is now, once run it will take the last data column from all sheets listed. As such onus is on you to make sure all the new data is actually inserted in all the workbooks/sheets before you run the macro. Otherwise you'll end up with duplicated data.
It might be more practical to place a copy of the code on each source sheet and e.g have a button you click when you have inserted new data?
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

doug5jmp

Board Regular
Joined
Apr 27, 2010
Messages
62
Adding a button to click/push to run would work as long as it is easy to train others on.

As shown in your post at 10:58, the data will show prior to the YTD from all workbooks and include all prior dates/columns in the order of the location (workbook)?

Will it matter if the "Item List" is in A4 and Dates and YTD are located in row 4 as well?
 

snowblizz

Well-known Member
Joined
Mar 16, 2009
Messages
1,123
Adding a button to click/push to run would work as long as it is easy to train others on.

As shown in your post at 10:58, the data will show prior to the YTD from all workbooks and include all prior dates/columns in the order of the location (workbook)?

Will it matter if the "Item List" is in A4 and Dates and YTD are located in row 4 as well?
The data would actually be ordered by date of the column, you asked for it sorted. The latest piece of code would only copy the "new" data. I could use the earlier suggestion and have it re-create all the data from the workbooks each time its run. If you don't have massive amounts of data it might not be too much overhead.
If you haven't I suggest trying it out on copies of your workbooks and see how it works.

It shouldn't matter where it starts, the code needs to be modified to take this into account though. However, the copying is based on copying the whole columns so would include any values in rows 1-3 if present.
 

doug5jmp

Board Regular
Joined
Apr 27, 2010
Messages
62
I would like to take a look at both examples...Would you list the data in teh code that needs to be removed when I want All columns vs. latest column?

I had set up a merge of A1:L2 to label the Location and row 3 was blank...I can alter the worksheets and delete the label and have the item list at A! for the heading.
 

doug5jmp

Board Regular
Joined
Apr 27, 2010
Messages
62
I had set up a PowerPoint to show co-workers my thought process. I could send that to show you exactly how the files link and see what I am talking about. BUT I don't know how on here.
 

snowblizz

Well-known Member
Joined
Mar 16, 2009
Messages
1,123
If you send me a private message with your email I can send you the test workbook I have. Contains both versions.

Correspondingly you can then send me the PP file.
 

doug5jmp

Board Regular
Joined
Apr 27, 2010
Messages
62
File rec'd and PP sent.
Is the purpose of the green text for my informational purpose or is it stating the function of the code?
 

snowblizz

Well-known Member
Joined
Mar 16, 2009
Messages
1,123
File rec'd and PP sent.
Is the purpose of the green text for my informational purpose or is it stating the function of the code?
The green text are comments. It has only informational value. It's actually for your informational purposes stating the function of the code.:biggrin: Or something like that. Helps me recalling what on earth I was doing as well.

Referring to our e-mail correspondence since you will have other people add data the "single" entry version would most likely be the most appropriate. The only robustness issue here is to make sure they do not continually save the same data or enter multiple columns of data in one go. Though some measures of protection can be placed, Excel is by no means secure.

It is possible to have the workbook be saved when the button pushing the data to the summary workbook is pressed. There's no real limit to what you can have the button do, if it can be done by Excel we can link it to the button.
 

doug5jmp

Board Regular
Joined
Apr 27, 2010
Messages
62
After reviewing the PP, do think I would be able to create a pivot table where I can select a time period (one month for example), have it pull all of the numerical data (number of items) from each location AND have a sum function of all the items during said time period?

OR is it even possible to use a pivot table in such a manner?
Would I have to use a shadow workbook or worksheet where all the data is reported to and than create a pivot table from?
 

snowblizz

Well-known Member
Joined
Mar 16, 2009
Messages
1,123
Unfortunately I don't know the first bit about Pivot Tables. But I do think that's something you could use as this is essentially database-like operations.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,783
Messages
5,410,384
Members
403,316
Latest member
samhadian

This Week's Hot Topics

Top