Help me figure out this macro please.

billybob333

New Member
Joined
Nov 19, 2005
Messages
5
Im trying to create a macro but truthfully I have only recorded macros, and am unsure about creating one. I just cant seem to figure out how to do this.

I will give a simplified version of what I am hoping to do.

My excel file has 2 worksheets. sheet 1 has a $ value in cell A1. This $ value changes frequently as it is based upon other cells.

I am hoping to take a "snapshot" of this cell either when the file is opened or closed. I would like this snapshot to be copied to sheet 2 with the date that the "snapshot" was taken.

So on sheet 2 I would like A1 to be the date, B1 the value copied from sheet 1, A1. Then the next time I open the file I would want the same thing to happen. On sheet 2 I would like A2 to be the date, and B2 to be the value copied from sheet 1, cell A1. And so on using columns A and B on sheet 2 to hold the data.

I hope I explained what I am trying to do. Thanks for any help!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Welcome to MrExcel Board!

If you can live with the duplicate entries this will make, try:

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    test1b
    ActiveWorkbook.Save
End Sub

Private Sub Workbook_Open()
    test1b
End Sub

in the thisWorkbook module, and:

Code:
Sub test1b()

    Worksheets(1).Range("A1").Copy
    Worksheets(2).Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
    Worksheets(2).Range("A" & Rows.Count).End(xlUp).Offset(, 1).Value = Date
    Application.CutCopyMode = False

End Sub

in a standard module.

  • Press Alt-F11 to open the VBE.
    Press Control-R to open the Project Explorer. (May be open already)
    Click "Microsoft Excel Objects" for the file you're working on (should expand the list of the ThisWorkbook module and any sheet modules.)

    Select Insert, Module from the drop down menus for standard modules.
    For ThisWorkbook and sheet modules:
    Double-click the ThisWorkbook module or the sheet this code applies to.
    Select Workbook, Before Close, and Open from the dropdowns.

    Put your code in the right-hand window.
    Press Alt-Q to close the VBE and return to Excel.

Hope that helps!
 
Upvote 0
Code:
Private Sub Workbook_Open()
    InsertChangeInfo ("ON OPEN")
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    InsertChangeInfo ("ON CLOSE")
End Sub

Private Sub InsertChangeInfo(sEvent)
With Sheets("Sheet2")

    .Rows("2:2").Insert Shift:=xlDown
    
    'VALUE
    .Range("A2").Value = Sheets("Sheet1").Range("A1").Value
    
    'DATE
     .Range("B2").Value = Date
     
     'TIME
     .Range("C2").Value = Time
     
     'PERSON
     .Range("D2").Value = Environ("username")
     
     'EVENT
     .Range("E2").Value = sEvent
    
End With

End Sub
 
Upvote 0
Hi Taz,
Why the double entries?
Either one of the Open or Before Close events should do what's needed.

Only thing I'd change is to use a regular paste to carry over any formatting, etc. (to get a true "snapshot") and then convert sheet2 from the formula to a value.
Such as:
Code:
Sub test1b()

Worksheets(1).Range("A1").Copy _
Worksheets(2).Range("A" & Rows.Count).End(xlUp)(2, 1)
With Worksheets(2).Range("A" & Rows.Count).End(xlUp)
    .Value = .Value
    .Offset(, 1).Value = Date
End With
Application.CutCopyMode = False

End Sub
Hope it helps.
Dan
 
Upvote 0
Hello HalfAce ... you asked ...

Why the double entries?

If many people are using the sheet you may want to know for sure what session is or is not responsible for a change . If the user that made the change did not have macro's enabled then the next person who enables macro's would be blamed for a change , if both Open and Closed are not enabled.

Another idea would be to have the cells "changeEvent" be what triggers the log entry. Though again this would fail if the user had not enabled the macro's.
 
Upvote 0
HalfAce said:
Hi Taz,
Why the double entries?
Either one of the Open or Before Close events should do what's needed.

Just because that's what the OP specified. Perhaps I misunderstood that requirement.

HalfAce said:
Only thing I'd change is to use a regular paste to carry over any formatting, etc. (to get a true "snapshot") and then convert sheet2 from the formula to a value.

Hope it helps.
Dan

Yeah.... Perhaps something else could be used (depending on the XL version); xlPasteValuesAndFormats maybe (not sure if I have that right...this PC has XL2k).
 
Upvote 0
Wow - Thanks for all the quick and detailed responses! Ill try it all out today. Sorry if I was confusing in my OP. I was trying to say I could have this run when the program was opened or closed, that it didnt matter which one. I dont mind dulicate entries for a date, although Id prefer just the last entry was retained for each date if possible.

Thanks again.
 
Upvote 0
Thanks guys - I tried both methods and they both do a great job. One more question for you. I probably should have asked this first but didnt think about it.

How could I modify the code so that a column change happens each month?

So same as above, on sheet 2 I would want the date in A1, value in B1. Tomorrow date in A2, value in B2. Then next month when i open the file, date in C1, value in C2. Im not sure if Im explaining well, but basically 24 columns of data, 2 pertaining to each month of the year? Somehow tell the macro that if the month is november, use A and B. If the month is December, use C and D.

Thanks again for any help - i very much appreciate it.
 
Upvote 0
Does something like this do it for you?
Code:
Sub Demo()
Dim LstNtryDateRw As Long
Dim LstNtryDateCol As Integer
Dim ThisMnth As String

LstNtryDateCol = Worksheets(2).Cells(2, Columns.Count).End(xlToLeft).Column
LstNtryDateRw = Worksheets(2).Cells(Rows.Count, LstNtryDateCol).End(xlUp).Row
ThisMnth = Month(Worksheets(2).Cells(LstNtryDateRw, LstNtryDateCol).Value)

If Month(Date) = ThisMnth Then
    Worksheets(1).Range("A1").Copy _
    Worksheets(2).Cells(LstNtryDateRw, LstNtryDateCol - 1)(2, 1)
    With Worksheets(2).Cells(LstNtryDateRw, LstNtryDateCol - 1)(2, 1)
        .Value = .Value
        .Offset(, 1).Value = Date
    End With
Else
    Worksheets(1).Range("A1").Copy _
    Worksheets(2).Cells(Rows.Count, LstNtryDateCol + 1).End(xlUp)(2, 1)
    
    With Worksheets(2).Cells(Rows.Count, LstNtryDateCol + 1).End(xlUp)
        .Value = .Value
        .Offset(, 1).Value = Date
    End With
End If

Application.CutCopyMode = False

End Sub

Hope it helps.

And yeah Taz, I can see now how we came up with 2 different interpretations of using the open and beforeclose events. Funny how 2 people can look at the same thing and get 2 different meanings.

Nimrod makes a good point about the macros being enabled/disabled. Bears looking at. :wink:
 
Upvote 0
Hi - back again. I got the file working OK using Nimrods method above. I wasnt able to get the monthly part to work HalfAce - Ive been playing with the code but am not successful. I have no experience with VB (or programming for that matter) so I am really unsure what I am doing.

This is the code I have working so far (using Nimrods method).

Code:
Sub InsertChangeInfo(sEvent)
With Sheets("Daily Results")

    .Rows("3:3").Insert Shift:=xlDown
    
    'DATE
    .Range("A3").Value = Date
    
    'VALUE
    .Range("B3").Value = Sheets("Main Page").Range("F2").Value
        
    'TIME
    .Range("D3").Value = Time
     
     
End With

End Sub

I have this running each time on close of the program. I have 3 questions if anyone is able to help?

1 - the main thing Id like to fix is the duplicate entries that are created if the program is closed on the same date more then once. I need the line that says ".Rows("3:3").Insert Shift:=xlDown" to only run if todays date is different then the last date entered (which would be in cell A3).

Something like
Code:
If todays date is not equal to the date in A3 then

      .Rows("3:3").Insert Shift:=xlDown

else
       stay on the same row

endif

Is this possible?

2 - Is it possible to run this macro when I press a button or hit a key combination (as well as run on close)? This would only be something Id like to do if I can get #1 figured out first, so it would overwrite the current dates entry.

3 - Im still interested in having it start over again with a new month if possible. For example June would take columns A-D, then on July 1st it would know to jump over and use E-H, etc.

Thanks so much for your help with this. The item I am most interested in figuring out is #1. Any help would be greatly appreciated.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top