Results 1 to 2 of 2
2016-05-07, 15:51 #1
- Join Date
- Apr 2016
- Thanked 0 Times in 0 Posts
Help please, trying to copy data from one sheet to a changing point on another.
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?
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
I haven't got the save function in yet as I haven't learned how to do that yet.
2016-05-07, 20:45 #2
- Join Date
- Aug 2010
- Pa, USA
- Thanked 649 Times in 592 Posts