I have an interesting question; I have got a cash reconciliation template that is done each month by our clients? This template is saved in a root directory... for example, C:\cashreconciliation\bus operator cash reconciliation template.xls
Now when this file is opened, the operator selects from the parameters sheet the name of the operator, month and then year. You can see this from the code below that is shown after my question 2, there is a button on this worksheet which saves a copy of this template and saves it to another file within the cash reconciliation folder and names it
Application.ActiveWorkbook.Path & "\Cash Reconciliation " & _ oprtr$ & " " & mnth$ & " " & yr$
In other words:
C:\cashreconciliation\Cash reconciliation bribie march 2007
This is a new file created.
Now my interesting question, I would like to do 2 things from these automatically created cash recs which contain data.
1) I have a separate ?Revenue report template? excel file similar to this cash rec template as described above. Saved in the same root as above.. that is, C:\cashreconciliation\Revenue report template. This also allows the user to select the operator, month and year from a drop down list. This new file is saved using as a new file name using a similar principal as above. This file is saved automatically in the same path as the cashrec
C:\cashreconciliation\Revenue report bribie march 2007.xls
Now I have these two files, I would like the revenue template file to automatically (or by a click of a button) retrieve certain cells in the cashrec ONLY IF THE OPERATOR AND THE MONTH AND THE YEAR OF BOTH OF THE FILES match..
For example.. lets say there is a cash reconciliation file name called:
Cash reconciliation bribie march 2007 (created from the template file named: bus operator cash reconciliation template.xls)
And another file name called:
Revenue report bribie march 2007 (created from the template file named: revenue report template.xls)
Now because the ?bribie march 2007 last three words? exist in these two separte files and they match I need some code that takes a cells value in the Cash reconciliation bribie march 2007 file in cell for example
C5 in sheet named ?cash summary? which equals $100 and transfers this value into F9 in sheet named ?submission? in the Revenue report bribie march 2007 file.
Other transfers for example would be:
C7 in sheet named ?cash summary? in the Cash reconciliation bribie march 2007 file and transfer them to F10 in sheet named ?submission? in the Revenue report bribie march 2007 file.
C9 in sheet named ?cash summary? in the Cash reconciliation bribie march 2007 file and transfer them to F11 in sheet named ?submission? in the Revenue report bribie march 2007 file.
C11 in sheet named ?cash summary? in the Cash reconciliation bribie march 2007 file and transfer them to F13 in sheet named ?submission? in the Revenue report bribie march 2007 file.
I would like a button on the Revenue report bribie march 2007 file to update all these fields ONLY if a corresponding Cash reconciliation bribie march 2007 file exists? there needs to be a match of the selected operator, and the month and the year for there to be all these above transfers.
I do hope you have a solution to this?
Well here is my Question 2)
A bit more tricky..
I am designing an analytical spreadsheet template which will be used as a management reporting tool. This is saved in the :
C:\cashreconciliation\management report template.xls
When opened this file allows the user to ONLY select the operator, AND year and NOT THE month from separate drop down lists. This new file is saved as a new file name using a similar principal as above. This file is saved automatically in the same path as the cashrec and for example the new file may be called the following depending on the parameters selected.
C:\cashreconciliation\management report bribie 2007.xls
Now what I would like this file to do is IF the files in the folder containing cash reconciliations contain the same operator, in this instance ?bribie? and the same year ?2007? .. for example say these files existed:
C:\cashreconciliation\Cash reconciliation bribie march 2007
C:\cashreconciliation\Cash reconciliation bribie april 2007
C:\cashreconciliation\Cash reconciliation bribie may 2007
C:\cashreconciliation\Cash reconciliation bribie june 2007
C:\cashreconciliation\Cash reconciliation bribie july 2007
Then I would like the file that is created: ?management report bribie 2007.xls? to find all these Months if the operator is the same and if the year is the same then do the following.
in cell A1 on sheet 1 on the ?management report bribie 2007.xls? file I would like it to go and find all cash reconciliation files where it matches ?BRIBIE? and ?2007??
then, from this I would like cell D4, in each of these C:\cashreconciliation\Cash reconciliation bribie xxxx 2007 to flow through to my ?management report bribie 2007 file and be identified by month.
For example in cell b5, I would like the ?management report bribie 2007? to say March 2007 and on c5 I want a code that will go and find that file named Cash reconciliation bribie march 2007, look in the summary sheet for this file and copy cell g5 and paste it in cell c5 on the ?management report bribie 2007?.
In this same workbook ?management report bribie 2007? in cell b6 I would like it to say April 2007 and on c6 I want a code that will go and find that file named Cash reconciliation bribie march 2007, look in the summary sheet for this file and copy cell g5 and paste it in cell c6 on the ?management report bribie 2007?.
Similarly for all the other months that exist for this operator and year..
I would all this function done by a click a button
CAN I PLEEEEEEESE GET SOME HELP
THANKYOU SOOO MUCH FOR YOUR EXPERTISE
Here is the code for question 1
Private Sub CommandButton1_Click()
Dim wbpath$, oprtr$, mnth$, yr$
Dim Sht As Worksheet
Dim sPath As String
' name new workbook according to file path to which original was saved and add Operator, Month, and Year to name
Application.ScreenUpdating = False
If Range("f24") = "-" Then
MsgBox "Operator name must be entered"
Exit Sub
End If
If Range("f26") = "-" Then
MsgBox "Month must be entered"
Exit Sub
End If
If Range("f27") = "-" Then
MsgBox "Year must be entered"
Exit Sub
End If
With Sheets("cover sheet")
oprtr$ = .Range("f24")
mnth$ = .Range("f26")
yr$ = .Range("f27")
End With
With Worksheets("Cover Sheet")
.Unprotect Password:="cbs"
Sheets("Cover Sheet").Shapes("CommandButton1").Delete
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> "Cover Sheet" Then Sht.Visible = True
Next
Application.Run "rename_sheets"
Application.Run "hidefirstlast"
'***************************
sPath = Application.ActiveWorkbook.Path & "\Cash Reconciliation " & _
oprtr$ & " " & mnth$ & " " & yr$
If Not FileExists(sPath) Then
Application.DisplayAlerts = False
ThisWorkbook.SaveAs sPath
Application.ScreenUpdating = True
Else
MsgBox "File exists"
End If
'***************************
If Range("f26") = "April" Then
Sheet36.Visible = False
Sheet1.Range("40:40").EntireRow.Hidden = True
End If
If Range("f26") = "June" Then
Sheet36.Visible = False
Sheet1.Range("40:40").EntireRow.Hidden = True
End If
If Range("f26") = "September" Then
Sheet36.Visible = False
Sheet1.Range("40:40").EntireRow.Hidden = True
End If
If Range("f26") = "November" Then
Sheet36.Visible = False
Sheet1.Range("40:40").EntireRow.Hidden = True
End If
If Range("f26") = "February" Then
Sheet36.Visible = False
Sheet35.Visible = False
Sheet1.Range("39:40").EntireRow.Hidden = True
End If
End With
End Sub
Function FileExists(Path As String) As Boolean
Dim sfile As String
sfile = Dir(Path, vbNormal)
FileExists = sfile <> ""
End Function