Question - How to search through the first row for a column header, copy the data, paste into another sheet and also paste the worksheet name the data

[database_coder]

Hi All,

Thank you very much for everyone who has posted to this website. I have been using it for sometime now, and finally decided to make an account because I have a question that I can not seem to answer.

I have a Workbook consisting of 30 Worksheets. In each worksheet, my headers go from A1:Z1, but the number of observations are not consistent, meaning every worksheet has a different set of data. On each worksheet, I am only worried about the data in the Columns (Number, and Person), but the order is different throughout each worksheet.

What my goal here is - copy all the data for Number, and Person from each worksheet, and paste into a SUMMARY worksheet. Also, on each worksheet data is copied I need to save the name of the worksheet next to the data in the SUMMARY worksheet.

I know there is a way to copy the data all into an array, and paste to SUMMARY at the end. But I can not find a proper way to do it.

My code is a mess, which I have pasted below. Is there anyway we can do this? Any help will be most appreciated.

Sub B_IDs()
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim sum As Worksheet
    Dim Leng As Long
    Dim x As Long
        Set sum = Sheets("Summary")
    
    sum.Cells.Clear
    
    Application.ScreenUpdating = False
    
    
    For Each ws In ActiveWorkbook.Worksheets
    Leng = ws.Range("A2").End(xlDown).Row + 1
        If ws.Name <> "Summary" Then
            If ws.Cells(1, 1).Value = "ID" Then
                ws.Range("A2:A100").Copy
                Worksheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
            End If
        End If
  
'        x = 1
'        For x = x To 20
'            If ws.Cells(1, x).Value = "PERSON" Then
'
'
''                Selection.Copy.Column
''                Worksheets("Summary").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
'            End If
'        Next x
    Next ws
    
   
        
End Sub

Sub WorksheetLoop()
    Dim wsCount As Integer
    Dim I As Integer
    Dim ws As Worksheet
    Dim RowCount As Long
    Dim x As Long
        
    wsCount = ActiveWorkbook.Worksheets.Count
    'RowCount = ActiveWorksheet.Count
    
    For Each ws In ActiveWorkbook.Worksheets
    
        For x = 1 To 100
            If Cells(x, "A") = "ID" And Cells(x, "F") = "PERSON" Then
                Cells(x, "A").Resize(, 3).Copy
                Worksheets("Summary").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
                Cells(x
            
            
            
            End If
            
        Next x
        
    Next ws

 

[hiker95]

database_coder,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?

I have a Workbook consisting of 30 Worksheets. In each worksheet, my headers go from A1:Z1, but the number of observations are not consistent, meaning every worksheet has a different set of data. On each worksheet, I am only worried about the data in the Columns (Number, and Person), but the order is different throughout each worksheet.

1. Can we have a screenshot of two of the raw data worksheets?

2. Can we have a screenshot of the SUMMARY worksheet, before, and after the data from the two raw data worksheets have been manually formatted by you for the results 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.

 

[database_coder]

Hi Hiker95,

I am currently using Windows 7 with Excel 2010 at work. I have just created a sample workbook at home, using my Mac (the link is located on the bottom of this post).

Now, my goal here is to create a Macro that copies the column labeled Number (in each worksheet it is column A) and Person (this column is not the same in every worksheet throughout the workbook), pastes it into the Summary Worksheet at the end of the Workbook, placing Number in Column A, Person in Column B, and the Name of the spreadsheet from which the data was copied from in Column C. The Summary spreadsheet is not populated, and is currently empty. The reason why I want to create a Macro is because the Workbook I am using is linked to Microsoft Access, and each Worksheet that needs to be copied is a table from the Microsoft Access database. Currently there are 30 worksheets, but going further this number is subject to increase.

My problem is when Person is in different columns. I will continue to try and accomplish this task, but any help would be most appreciated.

The link to my sample workbook is : https://app.box.com/s/2k655mxi4f4ong4ivigf

Thanks
database_coder

 

[database_coder]

I could probably use a Do While Loop, but I still do not know how to search the First Row to find 'Person', and then put the name of the worksheet next to the pasted data. Copying and Pasting the data from one worksheet to another is very simple. Or, one could use a For statement and copy the data into an array, paste the data in Summary, but I still question how I would be able to put the worksheet name next to the pasted data.

How could we implement:

 Activesheet.[A1] = Activesheet.Name

into this Macro?

 

[hiker95]

database_coder,

Please post another workbook, with worksheet Summary manually formatted by you for the results you are looking for.

 

[database_coder]

Number	Person	Worksheet Name
10	Mike	WorksheetName1Example
25	Don	WorksheetName2Example
3	Jordan	WorksheetName3Example
13	Sarsh	WorksheetName4Example
16	Tom	WorksheetName5Example
2	Eliza	WorksheetName6Example
1	Frank	WorksheetName7Example
8	Tim	WorksheetName8Example
40	Jessica	WorksheetName9Example

<tbody>
</tbody>

 

[database_coder]

Above is how I would like the summary worksheet to be formatted, Number in Column A, Person in Column B, and Worksheet Name in Column C.

 

[database_coder]

Although there may be several Observations for each worksheet. I do not think this is possible but if anyone has any insight please share.

 

[hiker95]

database_coder,

Thanks for the workbook and the screenshot.

I assume that in each raw data worksheet, the column title Name exists in row 1, and, Name does not contain any leading or trailing space characters.

In the following screenshots of the raw data worksheets I am only displaying the Number, and, the Name columns.

Excel 2007
	A	D
1	Number	Name
2	2	Pete
3	3	Tim
4	6	Tom
5	7	Frank
6	8	Ed
7	10	Sarah
8	15	Jessica
9	23	Angie
10	19	Fred
11	132	Mike
12	135	Fred
13	137	Mike
14	139	Doug
15	142	Pete
16	144	Tim
17	147	Tom
18	149	Frank
19	152	Ed
20	154	Sarah
21	157	Jessica
22	159	Angie
23	162	Fred
24	164	Mike
25
Breaks

Excel 2007
	A	C
1	Number	Name
2	38	Fred
3	40	Mike
4	43	Doug
5	45	Pete
6	48	Tim
7	50	Tom
8	53	Frank
9	55	Ed
10	58	Fred
11	60	Mike
12	62	Doug
13	65	Pete
14	67	Tim
15	70	Tom
16	72	Frank
17	75	Ed
18
OctBreaks

Excel 2007
	A	H
1	Number	Name
2	100	Ed
3	102	Fred
4	105	Mike
5	107	Fred
6	110	Mike
7	112	Doug
8	115	Pete
9	117	Tim
10	120	Tom
11	122	Frank
12	125	Ed
13	127	Fred
14	130	Mike
15
NovBreaks

Excel 2007
	A	I
1	Number	Name
2	2	Fred
3	3	Mike
4	6	Doug
5	7	Pete
6	8	Tim
7	10	Tom
8	15	Frank
9	23	Ed
10	19	Fred
11	23	Mike
12	25	Doug
13	28	Pete
14	30	Tim
15	33	Tom
16	35	Frank
17	77	Ed
18	80	Fred
19	82	Mike
20	85	Doug
21	87	Pete
22	90	Tim
23	92	Tom
24	95	Frank
25	97	Ed
26
DecBreaks

Before the macro worksheet Summary is empty:

Excel 2007
	A	B	C
1	Number	Person	Worksheet Name
2
3
4
Summary

After the macro:

Excel 2007
	A	B	C
1	Number	Person	Worksheet Name
2	2	Pete	Breaks
3	3	Tim	Breaks
4	6	Tom	Breaks
5	7	Frank	Breaks
6	8	Ed	Breaks
7	10	Sarah	Breaks
8	15	Jessica	Breaks
9	23	Angie	Breaks
10	19	Fred	Breaks
11	132	Mike	Breaks
12	135	Fred	Breaks
13	137	Mike	Breaks
14	139	Doug	Breaks
15	142	Pete	Breaks
16	144	Tim	Breaks
17	147	Tom	Breaks
18	149	Frank	Breaks
19	152	Ed	Breaks
20	154	Sarah	Breaks
21	157	Jessica	Breaks
22	159	Angie	Breaks
23	162	Fred	Breaks
24	164	Mike	Breaks
25	38	Fred	OctBreaks
26	40	Mike	OctBreaks
27	43	Doug	OctBreaks
28	45	Pete	OctBreaks
29	48	Tim	OctBreaks
30	50	Tom	OctBreaks
31	53	Frank	OctBreaks
32	55	Ed	OctBreaks
33	58	Fred	OctBreaks
34	60	Mike	OctBreaks
35	62	Doug	OctBreaks
36	65	Pete	OctBreaks
37	67	Tim	OctBreaks
38	70	Tom	OctBreaks
39	72	Frank	OctBreaks
40	75	Ed	OctBreaks
41	100	Ed	NovBreaks
42	102	Fred	NovBreaks
43	105	Mike	NovBreaks
44	107	Fred	NovBreaks
45	110	Mike	NovBreaks
46	112	Doug	NovBreaks
47	115	Pete	NovBreaks
48	117	Tim	NovBreaks
49	120	Tom	NovBreaks
50	122	Frank	NovBreaks
51	125	Ed	NovBreaks
52	127	Fred	NovBreaks
53	130	Mike	NovBreaks
54	2	Fred	DecBreaks
55	3	Mike	DecBreaks
56	6	Doug	DecBreaks
57	7	Pete	DecBreaks
58	8	Tim	DecBreaks
59	10	Tom	DecBreaks
60	15	Frank	DecBreaks
61	23	Ed	DecBreaks
62	19	Fred	DecBreaks
63	23	Mike	DecBreaks
64	25	Doug	DecBreaks
65	28	Pete	DecBreaks
66	30	Tim	DecBreaks
67	33	Tom	DecBreaks
68	35	Frank	DecBreaks
69	77	Ed	DecBreaks
70	80	Fred	DecBreaks
71	82	Mike	DecBreaks
72	85	Doug	DecBreaks
73	87	Pete	DecBreaks
74	90	Tim	DecBreaks
75	92	Tom	DecBreaks
76	95	Frank	DecBreaks
77	97	Ed	DecBreaks
78
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 GetNumberPersonSheetName()
' hiker95, 02/07/2014, ME755957
Dim ws As Worksheet
Dim lr As Long, fprng As Range, nr As Long
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "Summary" Then
    With ws
      nr = Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      lr = .Cells(Rows.Count, 1).End(xlUp).Row
      Sheets("Summary").Range("A" & nr).Resize(lr - 1).Value = .Range(.Cells(2, 1), .Cells(lr, 1)).Value
      Sheets("Summary").Range("C" & nr).Resize(lr - 1).Value = ws.Name
      Set fprng = .Rows(1).Find("Name", LookAt:=xlWhole)
      If Not fprng Is Nothing Then
        Sheets("Summary").Range("B" & nr).Resize(lr - 1).Value = .Range(.Cells(2, fprng.Column), .Cells(lr, fprng.Column)).Value
      End If
    End With
  End If
Next ws
With Sheets("Summary")
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  .Range("A2:A" & lr).NumberFormat = "0"
  .Columns.AutoFit
  .Activate
End With
Application.ScreenUpdating = True
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 GetNumberPersonSheetName macro.

 

[database_coder]

Hiker95,

Wow, I am astonished by how clean the code is and how successful it runs. Very good work.

Would it be possible to extend this one step further by stating Number to change columns between each worksheet. How would you go about changing this?