Macro to Combine Data from Different Columns

[newbie18]

I have a large sales data report containing severalgroups of customers. I would like to automatically generate individual usagereports (separate files so not by tab) from this large file for each groupevery month and save them separately on a shared drive. I would like to also have each reportautomatically be customized with different headers (based on customers’request); however, I could evade this if it is not possible.
Here is a condensed example of my sales report. The actual report has several rows for eachgroup that contains different sales, document date, customer ID, and etc. I would like to combine all data in columns A,C, E, H, and I related to the group ABC. With the combined data, I would like them to be copied and pasted onto anew worksheet and saved on a shared drive under a folder named ABC. Is there a macro or method to do this? Would I need to do a find and search on thegroup name, and then copy the data onto a new worksheet?


[TABLE="width: 817"]
<tbody>[TR]
[TD="width: 86"] Customer ID
[/TD]
[TD="width: 78"] Item ID
[/TD]
[TD="width: 113"] Document Date ID
[/TD]
[TD="width: 133"] Accounting Year DESC
[/TD]
[TD="width: 127"] Calendar Month DESC
[/TD]
[TD="width: 98"] Calendar Year ID
[/TD]
[TD="width: 56"]

Qty Sold​

[/TD]
[TD="width: 117"]

Price Paid Each​

[/TD]
[TD="width: 91"]

Sales​

[/TD]
[TD="width: 191"]

Group Name​

[/TD]
[/TR]
[TR]
[TD="width: 86"] 209103997
[/TD]
[TD="width: 78"] 075704259
[/TD]
[TD="width: 113"] 8/12/2019
[/TD]
[TD="width: 133"] Fiscal 2020
[/TD]
[TD="width: 127"] August 2019
[/TD]
[TD="width: 98"] 2019
[/TD]
[TD="width: 56"]

1​

[/TD]
[TD="width: 117"]

$ 36.00​

[/TD]
[TD="width: 91"]

$ 36.00​

[/TD]
[TD="width: 191, bgcolor: transparent"] ABC
[/TD]
[/TR]
[TR]
[TD="width: 86"] 209103998
[/TD]
[TD="width: 78"] UN5758511
[/TD]
[TD="width: 113"] 8/12/2019
[/TD]
[TD="width: 133"] Fiscal 2020
[/TD]
[TD="width: 127"] August 2019
[/TD]
[TD="width: 98"] 2019
[/TD]
[TD="width: 56"]

0​

[/TD]
[TD="width: 117"]

$ 1.52​

[/TD]
[TD="width: 91"]

$ 1.52​

[/TD]
[TD="width: 191, bgcolor: transparent"] DEF
[/TD]
[/TR]
[TR]
[TD="width: 86"] 209103999
[/TD]
[TD="width: 78"] UN5758529
[/TD]
[TD="width: 113"] 8/12/2019
[/TD]
[TD="width: 133"] Fiscal 2020
[/TD]
[TD="width: 127"] August 2019
[/TD]
[TD="width: 98"] 2019
[/TD]
[TD="width: 56"]

0​

[/TD]
[TD="width: 117"]

$ 1.76​

[/TD]
[TD="width: 91"]

$ 1.76​

[/TD]
[TD="width: 191, bgcolor: transparent"] GHI
[/TD]
[/TR]
[TR]
[TD="width: 86"] 209104289
[/TD]
[TD="width: 78"] 075704259
[/TD]
[TD="width: 113"] 8/12/2019
[/TD]
[TD="width: 133"] Fiscal 2020
[/TD]
[TD="width: 127"] August 2019
[/TD]
[TD="width: 98"] 2019
[/TD]
[TD="width: 56"]

1​

[/TD]
[TD="width: 117"]

$ 36.00​

[/TD]
[TD="width: 91"]

$ 36.00​

[/TD]
[TD="width: 191, bgcolor: transparent"] JKL
[/TD]
[/TR]
</tbody>[/TABLE]

 

[pmich]

newbie18

The following is possible:
"separate files so not by tab) from this large file for each groupevery month and save them separately on a shared drive."

You said : 'I would like to combine all data in columns A,C, E, H,..."
Based on what criteria?

Then you said "... and I related to the group ABC" --- What is this? Someone else may understand. But I couldn't.

You further said : "...With the combined data.." -- It will be good if you give some example.

The following is possible:
"I would like them to be copied and pasted onto anew worksheet and saved on a shared drive under a folder named ABC."

You asked : "Is there a macro or method to do this?" -- Yes, VBA code can do this job.
But, provide a sample of the end result.

You asked : "Would I need to do a find and search on the group name, and then copy the data onto a new worksheet?"
What you are trying to find?
Is it 'on the group name' or 'based on the group name'?

If you are specific, then you will surely get several suggestions from experts. I can try to give my suggestion.

 

[newbie18]

newbie18

The following is possible:
"separate files so not by tab) from this large file for each groupevery month and save them separately on a shared drive."

You said : 'I would like to combine all data in columns A,C, E, H,..."
Based on what criteria? Based on the Group Name column

Then you said "... and I related to the group ABC" --- What is this? Someone else may understand. But I couldn't. Sorry, this was a mistyped. I basically want customized reports based on the group. The Group Name will have multiple rows for each group, so I would like to combine all data, say ABC, and have the report automatically saved on the shared folder. Here is an example of the raw data:

You further said : "...With the combined data.." -- It will be good if you give some example.
Here is what I would like the customized report to look like from the raw data above:

The following is possible:
"I would like them to be copied and pasted onto a new worksheet and saved on a shared drive under a folder named ABC."

You asked : "Is there a macro or method to do this?" -- Yes, VBA code can do this job.
But, provide a sample of the end result.

You asked : "Would I need to do a find and search on the group name, and then copy the data onto a new worksheet?"
What you are trying to find?
Is it 'on the group name' or 'based on the group name'? Combine data based on the group name

If you are specific, then you will surely get several suggestions from experts. I can try to give my suggestion.

 

[pmich]

I will post my suggestion.
You can now have a look here:

How to Use VBA Macros to Copy Data to Another Workbook in Excel

Learn how to copy data from one workbook to another using VBA macros in Excel, includes copying below existing data and ranges that change size. +Video

www.excelcampus.com

 

[pmich]

This code will filter group ABC and will copy it to another WorkBook.
Edit the File Name, Sheet Name, Column Name according to your requirement.

Private Sub cmdFilterAndCopyToWkBk2()
 Dim rng As Range
 Workbooks.Open "d:\SourceData.xlsx"
 Workbooks.Open "d:\DestData.xlsx"
 Dim wsSrc As Worksheet
 Dim wsDest As Worksheet
 Dim lSrcLastRow As Long
 Dim lDestLastRow As Long
 Set wsSrc = Workbooks("SourceData.xlsx").Worksheets("SrcData")
 Set wsDest = Workbooks("DestData.xlsx").Worksheets("CopiedData")
 lSrcLastRow = wsSrc.Cells(wsSrc.Rows.Count, "A").End(xlUp).Row
 Set rng = wsSrc.Range("A1:J" & lSrcLastRow)
 lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
 With rng
  .AutoFilter
  .AutoFilter Field:=10, Criteria1:="ABC"
  .SpecialCells(xlCellTypeVisible).Copy
  .Offset(1, 0).Resize(rng.Rows.Count - 1).Copy Destination:=wsDest.Range("A" & LTrim(Str(lDestLastRow + 1)))
 End With
 Workbooks("DestData.xlsx").Close SaveChanges:=True
End Sub

 

[newbie18]

Thank you for your quick response. I have a few questions below:

Workbooks.Open "d:\SourceData.xlsx" Would this be the file name of the raw data? Do I insert the full file path or just the file name?
Workbooks.Open "d:\DestData.xlsx" Would this be the file name of the new data (say for ABC)?
Set wsSrc = Workbooks("SourceData.xlsx").Worksheets("SrcData") Do I change the sheet name here under Worksheets(SrcData) on the raw data file?
Set wsDest = Workbooks("DestData.xlsx").Worksheets("CopiedData") Same question as above but with the new data?

Also, am I able to select only certain columns to copy over? The raw data file has over 30 columns, and I don't need all columns.

This code will filter group ABC and will copy it to another WorkBook.
Edit the File Name, Sheet Name, Column Name according to your requirement.

Private Sub cmdFilterAndCopyToWkBk2()
Dim rng As Range
Workbooks.Open "d:\SourceData.xlsx"
Workbooks.Open "d:\DestData.xlsx"
Dim wsSrc As Worksheet
Dim wsDest As Worksheet
Dim lSrcLastRow As Long
Dim lDestLastRow As Long
Set wsSrc = Workbooks("SourceData.xlsx").Worksheets("SrcData")
Set wsDest = Workbooks("DestData.xlsx").Worksheets("CopiedData")
lSrcLastRow = wsSrc.Cells(wsSrc.Rows.Count, "A").End(xlUp).Row
Set rng = wsSrc.Range("A1:J" & lSrcLastRow)
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
With rng
  .AutoFilter
  .AutoFilter Field:=10, Criteria1:="ABC"
  .SpecialCells(xlCellTypeVisible).Copy
  .Offset(1, 0).Resize(rng.Rows.Count - 1).Copy Destination:=wsDest.Range("A" & LTrim(Str(lDestLastRow + 1)))
End With
Workbooks("DestData.xlsx").Close SaveChanges:=True
End Sub

 

[newbie18]

Thank you for your quick response. I have a few questions below:

Workbooks.Open "d:\SourceData.xlsx" Would this be the file name of the raw data? Do I insert the full file path or just the file name?
Workbooks.Open "d:\DestData.xlsx" Would this be the file name of the new data (say for ABC)?
Set wsSrc = Workbooks("SourceData.xlsx").Worksheets("SrcData") Do I change the sheet name here under Worksheets(SrcData) on the raw data file?
Set wsDest = Workbooks("DestData.xlsx").Worksheets("CopiedData") Same question as above but with the new data?

Can certain columns be selected to copy over? The raw data file has over 30 columns, and I don't need all columns. Also, am I able to replicate this for all of the other groups, since I would need a report for different groups. Lastly, is there a way to use a cell in the raw data file to type in the group name and have the macro run a report based on what it typed in the cell? For example, if I type in "ABC" in cell A2, the macro will automatically run the report for ABC. Next, if I want the same report for "DEF" in cell A2, a report will be run for DEF.

 

[pmich]

newbie18
Workbooks.Open "d:\SourceData.xlsx" Would this be the file name of the raw data? Yes. This is file name of Source Data which has all groups.
Workbooks.Open "d:\SourceData.xlsx" Do I insert the full file path or just the file name? Yes. You have to type the full path of the Source File.
Workbooks.Open "d:\DestData.xlsx" Would this be the file name of the new data (say for ABC)? Yes. This is the file name of the ABC Group Data.
Set wsSrc = Workbooks("SourceData.xlsx").Worksheets("SrcData") Do I change the sheet name here under Worksheets(SrcData) on the raw data file? Yes. You have to type the Sheet Name of the Raw File, which is Source File.
Set wsDest = Workbooks("DestData.xlsx").Worksheets("CopiedData") Same question as above but with the new data? Yes. You have to type the Sheet Name of the ABC Group File, which is Destination File.
Can certain columns be selected to copy over? Yes.
am I able to replicate this for all of the other groups Yes.
is there a way to use a cell in the raw data file to type in the group name and have the macro run a report based on what it typed in the cell? Yes.
if I type in "ABC" in cell A2, the macro will automatically run the report for ABC. Next, if I want the same report for "DEF" in cell A2, a report will be run for DEF. Yes. Alternatively, if you need reports for all groups, the programme can be edited to do that job. Or, instead of typing in a cell, you can type in a text box the group name of your choice and the programme will give you the report of that group and will wait for you either to type the next group's name or to exit the programme. Hope by saying REPORT you mean the filtered data of that particular Group .
The above code should be in another WorkBook.
But, if you want the above Code to be in the Source WorkBook, then the extension of the Source File will not be xlsx, but it will be xlsm, which has been saved by you as a macro enabled WorkBook.
If you are using the code in the Source File itself, then you will open it manually first to run the code.
Hence, you wont use the line of code in the above programme to open the Source File.
Moreover, the code has to be edited to do the following:
To open WorkBooks of each Group, if they are available or to create WorkBooks for each Group and open them to paste the filtered data.
To filter data of each available group in the Source File, which is Raw data File.
To post the filtered data in that Group's Destination WorkBook.
To save each Destination File.
I will suggest you (and I hope that the experts who reply in Excel Forums will also suggest) that you should spend some time in searching for excel tutorials on the above issues.
That will increase your Excel expertise.
Simply if you need a code for your present need, and if you get it, your problem may be solved, but you will still be in need of more suggestions from experts.
Normally, what you have achieved so far should be posted, which I see a common practice in the Excel Forums that I look into.