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

Posts
1206
Registration date
Thursday July 24, 2014
Status
Contributor
Last seen
December 7, 2018
- - Latest reply: vcoolio
Posts
1206
Registration date
Thursday July 24, 2014
Status
Contributor
Last seen
December 7, 2018
- Sep 9, 2014 at 06:12 AM
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
See more 

20/23 replies

Best answer
Posts
2440
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 13, 2018
2
Thank you
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

Say "Thank you" 2

A few words of thanks would be greatly appreciated. Add comment

CCM has helped 2743 users this month

Posts
2440
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 13, 2018
2
Thank you
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

Say "Thank you" 2

A few words of thanks would be greatly appreciated. Add comment

CCM has helped 2743 users this month

Posts
2440
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 13, 2018
1
Thank you
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.
Posts
2440
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 13, 2018
1
Thank you
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
Posts
1206
Registration date
Thursday July 24, 2014
Status
Contributor
Last seen
December 7, 2018
0
Thank you
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.
Posts
1206
Registration date
Thursday July 24, 2014
Status
Contributor
Last seen
December 7, 2018
0
Thank you
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.
Posts
1206
Registration date
Thursday July 24, 2014
Status
Contributor
Last seen
December 7, 2018
0
Thank you
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.
Posts
1206
Registration date
Thursday July 24, 2014
Status
Contributor
Last seen
December 7, 2018
0
Thank you
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.
TrowaD
Posts
2440
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 13, 2018
-
Hi Vcoolio,

Good to see you are experimenting with the ranges. When I know I have a totals row and want to find the first available row above it, I would use Range("C1").End(xlDown) if I have a two row header or Range("C100").End(xlUp) if row 100 is totals and I have one row header.
Another nice thing to know is that you can apply these actions with you keyboard by hitting the End button and then an arrow button to experiment and to see if the cursor end up where you want it to. It is even possible to use End twice. So Range("C"&rows.count).End(xlUp).End(xlUp) would also bypass the totals row.

Now for my question to you: Are the entries you want to move above each other (like row 20:22) or can there be rows in between (like row 20 and row 22)?

Since you originally liked option 2, it is also possible to create a message box to check the data before moving.
So you enter the Amount Paid, a message box pops up showing the data which will be moved. You check and when correct, hit OK and data will be moved. Or hit cancel and nothing happens so you can alter the data.

So much possibilities, so take some "frothies" before they freeze and think about it :).

Talk to you next week.

Cheers,
Trowa
Posts
1206
Registration date
Thursday July 24, 2014
Status
Contributor
Last seen
December 7, 2018
0
Thank you
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.
Posts
1206
Registration date
Thursday July 24, 2014
Status
Contributor
Last seen
December 7, 2018
0
Thank you
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.
Posts
1206
Registration date
Thursday July 24, 2014
Status
Contributor
Last seen
December 7, 2018
0
Thank you
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.
TrowaD
Posts
2440
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 13, 2018
-
Hi Vcoolio,

Not sure what you were trying to achieve, but by trial and error we learn the most.

In your 31 July post you said:
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?

Sure we can!

To implement the code below, right-click the tab of the sheet where you enter the Amount Paid etc. and select View Code. Paste the code in the big white field. So now it is not in a module but in a sheet. The difference is that now the code will run automatically whenever you confirm a value in the F column.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("F:F")) Is Nothing Then Exit Sub

Dim wb As Workbook
Dim wbCheck As Boolean
Dim R1, R2, R3, DestSh As String
Dim dataCheck As Variant

R1 = Target.Offset(0, -1)
R2 = Target.Offset(0, -4)
R3 = Target

dataCheck = MsgBox("The following data will be copied to the Cash Book:" & Chr(10) & "Date Paid: " & _
                    R1 & Chr(10) & "Customer: " & R2 & Chr(10) & "Amount Paid: " & R3, vbOKCancel, "Data Check")
                    
If dataCheck = vbCancel Then Exit Sub

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("A" & Rows.Count).End(xlUp).Offset(0, 1) = R2
Sheets(DestSh).Range("A" & Rows.Count).End(xlUp).Offset(0, 2) = R3
End Sub


You know where to find me with any questions you might have.

Best regards,
Trowa
Posts
1206
Registration date
Thursday July 24, 2014
Status
Contributor
Last seen
December 7, 2018
0
Thank you
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.
TrowaD
Posts
2440
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 13, 2018
-
Hi Vcoolio,

That would mean that the sheet can't be found.

What I'm guessing is that you have, for example, a date in September, so the code looks for a sheet named "Sep" and you might have called it "Sept".

So check the DestSh references (Jan, Feb etc..) in the code and make sure the sheets are named the same way (or alter the references in the code).

That must be it or else I will pull my hair out and I really like my hair!

Best regards,
Trowa
Posts
1206
Registration date
Thursday July 24, 2014
Status
Contributor
Last seen
December 7, 2018
0
Thank you
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.
TrowaD
Posts
2440
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 13, 2018
-
Hi Vcoolio,

After some "frothies" I decided to leave my hair alone, haha.

That fixed range could be the problem. I thought you entered the date in E4 and then entered you data, which would then be copied to the month tab equal to the date in E4.
So you enter 1-7-14 (or whatever format) in E4, then enter all the data that needs to go in the July tab.
Then you enter 1-8-14 in E4, then enter all the data that needs to go in the Augustus tab.
Etc..

Let me provide you with an option to bypass the fixed range of E4.
After you enter the Amount Paid the code will run asking you to which month to copy the data to.
Then the conformation message box will appear letting you know which data will be copied to which sheet.

Downside to this method is that if you enter the Amount Paid like 10 times you will have enter the month 10 times as well (you could copy the month and just hit Ctrl+v, but still).

Think about it and let me now.

Best regards,
Trowa
Posts
1206
Registration date
Thursday July 24, 2014
Status
Contributor
Last seen
December 7, 2018
0
Thank you
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.
Posts
1206
Registration date
Thursday July 24, 2014
Status
Contributor
Last seen
December 7, 2018
0
Thank you
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.
Posts
1206
Registration date
Thursday July 24, 2014
Status
Contributor
Last seen
December 7, 2018
0
Thank you
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.
Posts
2440
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 13, 2018
0
Thank you
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

Posts
1206
Registration date
Thursday July 24, 2014
Status
Contributor
Last seen
December 7, 2018
0
Thank you
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.
TrowaD
Posts
2440
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 13, 2018
-
It has been a blast helping you out.

All the best to you my friend!
Posts
1206
Registration date
Thursday July 24, 2014
Status
Contributor
Last seen
December 7, 2018
0
Thank you
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.
Posts
1206
Registration date
Thursday July 24, 2014
Status
Contributor
Last seen
December 7, 2018
0
Thank you
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.
1 2 Next