Columns to columns

[doug5jmp]

I have a data list

Item List 4/07/10 Blank 04/15/10 Blank YTD
Soap 3 Blank 2 5
Tp 3 Blank 3 6
PT 5 4 9

Each week a new date and amount is entered, and the sum for YTD is updated. I would like to copy only those columns that have a date entered and the YTD, to another worksheet with the same item list. The problem is when I select the range I end up with the blank columns as well. This has to be dynamic and have the new date and item numbers (column) update and be placed into the new worksheet when they are entered in the current worksheet. Note...The documents will be separate excel wkbks.

I am a novice and not sure what to do.

 

[snowblizz]

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:

WkBArray = Array(Workbooks("Book1").Sheets("Sheet1"), Workbooks("Book1").Sheets("Sheet2")) 'add source sheets here

to e.g.

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?

 

[doug5jmp]

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]

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]

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]

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]

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]

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]

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. 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]

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]

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.