database_coder
New Member
- Joined
- Feb 6, 2014
- Messages
- 35
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.
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.
Code:
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
