Columns to columns

doug5jmp

Board Regular
Joined
Apr 27, 2010
Messages
62
...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?
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

snowblizz

Well-known Member
Joined
Mar 16, 2009
Messages
1,123
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.
Code:
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.
Code:
Call datacopySingle
 

doug5jmp

Board Regular
Joined
Apr 27, 2010
Messages
62
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

Well-known Member
Joined
Mar 16, 2009
Messages
1,123
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

Board Regular
Joined
Apr 27, 2010
Messages
62
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

Board Regular
Joined
Apr 27, 2010
Messages
62
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

Well-known Member
Joined
Mar 16, 2009
Messages
1,123
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.

Code:
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

Board Regular
Joined
Apr 27, 2010
Messages
62
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

Board Regular
Joined
Apr 27, 2010
Messages
62
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...
 

Forum statistics

Threads
1,089,599
Messages
5,409,207
Members
403,256
Latest member
Viq

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