Excel VBA Match Columns and Paste from One Workbook to Another

mamun.ges · Oct 17th 2017

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

Mumps · Oct 17th 2017

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.

Code

Sub CopyRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("Sheet1").Range("A1:D" & LastRow).AutoFilter Field:=3, Criteria1:="Female"
    Sheets("Sheet1").Range("A2:D" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Workbooks("B.xlsx").Sheets("Female").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Sheets("Sheet1").Range("A1:D" & LastRow).AutoFilter Field:=3, Criteria1:="Male"
    Sheets("Sheet1").Range("A2:D" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Workbooks("B.xlsx").Sheets("Male").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    If Sheets("Sheet1").FilterMode Then Sheets("Sheet1").ShowAllData
    Application.ScreenUpdating = True
End Sub

Display More

mamun.ges · Oct 17th 2017

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?

mamun.ges · Oct 17th 2017

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

Mumps · Oct 17th 2017

Try:

Code

Sub CopyRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("Sheet1").Range("A1:D" & LastRow).AutoFilter Field:=3, Criteria1:="Female"
    Sheets("Sheet1").Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Workbooks("B.xlsx").Sheets("Female").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Sheets("Sheet1").Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Workbooks("B.xlsx").Sheets("Female").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
    Sheets("Sheet1").Range("A1:D" & LastRow).AutoFilter Field:=3, Criteria1:="Male"
    Sheets("Sheet1").Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Workbooks("B.xlsx").Sheets("Male").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Sheets("Sheet1").Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Workbooks("B.xlsx").Sheets("Male").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
    If Sheets("Sheet1").FilterMode Then Sheets("Sheet1").ShowAllData
    Application.ScreenUpdating = True
End Sub

Display More

mamun.ges · Oct 18th 2017

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.

Mumps · Oct 18th 2017

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.

mamun.ges · Oct 19th 2017

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.

Mumps · Oct 19th 2017

Try the attached files.

mamun.ges · Oct 20th 2017

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.

Mumps · Oct 20th 2017

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.

mamun.ges · Oct 21st 2017

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.

Mumps · Oct 21st 2017

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?

mamun.ges · Oct 21st 2017

Hi Mumps

Here is the file send for your understanding my thoughts.

Thanks again.

Mumps · Oct 22nd 2017

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?

mamun.ges · Oct 23rd 2017

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.

Mumps · Oct 23rd 2017

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?

mamun.ges · Oct 23rd 2017

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.

Mumps · Oct 24th 2017

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?

mamun.ges · Oct 24th 2017

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.

Excel VBA Match Columns and Paste from One Workbook to Another

Participate now!

Share