Excel Snapshot Data, particular date, different workbook

drewhx15

New Member
Joined
Jun 6, 2019
Messages
31
Hi,

I am struggling to figure this query out and hope you can help me!

I have a numeric figure that changes frequently. I need to be able to set something
in place that will do the following:

On workbook 1 a figure is input manually.

This needs to be recorded (snapshot taken) on a certain date I..e the first of the month. The figure captured is automatically put in a cell on workbook 2.

This will run on each month and populate the related cells on workbook 2 with the figure from workbook 1 on the date it's needed.

This figure will be locked in workbook 2 and not change again despite the figure in workbook 1 being amended.

I hope this makes sense it's been so difficult to try and find an answer. I'm new to this depth of excel.

Kind regards,
Drew
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Let me rephrase what you are saying:

  1. In workbook1 there is a cell which changes regularly through some process.
  2. Once a month you want to get this value from workbook1 and store it in a cell in workbook2
  3. Each month the value is stored in the next cell in workbook2 (assuming in a column)
 
Upvote 0
Copy the complete code below into a new macro module in the workbook1.

Then read all the comments and modify where instructed.
Code:
Option Explicit

'    In workbook1 there is a cell which changes regularly through some process.
'    Once a month you want to get this value from workbook1 and store it in a cell in workbook2
'    Each month the value is stored in the next cell in workbook2 (assuming in a column)

' >>>>>>> This macro to be placed in Workbook1 <<<<<<<<

Sub StoreValue()
'////////////////////////////////////////////////////////////////
'// This macro will open another workbook (name set in sWB2    //
'// below). Then it will copy the value from this workbook     //
'// into a specifoed cell in WB2.                              //
'// Modify the sheet names, workbook name and cell addresses   //
'// where the comments below instruct you to.                  //
'////////////////////////////////////////////////////////////////

    Dim lR As Long
    Dim rC As Range, rS As Range
    Dim wbWB1 As Workbook, wbWB2 As Workbook
    Dim wsWS1 As Worksheet, wsWS2 As Worksheet
    Dim sPath As String
    Const sWB2 As String = "Save.xlsm"  '<<<<< Enter the name of Workbook2 here <<<<<
    
    Set wbWB1 = ThisWorkbook
    Set wsWS1 = wbWB1.Sheets("Sheet1")  '<<<<< Enter the name of the worksheet in workbook1 where _
                                                the value is which needs to be stored
    Set rS = wsWS1.Range("A1")          '<<<<< Enter the cell address where this value is
    
    sPath = wbWB1.Path                  '<<<<< If the Workbook2 is in a different folder than _
                                               Workbook1, replace 'wbWB1.Path' with the path for WB2 <<<<<
    
    If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
    
    ' Check if Workbook2 is open
    On Error Resume Next
    Set wbWB2 = Workbooks(sWB2)
    
    If wbWB2 Is Nothing Then    'Workbook 2 is not open
        Set wbWB2 = Workbooks.Open(fileName:=sPath & sWB2)
    End If
    On Error GoTo 0
    
    If wbWB2 Is Nothing Then    'Error opening file
        MsgBox prompt:="File " & sPath & sWB2 & vbCrLf & " could not be opened. Please check.", _
               Buttons:=vbCritical & vbOKOnly, Title:="Error file not found"
        Exit Sub
    End If
    
    'WB2 is open. Get the next row in WB2
    
    Set wsWS2 = wbWB2.Sheets("Sheet1")  '<<<<< Enter name of sheet in workbook2 where values are to be stored <<<<<
    Set rC = wsWS2.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) '<<<<< Change "'"into the column letter where _
                                                                        this value will be stored
    'and Read the cell to copy into WB2
    rC.Value = rS.Value
    
    wbWB2.Save
    
    ' wbwb2.Close  '<<<<< remove comment ' to automatically close the Workbook2 after saving
    
    ' cleanup
    Set wbWB1 = Null
    Set wbWB2 = Null
    Set wsWS1 = Null
    Set wsWS2 = Null
    Set rS = Null: Set rC = Null
End Sub
 
Upvote 0
Hi,

Thank you for getting back to me on this, I will do as instructed.

I will let you know if I get it to work, once again thank you so much!

Drew
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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