Columns to columns

doug5jmp

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

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

doug5jmp

Board Regular
Joined
Apr 27, 2010
Messages
62
Item List______4/07/10________Blank_________04/15/10________Blank________YTD
Soap[__________3____________Blank___________2[____________Blank_________5
Tp ____________3____________Blank___________3_____________Blank_________6
PT____________2____________Blank___________4_____________Blank_________6

This is easier to see
 

snowblizz

Well-known Member
Joined
Mar 16, 2009
Messages
1,123
You want something along these lines. It works on a basic level.

Code:
Sub datacopy()
Set ListSht = Sheets("Sheet3")
Set DataSht = ActiveSheet
ListSht.UsedRange.ClearContents
LastCol = DataSht.Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

DataSht.Columns(1).Copy ListSht.Columns(1)

For Col = 2 To LastCol
LastCol2 = ListSht.Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    If DataSht.Cells(1, Col).Value <> "" Then
    DataSht.Columns(Col).Copy
    ListSht.Columns(LastCol2 + 1).PasteSpecial (xlPasteValuesAndNumberFormats)
    End If
Next Col
Application.CutCopyMode = False
End Sub
 

doug5jmp

Board Regular
Joined
Apr 27, 2010
Messages
62
Being that I am a novice...I..
alt f11
copy paste code
run with f5

I do this on the new workbook or the one with the data?
In the end I have 3 more wkbks with the same list and want to do exactly the same thing.

Where to I list all of the wkbks/wkshts? in the code so that the new workbk/wkst has all 4 listings?

AND how to order them in the columns?

My goal is to get all the information in one sheet so I can easily run reports. At work I only have 2003 office and am plugging away at getting every bit of information into a much needed report. I need All dates listed in order by each of the 4 locations and sum up each location and total all locations...And when each locations updates the report would have to add the dates etc.
 
Last edited:

snowblizz

Well-known Member
Joined
Mar 16, 2009
Messages
1,123
The code is run from the source workbook and source sheet. Though now that its fully qualified it is not technically necessary.
I'll try to explain the code and then we'll see what we can do.
Code:
Sub datacopy()
Set DataSht = Workbooks("Sourceworkbook").Sheets("Sourcesheet") 'source sheet
Set ListSht = Workbooks("Destination workbook").Sheets("Sheet3") 'destination sheet

ListSht.UsedRange.ClearContents  'empties the destination sheet
LastCol = DataSht.Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column 'finds the last used column in source sheet

DataSht.Columns(1).Copy ListSht.Columns(1) 'copies "headers", basically Column A, from source to destination

For Col = 2 To LastCol 'loops through all columns in source sheet from column B to the last column used
LastCol2 = ListSht.Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column 'finds last used column in destination
    If DataSht.Cells(1, Col).Value <> "" Then 'test for an empty column, assumes any column with empty cell in row 1 is empty
    DataSht.Columns(Col).Copy 'copies whole column
    ListSht.Columns(LastCol2 + 1).PasteSpecial (xlPasteValuesAndNumberFormats) 'pastes numbers and formats to the destination sheet
    End If
Next Col
Application.CutCopyMode = False
End Sub
However, do I understand that you want to have data from multiple workbooks that you want consolidated into one? That makes it a lot trickier unfortunately. Right now it avoids the whole mess of finding out which is "new" column by just re-copying everything each time.

But would it be true that the last column is always YTD and the "new" column is always the last one before (with data).
 

doug5jmp

Board Regular
Joined
Apr 27, 2010
Messages
62
that is correct....multiple and the new and ytd...you are getting what I am looking for...
it is essentially looking for each location, and how many items were taken as physical inventory on a given date. Than combing each inventory location and update with last new column before YTD
 
Last edited:

snowblizz

Well-known Member
Joined
Mar 16, 2009
Messages
1,123
I've got it this far right now.
Excel Workbook
ABCDEF
1Item List4.7.201004/15/10YTD
2Soap325
3Tp336
4PT549
Sheet1
Excel Workbook
ABCDEF
1Item List4.3.20104.12.2010YTD
2Soap325
3Tp336
4PT549
Excel 2003 Sheet2
Excel Workbook
ABCDE
1Item List4.7.201004/15/104.3.20104.12.2010
2Soap3232
3Tp3333
4PT5454
Excel 2003 Sheet3
Excel 2003

Now in the items list will all items exist, and on the same row, for each separate location?
(YTD sum formula and sorting not fixed yet though.)
 

snowblizz

Well-known Member
Joined
Mar 16, 2009
Messages
1,123
This should now be close.
It needs to be run from a code module. I suggest placing the code module in the destination workbook. Referenced workbooks need to be open.
It copies the last data column before YTD on the assumption that this is the newest one.
Code:
Sub datacopy()
Set ListSht = Workbooks("Book1").Sheets("Sheet3") 'destination sheet
Dim sheetArray()
WkBArray = Array(Workbooks("Book1").Sheets("Sheet1"), Workbooks("Book1").Sheets("Sheet2")) 'add source sheets here

For Each wkB In WkBArray 'loop through all listed sheets
Set DataSht = wkB

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
 .Range(.Cells(1, 2), .Cells(LastRow, LastCol2)).Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
        DataOption1:=xlSortNormal
If .Cells(1, LastCol2).Value = "YTD" Then
i = 0
j = -1
Else
.Cells(1, LastCol2 + 1).Value = "YTD"
i = 1
j = 0
End If
.Range(.Cells(2, LastCol2 + i), .Cells(LastRow, LastCol2 + i)).FormulaR1C1 = "=SUM(R" & Row & "C2:R" & Row & "C" & LastCol2 + j & ")"
End With
End Sub
 

doug5jmp

Board Regular
Joined
Apr 27, 2010
Messages
62
Yes all Items in the Item list can be the same and in the same location. Though there are a couple of items that would not be in atwo locations(workbooks) BUT I can enter them and just have each location the does not use those items place a "0" if need be.

When the code is ran all sheets have to be open. Though will the destination workbk/worksheet update when it is opened by itself? All files, wkbks and wkshts are saved in the same drive on a server that is used by all locations.

THANK YOU...
 

doug5jmp

Board Regular
Joined
Apr 27, 2010
Messages
62
I am not sure what the code module is. Can you show in your code that: There are 4 wkbks are using wksheet 1 AND 1 wkbk with 5 wkshts.
And exactly how to input the code module on the destination worksheet.
 

Forum statistics

Threads
1,089,462
Messages
5,408,404
Members
403,202
Latest member
Novice43

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