Excel VBA Match Columns and Paste from One Workbook to Another

  • I have data set in a workbook Name "A" sheet 1 like follwing: [TABLE="border: 1, cellpadding: 1, width: 500"]

    [tr]


    [td]

    SL

    [/td]


    [td]

    Name

    [/td]


    [td]

    Gender

    [/td]


    [td]

    Age

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    Asley

    [/td]


    [td]

    Female

    [/td]


    [td]

    21

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    Johan

    [/td]


    [td]

    Male

    [/td]


    [td]

    32

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    Jack

    [/td]


    [td]

    Male

    [/td]


    [td]

    31

    [/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    Rubi

    [/td]


    [td]

    Female

    [/td]


    [td]

    28

    [/td]


    [/tr]


    [/TABLE]


    I have another worksheet Name "B" and sheet Name "Male" where
    all the "Male" categories will be copied like this [TABLE="border: 1, cellpadding: 1, width: 500"]

    [tr]


    [td]

    SL

    [/td]


    [td]

    Name

    [/td]


    [td]

    Gender

    [/td]


    [td]

    Age

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    Johan

    [/td]


    [td]

    Male

    [/td]


    [td]

    32

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    Jack

    [/td]


    [td]

    Male

    [/td]


    [td]

    31

    [/td]


    [/tr]


    [/TABLE]


    in this worksheet Name "B" there is another sheet name "Female" where
    all the "female" categories will be copied like this [TABLE="border: 1, cellpadding: 1, width: 500"]

    [tr]


    [td]

    SL

    [/td]


    [td]

    Name

    [/td]


    [td]

    Gender

    [/td]


    [td]

    Age

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    Asley

    [/td]


    [td]

    Female

    [/td]


    [td]

    21

    [/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    Rubi

    [/td]


    [td]

    Female

    [/td]


    [td]

    28

    [/td]


    [/tr]


    [/TABLE]


    Can it done using VBA. Now I am Using formula but when ever I change the source file
    its formula giving wrong data.


    Thanks

  • Make sure that both workbooks are open and that workbook "B.xlsx" (change the workbook name in the code to match your actual name) has a sheet named "Male" and another named "Female" and both sheet have the appropriate headers in row 1. Place this macro in a regular module in your Workbook A and run it from there.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi, Thanks You for your support. It says


    "Run time error 9


    Subscript out of range"


    I have open developer mode and create a module then copy the code you provide. Then press Alt+F8 to run the macro. The Massage above shows. Am I doing right?

  • Its Worked !!!


    I wrongly copy code to workbook B instead of A. If the code copy to workbook B that worked fine for me. If I want Only Name and Age in Male and Female Sheet in workbook B what I need to do?


    Thank You So Much...

  • Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi,
    Thank You Again. I apply the code but the result is: In sheet B data copy only in Female sheet and no data copy in Male sheet. And the source file A,Sheet 1 Gender filter to Female. There is this massage:
    [ATTACH=JSON]{"data-align":"none","data-size":"medium","data-attachmentid":1193979}[/ATTACH] .



    Now,Is there any way where I create a active x control in Destination file B in every sheet. If this control clicked the data from the source file A transfer to Destination file B. The code need to input in destination file B. And if data in file A, Sheet 1 changes then the data in file B sheet Male & Female changes automatically.


    This is File A [TABLE="border: 1, cellpadding: 1, width: 500"]

    [tr]


    [td]

    SL

    [/td]


    [td]

    Name

    [/td]


    [td]

    Gender

    [/td]


    [td]

    Age

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    Asley

    [/td]


    [td]

    Female

    [/td]


    [td]

    21

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    Ad

    [/td]


    [td]

    Male

    [/td]


    [td]

    32

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    Axz

    [/td]


    [td]

    Male

    [/td]


    [td]

    31

    [/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    Rubi

    [/td]


    [td]

    Female

    [/td]


    [td]

    28

    [/td]


    [/tr]


    [/TABLE]


    And In file B Sheet Female:
    [ATTACH=JSON]{"data-align":"none","data-size":"medium","data-attachmentid":1193980}[/ATTACH]


    And In file B sheet Male as well as same with Male data:


    Is it possible to do in that way using VBA? Thank you Again for the support.

  • It would be easier to test the code if I can work with your actual files. Can you attach a copy of both files? Include a step-by-step detailed explanation of what you want to do referring to specific cells and worksheets.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi Mumps,


    Thanks for the feedback. For the purpose of coding I am attaching the test file though the main is large enough and having personal data.
    There is two files: one file is the source file name "A" which change after the calculation is complete then another source file impute for calculation stored the previous one in another Location) other file is "B" which doing all the calculation.


    The code you provide earlier which need to impute in the source file "A". Problem is after the calculation done the source file need to remove from the folder. So every time code need to impute in source file. It will be great if the calculation file Named "B" having the code. When ever the active x control clicked it pick the data from the source file pasted in the calculation file "B" in different sheet name according to product types in same header name.


    When Both file is open: If any change is done in source file data the same change will happen in the calculation file data in real time.


    Thank You Again for your valuable support.

  • Hi Mumps,


    Thank You so much for the support. The attached file worked fine for me.


    Ok. There is one thing. If the file A is in filter mode then File B debug " Say run time error 1004". after second click it collect data and File A showing all data, filter mode gone. Is it possible to collect data while the filter mode on and leave the filter mode as it is. Whenever I am changing something in File A while the filter mode is on, it goes back to show all data. Can the file A leave as it is. Hope there is way.


    Secondly, Always need to paste code in file A, there is no alternative.


    Thanking you Again.
    All the Best.

  • Delete this line of code in the macro in File A:

    Code
    If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData


    You need to have that macro in File A if you want File B to update automatically when you change data in File A.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi, Mumps


    Thanks again. It worked fine.
    Now, If I wrongly click collect data active x control twice, then It collect data again. Which make duplicate entry. Can it be controlled?
    If found duplicate do not collect data skip duplicate.


    There is other problem: When ever collect data active x control click then data in file B, column E and F all cleared !!
    Then I Remove the line [VBA]ActiveSheet.UsedRange.Offset(1, 0).ClearContents [/VBA] now no data in file B, column E and F is cleaned. Is it OK?



    Thanking you Again.
    All the Best.

  • In the file B that you posted there was no data in columns E and F. I based the macro on the file you posted. If your actual file is different, that will affect how the macro works. Can you post copies of your actual files?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • I had a look at your files and they are very different from your original samples. Please explain again in detail referring to specific cells, ranges, columns and sheets exactly what you want to do. Which columns from Data File A do you want to copy to Data File B? Do you still want a 'Collect Data' button on the appropriate sheets in Data File B? I noticed that Data File A no longer has a macro in it. Do you still want Data File B to update automatically when you make changes in Data File A? If so, can those changes be made in any column or row in Data File A or just in the columns that you want to copy from A to B?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi Mumps,


    Yes, the Original file is different from the sample file. I just wanted to know that if it is possible? And you have done it right. Ok, Now I am describing in detail.


    The source is Data A file and File B is destination file. In source file there is only one sheet name Sheet1. But there is multiple sheet in File B name FDR, DBS, TBS, MES, MESN (Please skip other files). I want the File B collect data from File A as per sheet name (FDR, DBS, TBS, MES, MESN) filtering the D column (MIGDEP_AC_TYPE) of File A. In B file for Sheet FDR only three columns collect data those are Account No, Date of Opening, and month type collect data from File A column B, E and G respectively. For Sheet DBS only two columns collect data those are Account No, Date of Opening, from File A column B, and E respectively. Sheet TBS, MES, MESN are the same as Sheet DBS. The copy of original file don't have the macro because I am testing on the sample file that you send. If the data automatically upload then I do not need the "Collect Data" button. But Yes,I still want to Data File B update automatically when I make changes in Data File A. Yes You are absolutely right, I definitely want only in the columns that I want to copy from A to B and there will be no duplicate data in Account No column.


    Thank You So Much for your support.
    All the best.

  • Column B in DATA FILE A has the Account Number. Can I assume that you will never change an existing account number in column B? Can I also assume that you will add new account numbers at the bottom of your data from time to time?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi Mumps


    Your assumption is absolutely right. :thumbcoo:. But Some times file A change and another file A paste in the same folder where file B exist.


    Thanks and All the best.

  • What do you mean when you say: "Some times file A change and another file A paste in the same folder where file B exist." Are there two Data File A files?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi Mumps,


    Yes, When one A file data correction complete with the help of File B then File A send to another folder and Another File A paste in the same folder where File B exist for calculation again. That's why I am said in previous conversation that every time I need to copy the code in File A.


    Thanks to you
    All the Best.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!