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.

 

[doug5jmp]

...getting things together...creating establishing and making the "Button" work.

Information would be entered in a new column with date at top and # of items below it...

Now we need to make the wkbk/wksht update and receive the new columns and its data...

So we would "push" the button and have it report to the wkbk/wksht and automatically save. (and if possible close the file). Call the button "Issue inventory report"

What code goes into Each source wksht and what code module is being put into the destination wksht?

 

[snowblizz]

Taking the approach that each source worksheet will have its own button this code will be placed in the source workbook, in a code module.
It opens the destination workbook, copies over the last column, sorts and updates the destination YTD and closes and saves both workbooks.

You mentioned earlier that you had one workbook with multiple worksheets, would they all be entered individually or would the user need to enter data from several sheets in the same workbook? Basically do you need the code to not close the workbook in some cases.

Sub datacopySingle()
Application.ScreenUpdating = False
Set listWkB = Workbooks.Open("BookTest.xls") 'destiantion workbook
Set listsht = listWkB.Sheets("Sheet3")  'destination sheet
Set datasht = Workbooks(ThisWorkbook.Name).Activesheet  'source sheet, will always be the active sheet ie the one the button is placed on

LastCol = datasht.Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
LastCol2 = listsht.Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

    datasht.Columns(LastCol - 2).Copy
    listsht.Columns(LastCol2 + 1).PasteSpecial (xlPasteValuesAndNumberFormats)

Application.CutCopyMode = False
LastCol2 = listsht.Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
LastRow = listsht.Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Row

 With listsht
 If LastCol2 <> 2 Then ' checks whether a sort is neded, if so sorts by date header
 .Range(.Cells(1, 2), .Cells(LastRow, LastCol2)).Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
        DataOption1:=xlSortNormal
End If
If .Cells(1, LastCol2).Value = "YTD" Then 'checks whether YTD header is present
i = 0
j = -1
Else 'if not present creates it
.Cells(1, LastCol2 + 1).Value = "YTD"
i = 1
j = 0
End If
'updates YTD formulas
.Range(.Cells(2, LastCol2 + i), .Cells(LastRow, LastCol2 + i)).FormulaR1C1 = "=SUM(R" & Row & "C2:R" & Row & "C" & LastCol2 + j & ")"
End With
ThisWorkbook.Save 'saves source workbook
listWkB.Save 'saves destination workbook
listWkB.Close False 'closes destination workbook
Application.ScreenUpdating = True 'turns screen updating back on
ThisWorkbook.Close False 'closes source workbook
End Sub

Then when you create the button on each sheet use the following code for the button click.

Call datacopySingle

 

[doug5jmp]

The one wkbk with multiple wkshts will all have data entered PRIOR to the workbook closing and saving.

What happens when a location(s) have the source files open and 3 are saving the source files at the same time?

What in the world will the destination workbook do????

 

[snowblizz]

The one wkbk with multiple wkshts will all have data entered PRIOR to the workbook closing and saving.

So one person enters the data in all the worksheets in that book. In this case we could modify the "single" copy code to copy the data from all worksheets. It will look a lot like the copyall code. Just need to find a way to place the button appropriately. Are all the sheets in this workbook datasheets?

What happens when a location(s) have the source files open and 3 are saving the source files at the same time?

What in the world will the destination workbook do????

I would imagine that the destination workbook will report that it is locked for editing, and most likely end up throwing an error in the code stopping execution.
It seems using this code line instead will open the file as read only but then notify the person when the opened file is available for editing. As the destination file should only be open for a few moments I'm hoping it will solve any problems. I'm not too familiar with this part of the operations either, and I can't test how this works.

Set listWkB = Workbooks.Open("BookTest.xls", Notify:=True) 'destination workbook
</pre>

 

[doug5jmp]

Are all the sheets in this workbook datasheets?

</pre>

All the sheet in the workbook are datasheets.

 

[doug5jmp]

How/what image would be used as the "button" and the [Call datacopySingle]

Is the "single" indicating the last column of the source wksht?(single column)?

For my future reference if I wanted to use all data, and send that to another workbook, what would the code be? For both the source and destination wkbk.

 

[doug5jmp]

I would imagine that the destination workbook will report that it is locked for editing, and most likely end up throwing an error in the code stopping execution.
It seems using this code line instead will open the file as read only but then notify the person when the opened file is available for editing. As the destination file should only be open for a few moments I'm hoping it will solve any problems.


...The thought process is that the source file will open as read only and indicate that it is ready for editing when the destination file is closed?

Can we create the button so that it opens red "read only" and turns green "available for editing"?

 

[snowblizz]

Yes the one called "single" is the one that takes the last (data) column. Now it strikes me I have made one assumption that is not correct. You wrote that you had placed "YTD" in Column DD originally. The code will not work in that case, since it assumes the new data column will be 2 columns to the left of the last column with data. It would be better to just keep pushing YTD "outwards" by inserting a new column for the user to enter data in.

The code will also require there be headers for column A in the destination workbook. The .Find method will not work if the destination workbook is completely empty. (The more I'm thinking, the more assumptions I'm remembering)

This will take all the sheets in a workbook and copy the last column from each and paste it to the destination workbook, sort everything by date. And then save and close both workbooks. Also placed in a module in the source worksheet.

Sub datacopyMultiSingle()
Application.ScreenUpdating = False
Set listWkB = Workbooks.Open("BookTest.xls", Notify:=True) 'destiantion workbook
Set listsht = listWkB.Sheets("Sheet3")  'destination sheet

For Each wkS In ThisWorkbook.Worksheets
Set datasht = wkS  'source sheet
LastCol = datasht.Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
LastCol2 = listsht.Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

    datasht.Columns(LastCol - 2).Copy
    listsht.Columns(LastCol2 + 1).PasteSpecial (xlPasteValuesAndNumberFormats)
Next
Application.CutCopyMode = False
LastCol2 = listsht.Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
LastRow = listsht.Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Row

 With listsht
 If LastCol2 <> 2 Then ' checks whether a sort is neded, if so sorts by date header
 .Range(.Cells(1, 2), .Cells(LastRow, LastCol2)).Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
        DataOption1:=xlSortNormal 'sorts the columns starting from column B, row 1
End If
If .Cells(1, LastCol2).Value = "YTD" Then 'checks whether YTD header is present in row 1, column [whatever the last column is]
i = 0
j = -1
Else 'if not present creates it
.Cells(1, LastCol2 + 1).Value = "YTD"
i = 1
j = 0
End If
'updates YTD formulas
.Range(.Cells(2, LastCol2 + i), .Cells(LastRow, LastCol2 + i)).FormulaR1C1 = "=SUM(R" & Row & "C2:R" & Row & "C" & LastCol2 + j & ")"
End With
ThisWorkbook.Save 'saves source workbook
listWkB.Save 'saves destination workbook
listWkB.Close False 'closes destination workbook
Application.ScreenUpdating = True 'turns screen updating back on
ThisWorkbook.Close False 'closes source workbook
End Sub

 

[doug5jmp]

What if...
To solve the problem of enter a read only code and say when the worksheet is ready to edit...

I will create another wksht where it will list the items and ONE column that the Date (heading) and numbers can be entered. We can than use said button to send the information to what we are calling the source wksht. I would do this for each location.

Is there a way to use the button to send the data and then erase the column after? Once the button is pushed, Can there be a pop up that asks - Do you want to submit your inventory count? with a Submit or Cancel button?

Can the destination wkbk than revert to having an update button, once pushed will pull the latest column data from the source wksht (single?) OR another array?

So...Now there would be the:
1. Submit than erase and close wksht sending to the next "OPEN" column of the "source wksht"
2. The Prior Source wksht (I will use as the aggregate source file)
3. Destination wkbk

 

[doug5jmp]

I'm just sitting here in awe! You REALLY are impressive!

I will set up the destination wkbk with the same item list in column A...