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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

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.
 

Forum statistics

Threads
1,089,543
Messages
5,408,859
Members
403,237
Latest member
Elmo9

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top