Previous Topic Next Topic

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

Solved/Closed
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jul 24, 2014 at 07:10 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - 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
Related:

23 responses

Answer 21 / 23
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Sep 1, 2014 at 12:17 PM
Hi Vcoolio,

Sorry for the delay, have been pretty busy.

Now let's solve your wife's query ;).

See if this code helps: 
Sub RunMe()
Dim lRow As Long
Dim ClientID As String

Sheets("Sheet1").Select

lRow = Range("A" & Rows.Count).End(xlUp).Row
ClientID = InputBox("Please enter the Case number you wish to move")

For Each cell In Range("A1:A" & lRow)
    If cell = ClientID Then
        Range(Cells(cell.Row, "A"), Cells(cell.Row, "G")).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        Range(Cells(cell.Row, "A"), Cells(cell.Row, "G")).Delete Shift:=xlUp
    End If
Next cell

End Sub


Don't forget to check the sheet references of the source sheet (line 5) and destination sheet (line 12).

I really enjoyed the way you asked your question, I even felt your wife's pressure, haha.

Now go impress your wife with your Excel skills!

Best regards,
Trowa

PS. be busy the rest of the week as well, so I'll talk to you next week.
0
Answer 22 / 23
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Sep 3, 2014 at 05:32 AM
Hello Trowa,

Two out of two so far!!

I told you that you are a genius! Your code works wonderfully. My wife is fully aware of my Excel skills, that's why she is now in love with you!

No apologies needed for the late reply. I figured that you were tied up elsewhere.
I reckon that you have been over the border getting a "frothie" stall ready for Oktoberfest ;-)

BTW, after asking you the above questions (22nd August), I realised that I should probably have started a new thread. So I did and I think that I headed it "Transfer Data from one Worksheet to Another". There is a code there that I created and works well but I like yours far better. Perhaps you could delete that thread as we now have the answer here.

I do, however, have a question for you in regards the code I created in that other thread and it refers to the following line of code:-

ActiveCell.EntireRow.Copy WS1.Range("A" & Rows.Count).End(3)(2)

Is the section of the code, .End(3)(2), a 'Last' function referring to the last cell in the last column? Or, is it just equivalent to .End(xlUp).Offset(1, 0)?

I was just wondering if you could clarify that part of the code as I have received conflicting explanations on this so I would be very interested in your opinion.

Still learning!

Many thanks again.

Cheers,
vcoolio.
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Sep 8, 2014 at 11:53 AM
Hi Vcoolio,

Glad to see you are not one of those impatient guys!

Also good to see you created another thread, but I agree with you being it obsolete. Thread will be deleted.

Onto your question:
What about ".End(3)(2)"?

The 3 stands for the direction:
1 = left
2 = right
3 = up
4 = down

The 2 stands for the offset, but counts the last/first row/column as well.
So:
.End(3)(1,1)
equals:
.End(3)

It is basically a different/shorter way of using the combo End and Offset.
So:
.End(3)(2)
yields the same result as:
.End(xlUp).Offset(1, 0)

Personally I don't like these shorter ways of writing codes, because it will make the code harder to read.

Another example would be the use of ranges:
Range("A1")
can be replaced with:
[A1]

Next question:
You also asked how to keep the totals row in place.

1) You could instead of deleting a row, just be clearing it's contents by using .ClearContents and then Sort your data.

2) You could assign multiple variables to each cell in the totals row, then delete the totals row, run the rest of the code and finally placing the assigned variables back to the totals row.
Like for example:
Dim Total1, Total2 As Long

Total1 = Range("A100").Value
Total2 = Range("B100").Value

Range("A100:B100").ClearContents

REST OF CODE HERE

Range("A100") = Total1
Range("B100") = Total2

3) But I think the following method works best.
Range("A" & Rows.Count).End(xlUp).EntireRow.Cut Rows(100)

Until next time,
Trowa
0
Answer 23 / 23
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Sep 9, 2014 at 06:12 AM
Greetings Trowa,

There is no point in being impatient. "Excel challenged" dummies like me have to realise that people like you do this sort of thing as a free service, in your own spare time, to help solve other peoples' issues. So we are not in a position to make demands on you. I dread to think what we would be charged to have professionals sort these things out for us!!

Thanks for the explanation in regards .End(3)(2) and the subsequent examples and explanations. That's brilliant! Your explanations as to why certain codes are used and what they mean all the way through the thread have been easy to understand and have made my understanding of VBA much clearer. "Others" have simply told me this or that is all you need but they have never explained why.
You're a great teacher old fella!

Just an aside to the code you came up with for my wife, which works brilliantly, in relation to the input box (great idea BTW!). Following is an extract of our conversation about it:-

Wife: "Its called a matter number not a case number!"
Me: "Does it really matter what its called? It is just a number".
Wife: "Matter number is what it has always been called".
Me: "Yes Dear"
....type, type, type, type..........(I changed Case Number to Matter Number in the input box message!).

I'm going back to my tools of trade now, Trowa.

Thanks a bazillion for all your help.

I'll probably come up with a new project before long. So, until then, live long and prosper.

Cheers,
vcoolio.
0
Previous
  • 1
  • 2

Similar discussions

Unlock safe folder without losing any data
Mintu -
HelpiOS -

1 response
How To Fix Mint Mobile Data Not Working?
sachin327 -
joshitha -

2 responses