Copy forward from sheet to sheet

E

eashanders

New member
Joined
Nov 17, 2014
Messages
3
Reaction score
0
Points
0
I have a workbook that has 31 sheets. One for each day of the week. All sheets are identicle. I need to move data forward from sheet to sheet. For example, Sheet "1st" data is entered in cells B17:H25. This range contains 10 rows. If data exists it transfers to Sheet "2nd", same range. On Sheet "2nd" data may be added or deleted but should be transferred to sheet "3rd", and this goes on until sheet "31st".
I'd like this to happen upon save, however, the workbook would be saved mutiple times throughout the day so the next consecutive sheet would have to be cleared prior to save. (1 wouldn't want 4 rows transferred and then 1 removed and the last row stays on the next sheet.​
 
Long weeks there. Here we only get 7 days per week :)

Not following what you want. Everyday copy today's sheet to tomorrow?

If so run this on save
Code: 
Sub CopyIt()
Dim sToday As String, sTomorrow As String
    sToday = "Sheet" & Format(Now(), "d")
    sTomorrow = "Sheet" & Format(Now() + 1, "d")
    
    On Error Resume Next
    Sheets(sTomorrow).Delete  'tomorrow may never come so take precautions
    On Error GoTo 0
    
    Sheets(sToday).Copy After:=Sheets(sToday)
    ActiveSheet.Name = sTomorrow

    Sheets(sToday).Select   'go to today
End Sub
 
Last edited:
Sorry I was tired when I wrote the post.
31 sheets for each day of the month. All are are identicle and data may exist in B17:H26. Upon save I need to clear data on that range in the next sheet and then copy data from previous sheet. So when working on "1st" data is entered in B17:H26. This is a day long working file and saved mutiple times throughout the day. So everything that is there the 1st time the file is saved during the day may not exist at the end. This is why the same range on the next day needs to be cleared 1st and then copied.
 
Gone from 31 days in a week to 31 sheets for each day of the month. Time to sit down and think about this.

Is it necessary to keep or have all previous sheets? What happens at the end of the month? What's in the B17:H26 range, values, formulas, something else? Is this workbook used everyday, no chance of weekends or holidays or any days missed? How are you doing this now?
 
Seriously! You have nothing better to do than bust someone's chops that is only asking for a little help? I'll go to a board that is a little more helpful.
 
Pretty tough to help without knowing what's needed. That's why I asked the questions, but hey... you do what you want.

Before a solution is achieved, be it here or elsewhere, pretty sure more than one of those questions will need to be addressed.

Good Luck with your project.
 
If it's only a set range then modify my previous code for that.
But as NOS says, we can't help if we don't understand everything you want to do.

Code: 
Sub CopyIt()
Dim sToday As String, sTomorrow As String
    sToday = "Sheet" & Format(Now(), "d")
    sTomorrow = "Sheet" & Format(Now() + 1, "d")
    
    Sheets(sTomorrow).range(" B17:H26").value2 =Sheets(sToday).range(" B17:H26").value2 
End Sub
 
eashanders, if you've cooled down enough to try something..... put this in the ThisWorkbook module of your workbook and if your sheets are named 1, 2, 3, 4 etc. and if you never miss a day it should do what you want. Don't know what you do or require come the end of the month.

Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim nxtday As Integer
    
nxtday = Day(Date) + 1
If nxtday = 32 Then
    Exit Sub
Else
    Sheets(nxtday).Range("B17:H26").ClearContents
    Sheets(Day(Date)).Range("B17:H26").Copy Sheets(nxtday).Range("B17")
End If

End Sub
 
Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
   if month(date)=month(date+1) then Sheets(day(date+1)).Range("B17:H26")=Sheets(Day(Date)).Range("B17:H26").Value
End Sub
 
You must log in or register to reply here.
Back
Top