Dear All
You have all been very kind with previous problems, and I have another one that I have been trying to sort for a long time without success. Basically I have a procedure which concatonates data on three workbooks and adds the data to a reference document. My problem is that on the first working day of every month the procedure falls over and will not run (Only on this day), now the reason for this is that the reference document is filled by month and the base documents are by day (Dealing Reports from each dealer to a master list of all deals in a given month). On the morning of the first working day of the month the procedure is run it falls over saying that it cannot find the reference document.
I would be grateful if anyone can tell me if it is possible to do the following and how to do it
At the moment I have to manually set the previous months data when the macro error appears please see below
strCurMonth = Format(Date, "mmmm")
varHols = Worksheets("Bank Holidays").Range("a1:a50")
dteWsData = workday(Date, -1, varHols)
strAlldeals = "Data:Dealing instructions:All Deals:" & Year(Date) & ":"
strAlldealsMth = "All Deals " & Format(Date, "mm-yy")
'strAlldeals = "All Deals 04-05"
strOpenAllDeals = strAlldeals & strAlldealsMth
Workbooks.Open FileName:=strOpenAllDeals, ReadOnly:=False
Set objAllDeals = Worksheets(strCurMonth & " deals")
'Set objAllDeals = Worksheets("April deals")What I would like to know if it is possible to indicate if today is the first workday of the month then
strPreMonth = Format(Date, [Previous Month]"mmmm")
varHols = Worksheets("Bank Holidays").Range("a1:a50")
dteWsData = workday(Date, -1, varHols)
strAlldeals = "Data:Dealing instructions:All Deals:" & Year(Date) & ":"
strAllPdealsMth = "All Deals " & Format(Date, [Previous Month]"mm-yy")
strOpenAllDeals = strAlldeals & strAllPdealsMth
Workbooks.Open FileName:=strOpenAllDeals, ReadOnly:=False
Set objAllDeals = Worksheets(strPreMonth & " deals")I hope this makes sense and I would be very grateful of help or advice anyone can offer
Kindest Regards