+ Reply to Thread
Results 1 to 43 of 43

Copy From One Sheet to Another Sheet of Same Workbook

  1. #1
    Registered User
    Join Date
    08-20-2021
    Location
    U.S,A
    MS-Off Ver
    MS2019
    Posts
    40

    Copy From One Sheet to Another Sheet of Same Workbook

    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
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    06-27-2010
    Location
    sYRIA
    MS-Off Ver
    Excel 2013
    Posts
    581

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    Hi
    Try this code on a copy of your file
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    08-20-2021
    Location
    U.S,A
    MS-Off Ver
    MS2019
    Posts
    40

    Re: Copy From One Sheet to Another Sheet of Same Workbook

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

  4. #4
    Registered User
    Join Date
    08-20-2021
    Location
    U.S,A
    MS-Off Ver
    MS2019
    Posts
    40

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    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!
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    06-27-2010
    Location
    sYRIA
    MS-Off Ver
    Excel 2013
    Posts
    581

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    All right its my mistake
    Try this code instead and let me know
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    08-20-2021
    Location
    U.S,A
    MS-Off Ver
    MS2019
    Posts
    40

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    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!
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,380

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    Try the attached
    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    08-20-2021
    Location
    U.S,A
    MS-Off Ver
    MS2019
    Posts
    40

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    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.

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,380

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    Quote Originally Posted by KSunShine View Post
    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.

  10. #10
    Registered User
    Join Date
    08-20-2021
    Location
    U.S,A
    MS-Off Ver
    MS2019
    Posts
    40

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    Hi jindon,

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

    Ksunshine
    Attached Files Attached Files

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,380

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    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
    Quote Originally Posted by KSunShine
    However, due to merged function of the code, the format on "Current" has new face. I do want the original format on "Current".

  12. #12
    Registered User
    Join Date
    08-20-2021
    Location
    U.S,A
    MS-Off Ver
    MS2019
    Posts
    40

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    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!
    Attached Files Attached Files

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,380

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    OK, that's better and faster.
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    08-20-2021
    Location
    U.S,A
    MS-Off Ver
    MS2019
    Posts
    40

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    Hi Jindon,

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

    KSunShine.

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,380

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    You are welcome.

  16. #16
    Registered User
    Join Date
    08-20-2021
    Location
    U.S,A
    MS-Off Ver
    MS2019
    Posts
    40

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    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.
    Attached Files Attached Files

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,380

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    What is the correct result that you expect?

  18. #18
    Registered User
    Join Date
    08-20-2021
    Location
    U.S,A
    MS-Off Ver
    MS2019
    Posts
    40

    Re: Copy From One Sheet to Another Sheet of Same Workbook

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

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,380

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    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?

  20. #20
    Registered User
    Join Date
    08-20-2021
    Location
    U.S,A
    MS-Off Ver
    MS2019
    Posts
    40

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    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
    Attached Files Attached Files

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,380

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    What I want to see is...
    Quote Originally Posted by jindon View Post
    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.

  22. #22
    Registered User
    Join Date
    08-20-2021
    Location
    U.S,A
    MS-Off Ver
    MS2019
    Posts
    40

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    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.
    Attached Files Attached Files

  23. #23
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,380

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    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.
    Attached Files Attached Files

  24. #24
    Registered User
    Join Date
    08-20-2021
    Location
    U.S,A
    MS-Off Ver
    MS2019
    Posts
    40

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    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.

  25. #25
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,380

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    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.

  26. #26
    Registered User
    Join Date
    08-20-2021
    Location
    U.S,A
    MS-Off Ver
    MS2019
    Posts
    40

    Re: Copy From One Sheet to Another Sheet of Same Workbook

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

  27. #27
    Registered User
    Join Date
    08-20-2021
    Location
    U.S,A
    MS-Off Ver
    MS2019
    Posts
    40

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    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)
    Attached Files Attached Files

  28. #28
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,380

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    Do you mean?
    Please Login or Register  to view this content.

  29. #29
    Registered User
    Join Date
    08-20-2021
    Location
    U.S,A
    MS-Off Ver
    MS2019
    Posts
    40

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    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.
    Attached Files Attached Files

  30. #30
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,380

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    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.

  31. #31
    Registered User
    Join Date
    08-20-2021
    Location
    U.S,A
    MS-Off Ver
    MS2019
    Posts
    40

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    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.

  32. #32
    Registered User
    Join Date
    08-20-2021
    Location
    U.S,A
    MS-Off Ver
    MS2019
    Posts
    40

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    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.
    Attached Files Attached Files

  33. #33
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,380

    Re: Copy From One Sheet to Another Sheet of Same Workbook

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

    Inset a blank row at row 231 in US Raw.

  34. #34
    Registered User
    Join Date
    08-20-2021
    Location
    U.S,A
    MS-Off Ver
    MS2019
    Posts
    40

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    Oh! Thanks Master!!!

  35. #35
    Registered User
    Join Date
    08-20-2021
    Location
    U.S,A
    MS-Off Ver
    MS2019
    Posts
    40

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    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.

  36. #36
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,380

    Re: Copy From One Sheet to Another Sheet of Same Workbook

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

  37. #37
    Registered User
    Join Date
    08-20-2021
    Location
    U.S,A
    MS-Off Ver
    MS2019
    Posts
    40

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    Thanks so much Jindon! I'll miss you.

  38. #38
    Registered User
    Join Date
    08-20-2021
    Location
    U.S,A
    MS-Off Ver
    MS2019
    Posts
    40

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    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.
    Attached Files Attached Files

  39. #39
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,380

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    Quote Originally Posted by KSunShine View Post
    Sometimes, Current sheet needs to be re-formatted or re-located of the Columns.
    How?
    Quote Originally Posted by KSunShine View Post
    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".

  40. #40
    Registered User
    Join Date
    08-20-2021
    Location
    U.S,A
    MS-Off Ver
    MS2019
    Posts
    40

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    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!
    Attached Files Attached Files

  41. #41
    Registered User
    Join Date
    08-20-2021
    Location
    U.S,A
    MS-Off Ver
    MS2019
    Posts
    40

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    Nothing to do with "Worksheet"

  42. #42
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,380

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    So, you are asking different question...
    Please Login or Register  to view this content.

  43. #43
    Registered User
    Join Date
    08-20-2021
    Location
    U.S,A
    MS-Off Ver
    MS2019
    Posts
    40

    Re: Copy From One Sheet to Another Sheet of Same Workbook

    It worked perfect as expect. Thanks Jindon!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 02-18-2020, 02:01 PM
  2. [SOLVED] Copy sheet from another workbook based on ComboBox names of the Workbook and sheet
    By igormigor in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-01-2016, 10:07 AM
  3. Copy specific data from a sheet to temp sheet and copy temp sheet to new workbook
    By ANUARORA in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-18-2015, 11:58 AM
  4. how to copy specific data from a sheet to temp sheet and copy temp sheet toa new workbook?
    By NatashaKapoor in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-18-2014, 09:01 AM
  5. Replies: 3
    Last Post: 11-06-2014, 01:23 AM
  6. [SOLVED] macro to copy rows from a sheet in one workbook to a sheet in a different workbook...
    By fredderf81 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-16-2013, 01:36 PM
  7. [SOLVED] Search for heading in data sheet and copy range to corresponding sheet in master workbook
    By sans in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-07-2012, 10:02 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1