Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Apr 2016
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Help please, trying to copy data from one sheet to a changing point on another.

    Hi all..

    Ok.. I have a weekly planner that I am trying to save in a yearly format for record keeping. what I am trying to do is when I click a button (or preferably just before I close the workbook) the contents of sheet 1 are copied and pasted to sheet 2 in a position relative to the date of the monday (start of the week) then the book is saved prior to closing.

    this is what I have so far.. but it doesn't work.. any help please?

    Code:
    Private Sub Save_Week_Click()'
    Dim FindString As Date
    Dim Rng As Range
    Dim RefCel As Integer
    Dim ThisMonday As Integer
    
    
     FindString = CLng(Date)
     ThisMonday = TODAY() - Weekday(TODAY(), 2) + 1
     
        With Rows("1:1")
            Set Rng = .Find(What:=FindString, _
                After:=.Cells(.Cells.Count), _
                LookIn:=xlFormulas, _
                LookAt:=xlWhole, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=False)
            If Not Rng Is Nothing Then
            Application.Goto Rng.Offset(1, 0), True
        Else
    'Give a message that todays date was not found
            MsgBox "Nothing found"
        End If
    End With
        
        RefCel = ThisMonday.Offset(1, 0)
        Range("B2").CurrentRegion.Select
        Selection.Copy
        Sheets("Sheet2").Select
        Range("RefCel").Select
        ActiveSheet.Paste
        Sheets("Sheet1").Select
    End Sub
    all help greatly appreciated

    Thank you

    Rob

    Edit:

    I haven't got the save function in yet as I haven't learned how to do that yet.
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Shuggy,

    Your question was answered in this thread:

    http://windowssecrets.com/forums/sho...r-and-counting

Posting Permissions

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