Copy column values from different sheets

**Nodster** · 02-04-2012, 07:42 PM

Hi folks

First post so I'll try and be concise

I have a workbook with 4 sheets: Early, Late, Daily, Monthly (the idea being that a worker will fill in values in the Early and Late sheets and the other sheets get filled automatically)

What I'm trying to do is copy the TOTALS column from the EARLY and LATE sheets to the DAILY sheet.

The totals (values only) from EARLY would be copied into DAILY C3:C25 and LATE into DAILY D3:D25 (if that makes sense)

I'm trying to do this using VBA so it is 'automatic'. I would also like to set this up so that each day the daily sheet is updated into a new column with the date at the top (at the moment i'm using text headings - I am aware of the =today() and =Now() but I don't know enough VBA to use this)

So if today is 4 feb columns are C:D, 5 Feb columns are E:F etc

Is this possible?

Thanks in advance
Cam

TEST.xls

**AlvaroSiza** · 02-05-2012, 01:12 AM

The following will (assuming you attach/assign it to a button like your others), upon click, move the early and late totals to each applicable row within the Daily sheet.

Sub MoveEarlyLateTotals()
'/==========================================================================
Dim ws1                 As Worksheet
Dim ws2                 As Worksheet
Dim ws3                 As Worksheet
Dim rngETotal           As Range
Dim rngLTotal           As Range
Dim rngDest             As Range
Dim LastCol             As Integer
'/==========================================================================
Set ws1 = ActiveWorkbook.Sheets("EARLY")
Set ws2 = ActiveWorkbook.Sheets("LATE")
Set ws3 = ActiveWorkbook.Sheets("DAILY")
Set rngETotal = ws1.Range("T2:T24")
Set rngLTotal = ws2.Range("T2:T24")

   With ws3
      LastCol = .Cells(3, .Columns.Count).End(xlToLeft).Column + 1
   End With
   
   Set rngDest = Sheets("DAILY").Range(Cells(3, LastCol), Cells(25, LastCol))
   rngETotal.Copy Destination:=rngDest
   LastCol = LastCol + 1
   Set rngDest = Sheets("DAILY").Range(Cells(3, LastCol), Cells(25, LastCol))
   rngLTotal.Copy Destination:=rngDest

End Sub

**Nodster** · 02-05-2012, 04:26 AM

Thanks for the reply

This works using a button, could I change it to work on either close sheet or open sheet.

Can't get this to work as it is referencing the cell (including the forumla), I'm only looking for the data to move (NO FORMULA)

Also I can't see where the date is added can you help with this.

Once again thanks for the speedy reply.

**AlvaroSiza** · 02-06-2012, 12:55 AM

This would be placed within the "DAILY" worksheet of the visual basic editor.

Revised to fire upon worksheet activation and to auto-fill date. Keep in mind that this will fire every time you activate the worksheet. This means that were you to visit the sheet 10 times in one day, you will see 10 iterations of the results each bearing the same date along the top (which is why I was leaning toward the click-button; 'when I'm ready' method. Some error handling to avoid this is in order, but I do not have the time to take that on right now. Also the .Copy Destination:= method will move formulas, not values, which as I am thinking about it is likely how your Early and Late totals will be calculated. You will need to replace the Destination:= with .PasteSpecial should this be the case.

Hopefully someone else will pick up the thread and help you out.

Private Sub Worksheet_Activate()
'/==========================================================================
Dim ws1                 As Worksheet
Dim ws2                 As Worksheet
Dim ws3                 As Worksheet
Dim rngETotal           As Range
Dim rngLTotal           As Range
Dim rngDest             As Range
Dim rngDate             As Range
Dim myDate              As Date
Dim LastCol             As Integer
'/==========================================================================
Set ws1 = ActiveWorkbook.Sheets("EARLY")
Set ws2 = ActiveWorkbook.Sheets("LATE")
Set ws3 = ActiveWorkbook.Sheets("DAILY")
Set rngETotal = ws1.Range("T2:T24")
Set rngLTotal = ws2.Range("T2:T24")
'/==========================================================================
   
   With ws3
      LastCol = .Cells(3, .Columns.Count).End(xlToLeft).Column + 1
   End With
   
   Set rngDate = Sheets("DAILY").Range(Cells(1, LastCol), Cells(1, LastCol))
   rngDate.Select
   myDate = Date
   rngDate = myDate

   Set rngDest = Sheets("DAILY").Range(Cells(3, LastCol), Cells(25, LastCol))
   rngETotal.Copy Destination:=rngDest
   LastCol = LastCol + 1
   Set rngDest = Sheets("DAILY").Range(Cells(3, LastCol), Cells(25, LastCol))
   rngLTotal.Copy Destination:=rngDest

End Sub

**Nodster** · 02-10-2012, 09:58 AM

Can I get around this by using the CASE statement..

eg

CASE = TODAY()

Case ("A2").value =TODAY()
copy data into column
else move along 2 columns
CASE("C2").value =TODAY()
copy data..

etc etc etc

I'm not really sure how to code this but could this be used to over come the worksheet duplicating data values as you would be 'forcing it' into one column. Should say that I would preset the cell values so that the macro just reads it then executes the copy and paste

Thanks again