Thank you kindly for the reply.
As mentioned, I have two Workbooks: Cash Book and Tax Invoice & Statement. The Tax Invoice & Statement Workbook has two sheets - sheet 1 being the Tax Invoice & Statement Template and sheet 2 being the Register. Every time I generate an invoice(sheet 1), the basic details (Date, Customer, Invoice Number and Amount) are automatically saved to the Register by clicking on a button. When an invoice is paid, I enter the Date Paid and Amount Paid. So, once I complete these entries, I would like to transfer the data [Date Paid: column E, Customer: column B, amount Paid: column F (in this order to match the same details in the Income section of each month)] to the Cash Book but specifically to the appropriate month; e.g. any payments made in July go to the July Income in the Cash Book, any payments made in August go to the August Income and so on. There may be one payment in a month or there may be many payments in a month. So I suppose part of the trick would be for the code to recognise the correct month in the Cash Book based on the Date Paid from the Register.
I also need to keep all entries in the Register.
The code I have opens the Cash Book Workbook ok but the entries are all over the place.
Would you prefer that I send you a copy of my two Workbooks as this macro seems somewhat tricky to get right.
Below is the code I have:-
Function IsWorkBookOpen(FileName As String)
Dim FF As Integer, ErrNum As Integer
On Error Resume Next 'Turn off error checking
FF = FreeFile() 'The inbuilt function gets a free file number.
Open FileName For Input Lock Read As #FF 'Try to open the file and lock it
Close FF 'Close the file
ErrNum = Error 'Capture the error number
On Error GoTo 0 'Turn on error checking
'Find which error happened
Select Case ErrNum
'File is not open
Case 0: IsWorkBookOpen = False
'Error for "Permission Denied."
'File already opened by another user
Case 70: IsWorkBookOpen = True
'Some other error occurred. Capture the error number for further action
Case Else: Error ErrNum
Private Sub CommandButton1_Click()
Product$ = Mid("E4", 4, 3)
info = IsWorkBookOpen("C:\Users\aaa\Cash Book Template.xlsx")
'Open the workbook if it is closed.
If info = False Then
Workbooks.Open FileName:="C:\Users\aaa\Cash Book Template.xlsx"
'Now in new Workbook. Need to count all worksheets in the opened Workbook.
Dim WS_Count As Integer
Dim I As Integer
'Set WS_Count equal to the number of worksheets in the active workbook.
WS_Count = ActiveWorkbook.Worksheets.Count
'Now need to loop through the worksheets.
'Begin the loop.
For I = 1 To WS_Count
'Now need to reference a sheet within the loop using a text function.
If Mid$(ActiveWorkbook.Worksheets(I).Name, 4, 3) = Product$ Then
'Find the first empty row in worksheet.
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
I think that the ranges may be the problem but I'm not sure how to get around this.
In the Invoice Register, I have formatted the Date Paid as a long version (e.g. 16 July 2014) so that I could use the MID text function for the code to recognise the month tabs in the Cash Book Template, but I don't think this is working either.
Apologies for the long winded post.