Copy From One Sheet to Another Sheet of Same Workbook

[KSunShine]

Hello,

After running a macro, I have information of sales from various locations in Worksheet. Now I need to copy range (B:M) of each location sale of this Worksheet to Current sheet, range (C:N).

The problem is each location sale in Current sheet occupied 37 rows and has special format. Is there any method as Vlookup, or macro can do the job automatically?
Thanks

KSunShine

[mohadin]

Hi
Try this code on a copy of your file

Please Login or Register  to view this content.

[KSunShine]

Thanks mohadin!
I'll test the code later, and will let you know.

[KSunShine]

Hi mohadin,

I tested the code, and it worked great in original Sample. However, as you know the rows listed on the "Worksheet" for each location is not always have same length; it all depends on the activities of the month.
As the result of rows changed, information displayed on "Current" sheet were wrong as you can see in my new attached updated file. Thanks for your time mohadin!

[mohadin]

All right its my mistake
Try this code instead and let me know

Please Login or Register  to view this content.

[KSunShine]

Hi mohadin,

The code is great with sample I sent.

However, based on my set up, "Current" sheet is where all locations listed as template even though no activities during the month. On the other hand, "Worksheet" is where activity locations displayed during the month. For testing, I crossed out some of locations from "Worksheet" then run your code, "Current" sheet still displayed information on these inactive locations. I also, got a message as "can't execute code in break mode".

I attached result of what I described above on Sample2 file that you can see. Thanks mohamin!

[jindon]

Try the attached

Please Login or Register  to view this content.

[KSunShine]

Hi Jindon,

The code work great even though, for testing, I added and removed some locations!

However, due to merged function of the code, the format on "Current" has new face. I do want the original format on "Current". Do you have magic wand? Thanks.

[jindon]

However, due to merged function of the code, the format on "Current" has new face. I do want the original format on "Current".

I need to see the workbook.

[KSunShine]

Hi jindon,

Attached is the Sample3 with "Current" sheet displays after running your code. Thanks.

Ksunshine

[jindon]

Do you just want to clear the data when Location in Current sheet is not in Worksheet?

I also don't understand the meaning of

However, due to merged function of the code, the format on "Current" has new face. I do want the original format on "Current".

[KSunShine]

Hi Jindon,

I still want all locations displayed on Current sheet even no activities during the month. Also, I want original format of Current sheet still the same
after running the code. However, merging function carried Worksheet format over the Current sheet. You will see my explanations on new upload file. Thanks!

[jindon]

OK, that's better and faster.

Please Login or Register  to view this content.

[KSunShine]

Hi Jindon,

Perfect, and perfect! Thanks so much Jindon!
May I have a big hug?

KSunShine.

[jindon]

You are welcome.

[KSunShine]

Hi Jindon,

I have one more sheet on Sample3 file, named BankSTMs. Information on this sheet needs to be transferred to "current" sheet same way your code transfer "Worksheet" to "Current" sheet.

I have been helped using formula by another gentlemen in this forum. However, the formula will interfere with new month record.

I tried your code with some adjustments but failed. I attached new complete Sample3(1) file. Please help if you have time. Thanks.

[jindon]

What is the correct result that you expect?

[KSunShine]

Hi Jindon,
I want to save each month record without changes. However, the formula works but it changes the result when new bankSTMs comes.

[jindon]

I just don't understand how "BankSTMs" get involved.
Col.U is showing the number all the way and your formula in Col.W showing #VALUE! all the way as well.

So, if you upload a workbook showing EXACT result that you want in "Current" (perhaps Col U), it may help to understand what you are trying to do.

Or is it your desired result?

[KSunShine]

Hi jindon,

It was my mistake when first trying to create workbook without thoroughly thinking the best result. Now, for convenience, I decided to take US RAW sheet to add into MRR workbook. As a result, I have to manually copy each individual account from US RAW sheet, Columns (B:C), then paste into CurrentMonth sheet, Columns (V:W) as attached file display.

I think your code would be better than using formula for this purpose. Thanks.
KSunShine

[jindon]

What I want to see is...

So, if you upload a workbook showing EXACT result that you want in "Current" (perhaps Col U), it may help to understand what you are trying to do.

[KSunShine]

Hi jindon,

Sorry, my mistake! I uploaded wrong file! It is CurrentMonth sheet in place of MRR Template. New file has full display in CurrentMonth sheet.

Numbers in Columns A, US RAW sheet, is account numbers of each individual and number of rows in this sheet is all depend on how many activities during the month. However, each individual account on CurrentMonth takes 37 rows. Each month, after preparing bank statement in US RAW, I copy and paste into CurrentMonth for each individual in Columns (V:W).

Based on your code, I think you use matching account numbers in column U, CurrentMonth to account numbers in column A, US RAW. Am I right? I attached new file and hope it is clear for your review. Thanks.

[jindon]

Assuming "Mrr1.xlsm", data, is in the same folder.
Read the data directly from the closed workbook for col.U:W on "Current" sheet, so no use of formula.

Please Login or Register  to view this content.

[KSunShine]

Hi jindon,
I saved MRR1.xlsm which has two sheets US RAW and CurrentMonth on desktop. I wonder what I need to replace the phrase ThisWorkbook.Path in the code. MRR1 is only workbook involve to this processing. Could you please advise? Thanks.

[jindon]

If it is on Desk Top then
change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

If it is in other specific foler, change the red part to something like

Please Login or Register  to view this content.

[KSunShine]

Thanks you so much Jindon! it takes time for me to learn all this instructions. I will let you know my progress, teacher.

[KSunShine]

Hi Jindon,
It is obvious that my explanations of what I need is not clear and it confused you. It just seem like what I need is exactly the same as what I asked you on previous sample, and the code you provided worked perfectly.

Only one thing that I added another sheet, US RAW to Sample3 file. And, data on US RAW needs to be transferred to CurrentMonth sheet the same manner as Worksheet to CurrentMonth but in different Columns.

You used this code to successfully transfer data from "Worksheet" to "Current"
as shown on the left side and on attached "Sample3 (1).xlsm I previous provided.

Dim myAreas As Areas, r As Range, e, x, dic As Object
Application.ScreenUpdating = False
Set dic = CreateObject("Scripting.Dictionary")
Set myAreas = Sheets("worksheet").Columns(2).SpecialCells(2, 1).Areas
For Each r In myAreas
dic(r(, 0).Value) = r.Resize(, 12).Value
Next
With Sheets("current")
For Each e In dic
x = Application.Match(e, .Columns(2), 0)
If IsNumeric(x) Then
With .Cells(x, 2).MergeArea
.Columns(2).Resize(, 12).ClearContents
.Cells(1, 2).Resize(UBound(dic(e), 1), 12).Value = dic(e)
End With
End If
Next
.Select
End With
Application.ScreenUpdating = True
End Sub

I just added another US RAW sheet to this workbook
And in same manner, can you create another code to transfer data from US RAW to Current? Thanks Jindon!
AS one of what I did below:
I copied range (A4:C6) from US RAW then pasted to Current, Range(V17:W19)

[jindon]

Do you mean?

Please Login or Register  to view this content.

[KSunShine]

Hi jindon,

I saved your code in TEST macro and run:
Under Current sheet, it missed catching Burton and Kings accounts. Also, it collected Hunter account wrongly. For testing, I added two more accounts as Brandon and Holly but the code didn't catch it.
Is it possible that please do not adjusting column U under Current sheet. I want this column display as its original. I uploaded the result of this code for your review.

I understand that helping me is time consuming, please take your time. I appreciate it. Thanks.

[jindon]

Updates Col.C:N only when Worksheet has same Location/
Updates Col.V:W only when US Raw has same Location.

Please Login or Register  to view this content.

[KSunShine]

Hi jindon,

It worked perfectly! It is early morning Friday in Japan right now. Have a nice weekend jindon! I appreciate your precious time.
KSunShine.

[KSunShine]

Hi jindon,
I lengthen activities on accounts and run the code again but information on the last three accounts on Current sheet were collected incorrectly. You can see highlighted on the last three under uploaded Current sheet.

[jindon]

US Raw must have complete blank row(s) between the Locations as it was.

Inset a blank row at row 231 in US Raw.

[KSunShine]

Oh! Thanks Master!!!

[KSunShine]

One last question Jindon:
The "Set myAreas = Sheets("worksheet").Columns(2).SpecialCells(2, 1).Areas" in the code, to me, has no relation to US RAW and Current in the processing, so why this declaration in there? Thanks.

[jindon]

That is to find correct data range in Worksheet for loading the data to Dictionary in order to outputting matched Location data in Current.

[KSunShine]

Thanks so much Jindon! I'll miss you.

[KSunShine]

Hi Jindon,

Sometimes, Current sheet needs to be re-formatted or re-located of the Columns. And when it happens, the code won't
work appropriately.

So, I want US RAW be directly transposed to Banks using account code in Column A. Can you please help creating a code
for it? I attached Sample3 for your review. Thanks.

[jindon]

Sometimes, Current sheet needs to be re-formatted or re-located of the Columns.

How?

So, I want US RAW be directly transposed to Banks using account code in Column A.

What is your desired result?

If you are talking about col.U to fill up till the end then no need to do like this.
You should tell what you really want to happen from "Worksheet" & "US Raw".

[KSunShine]

Instead of manually copy each of account in CL (B:C) on US RAW then paste into CL (B:C) on Current, I want a code to process it. I uploaded the updated file for your review. Thanks Jindon!

[KSunShine]

Nothing to do with "Worksheet"

[jindon]

So, you are asking different question...

Please Login or Register  to view this content.

[KSunShine]

It worked perfect as expect. Thanks Jindon!