Need some help with a Macro

[SimpleDAS]

Hi all,

I am a completely new user to Excel Macros!
I have sourced myself a code from this site, that goes a little something like:

Private Sub Worksheet_Activate()


Dim LookupRange As Range
Dim MoveToRange As Range
Dim MoveDay As Long
Dim ClearRow As Range


Set LookupRange = Worksheets("Sheet1").Range("C2")
Set MoveToRange = Worksheets("Sheet1").Range("E2")
MoveDay = Day(Now)
If MoveDay = Day(Today) - 365 Then
If MsgBox("Data will be moved today as some training has been recorded for over a year.**Do you wish to move the data?", vbYesNo + vbQuestion, "Move Data") = vbYes Then
MoveToRange.Value = LookupRange.Value
LookupRange.Value = ""
ClearRow.Value = ""


End If
End If


End Sub

I can't seem to get the desired result however, and I suspect it is because I am not fulfilling the requirements for the "MoveDay" variables.

For context, I am trying to create an automated function that will check all dates for some staff training inputs, and if the date exceeds a certain paramater, will move the data to another area. (At the moment the code specifies it wants it to be moved from C2 to E2 but I am wondering if this can be expanded to move to a different Worksheet, and if I can get it to move more than the single cell?

Thanks!
Your friendly Internet Simple_DirectorateAdminSupport

 

[PeteWright]

Hi @SimpleDAS

I am not sure what you are trying to achieve.
Could you give some examples please?


You could simply record a Macro and see how the code looks like.
If your task can be done "by hand", it surely can be done with a VBA macro.

To record a macro open the Developer ribbon tab and click on "Record Macro"
Then just "do" whatever you need to (like selecting and filling in a cell or move cell values to different cells) and when you're done click on "Stop Recording".

The recorded VBA code will appear in the VBA Editor (which you can open with [Alt]+[F11].

 

[SimpleDAS]

Hi @SimpleDAS

I am not sure what you are trying to achieve.
Could you give some examples please?


You could simply record a Macro and see how the code looks like.
If your task can be done "by hand", it surely can be done with a VBA macro.

To record a macro open the Developer ribbon tab and click on "Record Macro"
Then just "do" whatever you need to (like selecting and filling in a cell or move cell values to different cells) and when you're done click on "Stop Recording".

The recorded VBA code will appear in the VBA Editor (which you can open with [Alt]+[F11].

Hi there, thanks for this idea, but I don't know if this would work because I want the data to be moved only if the date in question is past a certain parameter (ie: more than a year ago)

So I have a list of training:

And I would like the macro to check the date in column C, and if it is a year or older than today, I would like the data in the relevant row (ie A2 to C2) to be moved (if possible) to a different worksheet.

Hopefully this makes a little more sense now?
Simple_DAS

N.B:
The above image is a test spreadsheet I have made purely to try the macro, it is not a wholly accurate representation of the sheet it will be added to

 

[SimpleDAS]

Thanks Rich! This helped me get to where I could figure it out!
If anyone wants to know, this ended up being the solution.

Sub CopyDataToAnotherSheet()

    Dim sourceSheet As Worksheet
    Dim destSheet As Worksheet
     
    Set sourceSheet = ThisWorkbook.Sheets("CIPs")
    Set targetSheet = ThisWorkbook.Sheets("CIP 2024")
   
    Dim sourceBTR2 As Range, targetBTR2 As Range
    Dim BTR2i As Integer
   
    For BTR2i = 107 To 122
       
        If sourceSheet.Cells(BTR2i, 32).Value > 0 And sourceSheet.Cells(BTR2i, 42).Value > 0 Then
           Set sourceBTR2 = sourceSheet.Range(sourceSheet.Cells(23 + BTR2i - 107, 21), sourceSheet.Cells(23 + BTR2i - 107, 25))
           
           Set targetBTR2 = targetSheet.Cells(targetSheet.Rows.Count, 36).End(xlUp).Offset(1, 0)
           
            If IsEmpty(targetBTR2.Value) Then
                sourceBTR2.Copy
                targetBTR2.PasteSpecial Paste:=xlPasteValues
                Application.CutCopyMode = False
            Else
                Set targetBTR2 = targetSheet.Cells(targetSheet.Rows.Count, 36).End(xlUp).Offset(1, 0)
                sourceBTR2.Copy
                targetBTR2.PasteSpecial Paste:=xlPasteValues
                Application.CutCopyMode = False
            End If
        Else
            Exit For
        End If
    Next BTR2i
End Sub

I have also found this code from another query but don't know if it could be adapted to fit my needs