Results 1 to 10 of 10

Thread: Copy forward from sheet to sheet

  1. #1

    Copy forward from sheet to sheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    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 by WizzardOfOz; 2014-11-18 at 07:57 AM.

  3. #3
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    370
    Articles
    0
    Excel Version
    2020
    Even ranges are longer over there than on this planet :

    in cells B17:H25. This range contains 10 rows

  4. #4
    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.

  5. #5
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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?

  6. #6
    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.

  7. #7
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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.

  8. #8
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    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

  9. #9
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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

  10. #10
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    370
    Articles
    0
    Excel Version
    2020
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •