Specific data from one worbook to another (Excel 2010).Solved/Closed

Question

Hello there,

 I have two workbooks, one is an accounting spread sheet (named Cash Book) and the other has an Invoice Template (sheet 1) and Invoice Register (sheet 2) simply named Tax Invoice & Statement Template.

 The Cash Book has 14 tabs (Totals, 12 tabs for each month of the year and a Summary). Each month has a separate Income and Expenses section. From the Invoice Register (sheet 2) in the second workbook, I would like to extract the following data: Date Paid, Customer and Amount Paid and send this information to the Cash Book but to the relevant month e.g. any amounts paid during April go into the April income, amounts paid in May go into May income etc.(with the Date Paid and Customer details of course). There may be only one payment during a month or there may be a dozen payments in a month so a "NextRow" function would obviously be needed.
I have created a macro that almost works but just doesn't send the data to the correct month or row but, for some reason, fills in the very last row in the Totals tab.
Perhaps this is just not possible with VBA and I could just be "chasing my tail".

Does anyone have any ideas?


 Cheers,
 vcoolio

TrowaD · Accepted Answer

Hi Vcoolio,

Seems like your code needs a small adjustment.

Why don't you post your code in your next post so we can advise.

Make sure it's clear for us which data needs to go where.

Best regards,
Trowa

vcoolio · Answer

Hello TrowaD,

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
 'No error
 '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
 End Select
 End Function

Private Sub CommandButton1_Click()
 Product$ = Mid("E4", 4, 3)
 Range("E4").Copy
 Range("B4").Copy
 Range("F4").Copy

Dim info
 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"
End If
 '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
 Worksheets(I).Activate
End If
'Find the first empty row in worksheet.
 erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
 ActiveSheet.Paste Destination:=Worksheets(I).Rows(erow)
 ActiveWorkbook.Close
 Next I
End Sub

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.

Kind regards,
vcoolio.

TrowaD · Answer

Hi Vcoolio,

Ok, after looking at your code I decided it would be easier to create one myself.

Just to be sure:
The sheet names in Cash Book are like "Jan", "Feb", "Mar" etc..
The destination range in Cash Book are column A:C.

Try it out and let us know your thoughts on it.

Private Sub CommandButton1_Click()
Dim wb As Workbook
Dim wbCheck As Boolean
Dim R1, R2, R3, DestSh As String

R1 = Range("E4")
R2 = Range("B4")
R3 = Range("F4")

DestSh = Month(Range("E4"))

If DestSh = 1 Then
    DestSh = "Jan"
ElseIf DestSh = 2 Then
    DestSh = "Feb"
ElseIf DestSh = 3 Then
    DestSh = "Mar"
ElseIf DestSh = 4 Then
    DestSh = "Apr"
ElseIf DestSh = 5 Then
    DestSh = "May"
ElseIf DestSh = 6 Then
    DestSh = "Jun"
ElseIf DestSh = 7 Then
    DestSh = "Jul"
ElseIf DestSh = 8 Then
    DestSh = "Aug"
ElseIf DestSh = 9 Then
    DestSh = "Sep"
ElseIf DestSh = 10 Then
    DestSh = "Oct"
ElseIf DestSh = 11 Then
    DestSh = "Nov"
ElseIf DestSh = 12 Then
    DestSh = "Dec"
End If

For Each wb In Workbooks
    If wb.Name = "Cash Book Template.xlsx" Then wbCheck = True
Next wb

If wbCheck = False Then
    Workbooks.Open Filename:="C:\Users\aaa\Cash Book Template.xlsx"
End If

Workbooks("Cash Book Template.xlsx").Activate

Sheets(DestSh).Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = R1
Sheets(DestSh).Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = R2
Sheets(DestSh).Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = R3
    
End Sub 
Best regards,
Trowa
 
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.

vcoolio · Answer

Hello Trowa,

Almost but not quite!

Your code is transferring Date Paid and Customer Name to the correct row in the specific month in the Cash Book Workbook but the Amount Paid is being placed below the Totals row of the specific month and not on the same row as the Date and Customer.

Also, the code is only "harvesting" the first row of details from the Register in the Tax Invoice & Statement Workbook and not any below the first row. So whichever details are on the first row are the only details transferred to the Cash Book (e.g.: if the date is say 21/07/2014, then details for this date are transferred to the July tab in the Cash Book but this is all).

I tried playing a little with the code, specifically the Ranges but with no luck. I'm still scratching my head!

I suppose that I could just do the data entry the "old fashioned way" and just use a split screen view with the two Work Books and paste & copy.

Would you like me to send a copy of the Work Books to you so that you can see what I mean and play with them yourself?

Your efforts in helping me are greatly appreciated (and should be worth a few "frothies"!).

Kind regards,
vcoolio.

TrowaD · Answer

Hi Vcoolio,

I'll be damned if I let you go back to the "old fashioned way", haha. Especially with the foresight of some "frothies"!

You totals row is preventing the code to find the first available row, since it checks from the bottom up. To bypass this, we will look at the A column to determine the first available row.

Change:
Sheets(DestSh).Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = R1
Sheets(DestSh).Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = R2
Sheets(DestSh).Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = R3

into:
Sheets(DestSh).Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = R1
Sheets(DestSh).Range("A" & Rows.Count).End(xlUp).Offset(0, 1) = R2
Sheets(DestSh).Range("A" & Rows.Count).End(xlUp).Offset(0, 2) = R3

For the second part, I focussed on your code which used fixed ranges, forgetting your explanation.
We can go several ways with this (to determine which data row to copy), let me know which you like best or maybe have some other thoughts:
1) Select the appropriate row (or cell in row) before clicking the button.
2) Let the code run automatically after entering the "Amount Paid".
3) After clicking the button (or maybe after leaving the sheet), use the last data entry (last row used).

Uploading your workbooks doesn't seem necessary to me, other then determining the last row used, which should be solved now.

Best regards,
Trowa

vcoolio · Answer

Hello Trowa,

That works better!

Your explanation for the Totals row preventing the code from finding the first available row now makes sense to me (as now does xlUp) because last night I tried this:-

Offset(-97,0) for range "C" and the Amount went to the top but, obviously, this is not a functional method.

You learn something every day!

As for the second part, I think that option (2) may be the best method. Let me know your thoughts.

The "frothies" are getting colder!

Many thanks again.

Cheers,
vcoolio.

vcoolio · Answer

Hello Trowa,

Further to my last post, and in relation to your option(2), I was thinking that perhaps once the "Amount Paid" entries are done, whether there is one entry or multiple entries for a day or a month, would it be possible to high-light just these "Amount Paid" entries together and then click on a button to transfer the data to the Cash Book? This would allow me to check all the amounts entered for errors or omissions prior to transferring. This is more akin to your option (1) but I think it could work really well. What do you think?

Kind regards,
vcoolio.

vcoolio · Answer

Hello Trowa,

This has been a real tutorial for me! You've made it all so much simpler to understand VBA : your explanation of xlUp/Down, the Boolean in the code (which is a lot tidier and simpler than my original Function code), the code you used to identify the different months rather than a loop - wonderful stuff, thank you.

Just think, I'm going through this whole process to streamline my whole accounting procedure just to keep the Tax Man happy! But it has been fun.

All the entries that are to be transferred to the Cash Book are sequential, i.e. every time I generate an invoice, the basic details are saved into the Register (on clicking a button) row by row.

I really like your idea of a message box and think that this would be an ideal way of checking the entries prior to transferring them. Can we set this up?

Thanks so much again.

Cheers,
vcoolio.

vcoolio · Answer

Hello again Trowa,

Since my last post, I have been trying all sorts of ways to try and have the code transfer all the rows of data from the Register to the appropriate month in the Cash Book at once. At the moment it only transfers the first row. I can get the other rows to transfer by changing the fixed ranges (say from "E4" to "E5" etc.) but obviously changing the ranges every time to transfer data is not an ideal or efficient method. When I tried a full range (e.g. "E4:E10") a "type mismatch" error message kept coming up. I'm onto something here and the answer is probably staring me in the face but I just can't see it! Please educate me further!

Cheers,
vcoolio.

vcoolio · Answer

Hello Trowa,

Well, I thought that I had done it! But no :-(

To get each row of data to transfer from the Register to the Cash book, I created a macro for each row (the macro you created above with a different cell reference) - just four rows for now - and assigned a button with that macro to each row. That worked but was a very cumbersome exercise and obviously not a very practical time saving exercise. So, I changed to one button and assigned each macro to that. As the "Assign Macro" window opened (on right clicking on the button), I clicked on the macro for a particular row, assigned it, and that worked also. Again, not a very practical method. So then I created a macro to run all macros but that would only transfer the very first row of data from the Register: back to square one...arrrrrrghhh!!
But it has still been fun!

Any ideas Trowa?

Cheers,
vcoolio.

vcoolio · Answer

Hello Trowa,

I'm getting Run time error '9': Subscript out of range.
The debug lights up the following row:

Sheets(DestSh).Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = R1

The message box works well but the above error keeps coming up on making the data entry.

Let me know your thoughts.

Cheers,
vcoolio.

vcoolio · Answer

Hello Trowa,

I thought that may have been the problem so I made sure that the months in the code were spelt exactly as per my sheet tabs (as there are slight differences). It then worked but all the entries went into July and not their designated months!

On hovering the  cursor over the  last three rows of code, I noticed that the (DestSh) on each line showed DestSh = "July". Could the fixed range DestSh = Month(Range("E4")) be the problem?

I want you to keep your hair also!

I think that you are really earning those "frothies"!!

Let me know what you think.

Cheers,
vcoolio.

vcoolio · Answer

Hello Trowa,

So Excel can drive a man to drink!

Aha! I see now what you have done. You were thinking that all the entries, regardless of month, would go into the first row in the Register. I tried this and it worked beautifully: entered a July payment and it went to the correct month tab in the Cash Book, entered an August payment and it went to the correct month tab in the Cash Book. I then made a couple of fictitious entries for September and October and it worked again. The only down side with this is that, because of the fixed range (E4) I had to clear the previous entry and move the next one up to the top row.

Perhaps we could leave the macro as it is and I could just change the name of the Register to say "Data Entry" then after entering the data to be transferred to the Cash Book I could clear and save the entry to another sheet and name it "Register" where the saved entries could live forever after as I do need to keep a record of each entry. This would mean another macro to "save and clear" and at the same time move the next entry up to the top row with any other entries below moving up a row at the same time.

Would this just be complicating matters unnecessarily? Could the range (E4) perhaps be altered to allow for a number of rows (say E4:E50)? 

Crack another frothie and let me know what you think.

Many thanks,

Cheers,
vcoolio.

vcoolio · Answer

Hello again Trowa,

Since my last post, I've been doing some experimenting with the idea I had about creating the extra sheet and I've surprised myself because it works!

This is the code I have created:

Sub SaveToSheet3()
    Dim WS2 As Worksheet
    Dim WS3 As Worksheet
    Dim rng As Range
    Set WS2 = Worksheets("Register")
    Set WS3 = Worksheets("Sheet3")
    'Work out which row is the next row
    NextRow = WS3.Cells(Rows.Count, 1).End(xlUp).Row + 1
    'Write the important values to Sheet3
    WS3.Cells(NextRow, 1).Resize(1, 10).Value = Array(WS2.Range("A4"), WS2.Range("B4"), WS2.Range("C4"), WS2.Range("D4"), _
    WS2.Range("E4"), WS2.Range("F4"), WS2.Range("G4"), WS2.Range("H4"), WS2.Range("I4"), WS2.Range("J4"))
    Range("A4:J4").Select
    Selection.Delete Shift:=xlUp
End Sub

I've not named the sheet yet, hence Sheet3.

So, what happens with this is that the details in the Register (from the first row) are transferred to the Cash Book (thanks to your brilliant code) then, by clicking on a button, my above code transfers the first row of details (those that have already been transferred to the Cash Book) from the Register to Sheet3 and the rows below all move up one ready to be transferred in the same manner. So the first row in the Register in effect becomes the "transfer row" due to the fixed range (E4).
The only thing that seems to be wrong is that when I transfer the data to Sheet 3, the debug lights up this row in your code:

R1 = Target.Offset(0, -1)

 and Run Time error 1004 (Application defined or Object defined error) comes up. Why I don't know because everything works OK. Perhaps you could enlighten me on this.

So, my Tax Invoice & Statement Template Work Book will have three Work Sheets (but only if you think it is worthwhile):-

Sheet 1: Tax Invoice & Statement Template.
Sheet 2: Data Entry (formerly "Register").
Sheet 3: Register (where all the invoice details will live forever!).

I would dearly appreciate your thoughts on this. 

(BTW, I'm running out of hair!!).

Cheers,
vcoolio.

TrowaD · Answer

Hi Vcoolio,

In the interest of not turning into bald men when we don't have to, let's keep this "simple"!

First the error.
It happens because you delete a row moving all cells up. This means that more then one cell changes in column F and Excel doesn't know which cell to choose. 

But to be honest I feel a bit silly. I thought E4 would have 1-8-14 like a general date and then the next rows would be specific dates in Augustus. I guess the word fixed screwed my head a little (or the accumulation of "frothies", haha).
The easiest way by far is to make it dynamic, no extra sheets, basically make every row function like row 4.

To do this change the 18th code line:
DestSh = Month(Range("E4"))
into:
DestSh = Month(R1)

Also nice to see you are getting the hang of VBA! I remember the joy making one of my first codes work.

Best regards,
Trowa

vcoolio · Answer

Hello Trowa,

Mate, you're a bloody genius!! (not that everyone is perfect !) ;-)

It all works perfectly now. I'll transfer all the coding to my actual Work Books now as I set up a couple of "dummy" ones to test everything - just in case.

As I sit here slurping on my espresso (too early in the afternoon for a frothie) admiring your work, I'm thinking: "you're good, you're bloody good". I'm over the moon! (I think that I need to get out a little more!).

Perhaps you should do a podcast on this one because I think that it is somewhat unique and that there would be lots of interested people out there with similar problems to solve.

I have a couple of more questions for you just to satisfy my curiosity:-

1) Can the Run Time error 1004 mentioned above be bypassed? 
2) Can xlUp/Down find empty rows in between occupied rows? For example, say rows 1 - 10 have data in them, rows 11 - 15 are empty for whatever reason and rows 16 - 20 have data in them. Can rows 11 - 15 be populated?

I ask these questions because I would like to keep the code I created above (and fine tune it) and combine it with any information that you can provide from question (2) for a possible future project - but not just now. I need to get out and "smell the roses" for a while. Excel is so addictive!

Let me know how I can get some frothies over to you.

Thanks a million for all the help that you have given me. As the Yankees would say: "You da man!"

I hope that I've been a good "student".

All the best to you.

Cheers,
vcoolio.

TrowaD · Answer

Hi Vcoolio,

Thanks for the many kind words!

I am part of Kioskea to see which Excel query's I can solve (to test my knowledge). There are a lot of issues I wouldn't come across myself.

Answering questions helps me to tell myself what I know (Teacher's knowledge; everything you explain to someone, you explain to yourself as well and repetition is a good way to keep the memory active), so ask away!

1) The error occurred because of a misconception. The code looks at column F for any changes made while we only used row 4 for data entry. So the best thing to do here is to make the code not look at column F but at the cell F4.
Change the 2nd code line:
If Intersect(Target, Columns("F:F")) Is Nothing Then Exit Sub
into:
If Intersect(Target, Range("F4")) Is Nothing Then Exit Sub

If however you want to keep the code as it is and you don't want the error to occur when cleaning up your sheet, then we add another condition to exit the code.
So this will be placed under the 2nd code line:
If Target.Cells.Count <> 1 Then Exit Sub

2) This must have slipped your mind (see my post from 31 july). From the example you provided we can go many ways to find the first available row:
Range("A1").End(xlDown).Offset(1, 0).Select
or
Range("A16").End(xlUp).Offset(1, 0).Select
or
Range("A" & Rows.Count).End(xlUp).End(xlUp).End(xlUp).Offset(1, 0).Select
or
If Range("A11") = vbNullString Then
    Range("A11").Select
Else
    Range("A10").End(xlDown).Offset(1, 0).Select
End If


You are a great "student", haha.

Let's continue to improve our Excel knowledge!

Best regards,
Trowa

vcoolio · Answer

Hello Trowa,

Thanks for that. Actually, I did try the triple End(xlUp) based on what you explained to me in an earlier post and it did work but something just didn't feel right. So, as the saying goes, "when in doubt, ask".

But now I have all the other options that you explained. Thanks again.

Since I started this thread, I have learned much about VBA thanks to your clear explanations and logic. I have read the code that you created many times over and I can now really understand what you have done and why and what all the syntax means. Obviously, there would be tonnes more to learn and the learning will probably never cease but that should do for now for my weary brain (I'm only a "Tradie" after all!).You're a good teacher, old fella!

I suppose that this thread can now be marked as solved. Do you do this or should I?
I can't see in my account settings where I can do this.

Live long and prosper.

Cheers, Trowa.
vcoolio.

vcoolio · Answer

Hello again Trowa.

Just an aside to all we discussed above, I was just wondering (actually, my wife is) if it is possible to just high-light a row of data in one sheet and transfer it to a second sheet (the same work book) by clicking on a command button (no check boxes this time). This has something to do with her job so I created a macro for her (thanks to you again!) which transfers the top row (row 2 in this case) using a fixed range (A2:G2) and moves all the following rows up by one so that the next one can be transferred when required and so on. However, as we know how women like to be difficult, she said that it won't necessarily be the top row that is always transferred but it will more likely be random rows. So, now I'm stuck (as the wife stands behind me, hands on hips, tapping her foot)! This random transfer of rows will leave gaps in the many rows of data so will the shift:=xlUp syntax still work within this macro?
The column range is A - G.

Any ideas, Trowa?

I'll probably have to buy you a brewery after all the help that you have given me!!

Cheers,
vcoolio.

vcoolio · Answer

Hello Trowa,

Since my last post (about my wife's question), I've since noticed that column A of her work sheet is for  case numbers which is just a client identity number. Could this be used as a target like we used column F in my macro?

She has confirmed that the transferred row of data needs to be deleted from sheet 1 and the rows then moved up.

Cheers,
vcoolio.

Specific data from one worbook to another (Excel 2010).

23 responses

Similar discussions

Subscribe To Our Newsletter!