Copy Row from Previous Worksheet to Current Worksheet

**Petrolcb41** · 09-25-2010, 01:06 PM

Hello all,

I was hoping for some assistance creating a macro for my situation.

so basically, I have a workbook that contains a worksheet for each day of the month (named "1", "2", "3", etc. to "31"). Each worksheet has a series of rows and columns where the columns are headed with the denominations in question (i.e. $100, $50, $20, etc.) and where each cell within each column contains how much of that particular denomination was removed at a particular time (i.e. -$2000, $-5000, etc.).

Now, B6:R6 are cells which contain that particular denomination's starting cash for that given day/worksheet. Again, we log under each column the cash used (be it positive or negative). The columns then sum them and give day ending cash totals in cells B37:R37.

So, what i'm trying to do it create a general (non-sheet specific) macro that will do the following:

1. highlight B37:R37 on current sheet
2. paste special-value and number formats into cells B6:R6 in the NEXT worksheet in the workbook (this basically transfers day 1's ending cash totals and makes them the starting totals for day 2, day 2 to day 3, and so on)

I attempted to do this by recording macros, however, i can only record 5 in the version of excel my company has (excel 2003) but i need a macro that can do it for 31 sheets. (technically 30 because sheet 1's starting totals must be entered manually)

My plan is assign this macro to a button which will be placed somewhere on worksheets 2 through 31 that will allow the use to click it, and have the ending totals from the previous day(B37:R37) prefill into the current sheet's starting totals cell range (B6:R6).

I do not want to just make the cells from one sheet equal the previous day's values because then i will have 31 sheets that all have figures on them and it will become a little convoluted.

Any help anyone could provide would be MUCH appreciated.

Thanks & enjoy your weekends,

Vin

**Andrew-R** · 09-25-2010, 01:50 PM

Here you go...

Sub HighlightAndCopy()

On Error GoTo ErrTrap

ActiveSheet.Range("B37:R37").Interior.Color = vbYellow
ThisWorkbook.Sheets(ActiveSheet.Index + 1).Range("B6:R6").Value = ActiveSheet.Range("B37:R37").Value
ThisWorkbook.Sheets(ActiveSheet.Index + 1).Range("B6:R6").NumberFormat = ActiveSheet.Range("B37:R37").NumberFormat

Exit Sub

ErrTrap:
If Err.Number = 9 Then
  MsgBox "This is the last sheet in the workbook"
End If

End Sub

**shg** · 09-25-2010, 02:58 PM

Here's an alternative:

Function OtherSheet(ByVal vRng As Variant, _
                    Optional ByVal iIndex As Long = 1, _
                    Optional bRelative As Boolean = True) As Range
    ' shg 2010
    
    ' Returns Range(vRng) on
    '   Worksheets(iIndex) if bRelative = False
    '   Worksheets(iIndex + Application.Caller.Worksheet.Index) if bRelative = true
    
    ' Defaults to the range on Application.Caller.Worksheet.Next
    
    ' If the referenced range doesn't overlap the calling range, just pass the range:
    '   =SUM(OtherSheet(A1:A3))
    
    ' If they do, pass the range reference in quotes to avoid a circular reference error:
    '   =SUM(OtherSheet("A1:A3"))
    
    ' To pass multi-area ranges, enclose in parens or quotes:
    '   =SUM(OtherSheet((A1:A10, C1:C5), 2, False))
    '   =SUM(OtherSheet("A1:A10, C1:C5", 2, False))
    
    ' No error checking -- it's a UDF _only_
    
    Application.Volatile True
    If TypeOf vRng Is Range Then vRng = vRng.Address
    
    With Application.Caller.Worksheet
        If bRelative Then iIndex = .Index + iIndex
        Set OtherSheet = .Parent.Worksheets(iIndex).Range(vRng)
    End With
End Function

In each worksheet other than "1", in B6 and copy across,

=OtherSheet(B37, -1)

The function is, per force, volatile.

**Petrolcb41** · 09-27-2010, 01:39 AM

Andrew,

Could you double check on this for me it doesn't appear to be working?

Thanks!!!

Originally Posted by Andrew-R

Here you go...

Sub HighlightAndCopy()

On Error GoTo ErrTrap

ActiveSheet.Range("B37:R37").Interior.Color = vbYellow
ThisWorkbook.Sheets(ActiveSheet.Index + 1).Range("B6:R6").Value = ActiveSheet.Range("B37:R37").Value
ThisWorkbook.Sheets(ActiveSheet.Index + 1).Range("B6:R6").NumberFormat = ActiveSheet.Range("B37:R37").NumberFormat

Exit Sub

ErrTrap:
If Err.Number = 9 Then
  MsgBox "This is the last sheet in the workbook"
End If

End Sub