Solution require how to add multiple sheet data in single sheet

[rajendradk9]

Dear All,

Need solution I've workbook contain Sheets 2716 having same column heading data in each sheet in 1st Row, also having some columns blank in between these heading so can you give me solution on it on the basis of column heading names below data in single sheet of all these sheets data.

Heading names as below

Product Name

Amount

sr no

DS

TS

Description

Qty

<tbody>
</tbody>

Rajendra

 

[hiker95]

rajendradk9,

What version of Excel and Windows are you using?

What you are requesting can be accomplished using the range object, as long as all the titles are on the same row, in this case row 1, no matter what columns they are in, or, what order they are in.


Can you post a screenshot of at least 2 of the actual raw data worksheets?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[rajendradk9]

rajendradk9,

What version of Excel and Windows are you using?

What you are requesting can be accomplished using the range object, as long as all the titles are on the same row, in this case row 1, no matter what columns they are in, or, what order they are in.


Can you post a screenshot of at least 2 of the actual raw data worksheets?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

Hello Hiker,

Excel 2007 system Windows 7,


-- removed inline image ---


2nd Screen shot

-- removed inline image ---


Output should be in below format


-- removed inline image ---

 

[rajendradk9]

Hello Hiker,

Using Excel 2007 system Windows 7, PLS download sample excel sheet for your ref below link.

https://www.dropbox.com/s/oii0vmklzhs5aho/sample.xlsx

Rajendra

 

[hiker95]

rajendradk9,

Thanks for the workbook.

For the sake of brevity, and, so that the screenshots will display correctly in MrExcel's format, not all columns are shown, and, some of the text descriptions have been shortened.


Sample raw data worksheets:

Excel 2007
	B	E	F	G	H	I	K
1	Cat Number	List Price	PGC	DS	TS	Description	Qty
2	415053005	11000	731 G8 MOT SHAFT				1
3	415056	11500	731 G8 MOT H-Series				1
4	415058	12000	731 G8 MOT H-Series				1
5	415059	12500	731 G8 MOT H-Series				1
6	415060	13000	731 G8 MOT F-Series				1
7	415061	13500	731 G8 MOT F-Series				1
8	415065	14000	731 G8 MOT H-Series				1
9	515011173	19000	622	E7 SSC Custom			1
10	522162	19500	645	E9 SSC DC MICRO			1
11	09B4041G08	20000	283	B7	ICB Mounting Circuit		1
12	100AC1000	20500	269	B7	ICB CONTACTOR,		1
13
Sheet1

Excel 2007
	B	E	F	G	H	I	K
1	Cat Number	List Price	PGC	DS	TS	Description	Qty
2	Z35852	10800	903	K3 SSC Limit Switch Assembly			1
3	Z35914	34300	336	B8	ICB ASSY,HANDLE/		1
4	Z36165	9290	903	K3 SSC Limit Switch Assembly			1
5	Z36166	11700	336	B8	ICB CARTRIDGES,		1
6	Z36302	2070	903	K3 SSC Limit Switch Assembly			1
7	Z36379	13400	903	K3 SSC Limit Switch Assembly			1
8	Z36403	34300	336	B8	ICB ASSY,DOOR LOCK		1
9	Z36576	2260	336	B8	ICB ASSY,COVER CATCH		1
10
Sheet 2716

After the macro in worksheet Summary (if the sheet does not exist the macro will create it, or, clear its contents if the sheet does exist):

Excel 2007
	A	B	C	D	E	F	G
1	Cat Number	List Price	PGC	DS	TS	Description	Qty
2	415053005	11000	731 G8 MOT SHAFT				1
3	415056	11500	731 G8 MOT H-Series				1
4	415058	12000	731 G8 MOT H-Series				1
5	415059	12500	731 G8 MOT H-Series				1
6	415060	13000	731 G8 MOT F-Series				1
7	415061	13500	731 G8 MOT F-Series				1
8	415065	14000	731 G8 MOT H-Series				1
9	515011173	19000	622	E7 SSC Custom			1
10	522162	19500	645	E9 SSC DC MICRO			1
11	09B4041G08	20000	283	B7	ICB Mounting Circuit		1
12	100AC1000	20500	269	B7	ICB CONTACTOR,		1
13	Z35852	10800	903	K3 SSC Limit Switch Assembly			1
14	Z35914	34300	336	B8	ICB ASSY,HANDLE/		1
15	Z36165	9290	903	K3 SSC Limit Switch Assembly			1
16	Z36166	11700	336	B8	ICB CARTRIDGES,		1
17	Z36302	2070	903	K3 SSC Limit Switch Assembly			1
18	Z36379	13400	903	K3 SSC Limit Switch Assembly			1
19	Z36403	34300	336	B8	ICB ASSY,DOOR LOCK		1
20	Z36576	2260	336	B8	ICB ASSY,COVER CATCH		1
21
Summary

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub GetAllSheetsDataToSummary()
' hiker95, 02/08/2014, ME756395
Dim ws As Worksheet
Dim lr As Long, nr As Long, n As Long
Dim cnrng As Range, lprng As Range, pgrng As Range, dsrng As Range, tsrng As Range, derng As Range, qtrng As Range
Application.ScreenUpdating = False
If Not Evaluate("ISREF(Summary!A1)") Then Worksheets.Add().Name = "Summary"
With Sheets("Summary")
  .UsedRange.ClearContents
End With
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "Summary" Then
    With ws
      Set cnrng = ws.Rows(1).Find("Cat Number", LookAt:=xlWhole)    'cnrng, Cat Number
      Set lprng = ws.Rows(1).Find("List Price", LookAt:=xlWhole)    'lprng, List Price
      Set pgrng = ws.Rows(1).Find("PGC", LookAt:=xlWhole)           'pgrng, PGC
      Set dsrng = ws.Rows(1).Find("DS", LookAt:=xlWhole)            'dsrng, DS
      Set tsrng = ws.Rows(1).Find("TS", LookAt:=xlWhole)            'tsrng, TS
      Set derng = ws.Rows(1).Find("Description", LookAt:=xlWhole)   'derng, Description
      Set qtrng = ws.Rows(1).Find("Qty", LookAt:=xlWhole)           'qtrng, Qty
      If (cnrng Is Nothing) * (lprng Is Nothing) * (pgrng Is Nothing) * (dsrng Is Nothing) _
            * (tsrng Is Nothing) * (derng Is Nothing) * (qtrng Is Nothing) Then
        MsgBox "One or more of the 7 titles in row 1, in sheet '" & ws.Name & "' NOT found!"
        GoTo Continue
      ElseIf (Not cnrng Is Nothing) * (Not lprng Is Nothing) * (Not pgrng Is Nothing) * (Not dsrng Is Nothing) _
            * (Not tsrng Is Nothing) * (Not derng Is Nothing) * (Not qtrng Is Nothing) Then
        n = n + 1
        If n = 1 Then
          ws.Cells(1, cnrng.Column).Copy Sheets("Summary").Range("A1")    'copy 7 titles to Summary!
          ws.Cells(1, lprng.Column).Copy Sheets("Summary").Range("B1")
          ws.Cells(1, pgrng.Column).Copy Sheets("Summary").Range("C1")
          ws.Cells(1, dsrng.Column).Copy Sheets("Summary").Range("D1")
          ws.Cells(1, tsrng.Column).Copy Sheets("Summary").Range("E1")
          ws.Cells(1, derng.Column).Copy Sheets("Summary").Range("F1")
          ws.Cells(1, qtrng.Column).Copy Sheets("Summary").Range("G1")
        End If
        nr = Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
        lr = .Cells(Rows.Count, cnrng.Column).End(xlUp).Row
        .Range(.Cells(2, cnrng.Column), .Cells(lr, cnrng.Column)).Copy Sheets("Summary").Range("A" & nr)
        .Range(.Cells(2, lprng.Column), .Cells(lr, lprng.Column)).Copy Sheets("Summary").Range("B" & nr)
        .Range(.Cells(2, pgrng.Column), .Cells(lr, pgrng.Column)).Copy Sheets("Summary").Range("C" & nr)
        .Range(.Cells(2, dsrng.Column), .Cells(lr, dsrng.Column)).Copy Sheets("Summary").Range("D" & nr)
        .Range(.Cells(2, tsrng.Column), .Cells(lr, tsrng.Column)).Copy Sheets("Summary").Range("E" & nr)
        .Range(.Cells(2, derng.Column), .Cells(lr, derng.Column)).Copy Sheets("Summary").Range("F" & nr)
        .Range(.Cells(2, qtrng.Column), .Cells(lr, qtrng.Column)).Copy Sheets("Summary").Range("G" & nr)
      End If
    End With
  End If
Continue:
Next ws
Application.ScreenUpdating = True
With Sheets("Summary")
  .Columns.AutoFit
  .Activate
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetAllSheetsDataToSummary macro.

 

[hiker95]

rajendradk9,

I finally realized that your two raw data worksheets used the same columns.

For the sake of brevity, and, so that the screenshots will display correctly in MrExcel's format, not all columns are shown, and, some of the text descriptions have been shortened.

Sample raw data worksheets (where the columns in each can be different):

Excel 2007
	C	F	H	J	M	N	P
1	Cat Number	List Price	PGC	DS	TS	Description	Qty
2	415053005	11000	731 G8 MOT SHAFT				1
3	415056	11500	731 G8 MOT H-Series				1
4	415058	12000	731 G8 MOT H-Series				1
5	415059	12500	731 G8 MOT H-Series				1
6	415060	13000	731 G8 MOT F-Series				1
7	415061	13500	731 G8 MOT F-Series				1
8	415065	14000	731 G8 MOT H-Series				1
9	515011173	19000	622	E7 SSC Custom			1
10	522162	19500	645	E9 SSC DC MICRO			1
11	09B4041G08	20000	283	B7	ICB Mounting Circuit		1
12	100AC1000	20500	269	B7	ICB CONTACTOR,		1
13
Sheet1

Excel 2007
	B	E	F	G	I	J	L
1	Cat Number	List Price	PGC	DS	TS	Description	Qty
2	Z35852	10800	903	K3 SSC Limit Switch Assembly			1
3	Z35914	34300	336	B8	ICB ASSY,HANDLE/		1
4	Z36165	9290	903	K3 SSC Limit Switch Assembly			1
5	Z36166	11700	336	B8	ICB CARTRIDGES,		1
6	Z36302	2070	903	K3 SSC Limit Switch Assembly			1
7	Z36379	13400	903	K3 SSC Limit Switch Assembly			1
8	Z36403	34300	336	B8	ICB ASSY,DOOR LOCK		1
9	Z36576	2260	336	B8	ICB ASSY,COVER CATCH		1
10
Sheet 2716

After the macro in worksheet Summary (if the sheet does not exist the macro will create it, or, clear its contents if the sheet does exist):

Excel 2007
	A	B	C	D	E	F	G
1	Cat Number	List Price	PGC	DS	TS	Description	Qty
2	415053005	11000	731 G8 MOT SHAFT				1
3	415056	11500	731 G8 MOT H-Series				1
4	415058	12000	731 G8 MOT H-Series				1
5	415059	12500	731 G8 MOT H-Series				1
6	415060	13000	731 G8 MOT F-Series				1
7	415061	13500	731 G8 MOT F-Series				1
8	415065	14000	731 G8 MOT H-Series				1
9	515011173	19000	622	E7 SSC Custom			1
10	522162	19500	645	E9 SSC DC MICRO			1
11	09B4041G08	20000	283	B7	ICB Mounting Circuit		1
12	100AC1000	20500	269	B7	ICB CONTACTOR,		1
13	Z35852	10800	903	K3 SSC Limit Switch Assembly			1
14	Z35914	34300	336	B8	ICB ASSY,HANDLE/		1
15	Z36165	9290	903	K3 SSC Limit Switch Assembly			1
16	Z36166	11700	336	B8	ICB CARTRIDGES,		1
17	Z36302	2070	903	K3 SSC Limit Switch Assembly			1
18	Z36379	13400	903	K3 SSC Limit Switch Assembly			1
19	Z36403	34300	336	B8	ICB ASSY,DOOR LOCK		1
20	Z36576	2260	336	B8	ICB ASSY,COVER CATCH		1
21
Summary

The same macro that is in my last reply #6, produced the above results.

 

[rajendradk9]

rajendradk9,

I finally realized that your two raw data worksheets used the same columns.

For the sake of brevity, and, so that the screenshots will display correctly in MrExcel's format, not all columns are shown, and, some of the text descriptions have been shortened.

Sample raw data worksheets (where the columns in each can be different):

Excel 2007

	C	F	H	J	M	N	P
1	Cat Number	List Price	PGC	DS	TS	Description	Qty
2	415053005	11000	731 G8 MOT SHAFT				1
3	415056	11500	731 G8 MOT H-Series				1
4	415058	12000	731 G8 MOT H-Series				1
5	415059	12500	731 G8 MOT H-Series				1
6	415060	13000	731 G8 MOT F-Series				1
7	415061	13500	731 G8 MOT F-Series				1
8	415065	14000	731 G8 MOT H-Series				1
9	515011173	19000	622	E7 SSC Custom			1
10	522162	19500	645	E9 SSC DC MICRO			1
11	09B4041G08	20000	283	B7	ICB Mounting Circuit		1
12	100AC1000	20500	269	B7	ICB CONTACTOR,		1
13

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Excel 2007

	B	E	F	G	I	J	L
1	Cat Number	List Price	PGC	DS	TS	Description	Qty
2	Z35852	10800	903	K3 SSC Limit Switch Assembly			1
3	Z35914	34300	336	B8	ICB ASSY,HANDLE/		1
4	Z36165	9290	903	K3 SSC Limit Switch Assembly			1
5	Z36166	11700	336	B8	ICB CARTRIDGES,		1
6	Z36302	2070	903	K3 SSC Limit Switch Assembly			1
7	Z36379	13400	903	K3 SSC Limit Switch Assembly			1
8	Z36403	34300	336	B8	ICB ASSY,DOOR LOCK		1
9	Z36576	2260	336	B8	ICB ASSY,COVER CATCH		1
10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet 2716​

After the macro in worksheet Summary (if the sheet does not exist the macro will create it, or, clear its contents if the sheet does exist):

Excel 2007

	A	B	C	D	E	F	G
1	Cat Number	List Price	PGC	DS	TS	Description	Qty
2	415053005	11000	731 G8 MOT SHAFT				1
3	415056	11500	731 G8 MOT H-Series				1
4	415058	12000	731 G8 MOT H-Series				1
5	415059	12500	731 G8 MOT H-Series				1
6	415060	13000	731 G8 MOT F-Series				1
7	415061	13500	731 G8 MOT F-Series				1
8	415065	14000	731 G8 MOT H-Series				1
9	515011173	19000	622	E7 SSC Custom			1
10	522162	19500	645	E9 SSC DC MICRO			1
11	09B4041G08	20000	283	B7	ICB Mounting Circuit		1
12	100AC1000	20500	269	B7	ICB CONTACTOR,		1
13	Z35852	10800	903	K3 SSC Limit Switch Assembly			1
14	Z35914	34300	336	B8	ICB ASSY,HANDLE/		1
15	Z36165	9290	903	K3 SSC Limit Switch Assembly			1
16	Z36166	11700	336	B8	ICB CARTRIDGES,		1
17	Z36302	2070	903	K3 SSC Limit Switch Assembly			1
18	Z36379	13400	903	K3 SSC Limit Switch Assembly			1
19	Z36403	34300	336	B8	ICB ASSY,DOOR LOCK		1
20	Z36576	2260	336	B8	ICB ASSY,COVER CATCH		1
21

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Summary​

The same macro that is in my last reply #6, produced the above results.

Hello Hiker,

I've gone through stepwise but while doing seen below pop-up of debuging or end showing download from below link.

https://www.dropbox.com/s/o9x8jjq9ypk9q5s/sampleok.png

If you see this image I've click it on Debug button then cursor automatically gone to macro coding & seen the highlighted showing check with the below link

https://www.dropbox.com/s/1capxsns8crg43r/debug.png

Is there any concern, PLS confirm

Rajendra

 

[hiker95]

rajendradk9,

Please do not quote entire replies from your helper. When quoting follow these guidelines:
1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
2. Quote ONLY the specific part of the post that is relevant - - not the entire post.

This will keep thread clutter to a minimum and make the discussion easier to follow.


I can not do, or see, anything with the png files.

Please put the workbook that is causing the error up on BOX.

 

[hiker95]

rajendradk9,

Try this first:

In the macro, remove the following lines of code:

Application.ScreenUpdating = False


Application.ScreenUpdating = True

Save the workbook, and, run the macro again.