Excel - dependant copying / summary sheet
Closed
Keith T
-
Feb 1, 2010 at 06:58 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 2, 2010 at 07:15 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 2, 2010 at 07:15 AM
Related:
- Excel - dependant copying / summary sheet
- Mark sheet in excel - Guide
- How to open excel sheet in notepad++ - Guide
- How to screenshot excel sheet - Guide
- Windows network commands cheat sheet - Guide
- Google sheet right to left - Guide
3 responses
Hi Keith,
When in excel press Alt+F11. This will open VBE. Goto the top menus: Insert>Module and copy the following code into the empty white space:
I don't know how your sheets are named, so I named them Sheet1 ... Sheet13.
Sheet1 being Januari, ..., Sheet12 being December and Sheet13 being the summary.
Change the names to match yours.
Every section of code has a bold range. Change this range to the amount of data you have for each sheet in each section. If this is the same for each sheet, select the entire code and press Ctrl+H to find and replace A2:C4 by your range.
To run the code go to excel, press Alt+F8 and double click "Test".
Does this suit your needs?
Best regards,
Trowa
When in excel press Alt+F11. This will open VBE. Goto the top menus: Insert>Module and copy the following code into the empty white space:
Sub Test() Sheets("Sheet1").Select Range("A1").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="N" Range("A2:C4").Select Selection.Copy Sheets("Sheet13").Select Range("A2").Select ActiveSheet.Paste Sheets("Sheet1").Select Range("A1").Select Selection.AutoFilter Sheets("Sheet2").Select Range("A1").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="N" Range("A2:C4").Select Selection.Copy Sheets("Sheet13").Select Range("A1").End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Sheets("Sheet2").Select Range("A1").Select Selection.AutoFilter Sheets("Sheet3").Select Range("A1").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="N" Range("A2:C4").Select Selection.Copy Sheets("Sheet13").Select Range("A1").End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Sheets("Sheet3").Select Range("A1").Select Selection.AutoFilter Sheets("Sheet4").Select Range("A1").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="N" Range("A2:C4").Select Selection.Copy Sheets("Sheet13").Select Range("A1").End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Sheets("Sheet4").Select Range("A1").Select Selection.AutoFilter Sheets("Sheet5").Select Range("A1").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="N" Range("A2:C4").Select Selection.Copy Sheets("Sheet13").Select Range("A1").End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Sheets("Sheet5").Select Range("A1").Select Selection.AutoFilter Sheets("Sheet6").Select Range("A1").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="N" Range("A2:C4").Select Selection.Copy Sheets("Sheet13").Select Range("A1").End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Sheets("Sheet6").Select Range("A1").Select Selection.AutoFilter Sheets("Sheet7").Select Range("A1").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="N" Range("A2:C4").Select Selection.Copy Sheets("Sheet13").Select Range("A1").End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Sheets("Sheet7").Select Range("A1").Select Selection.AutoFilter Sheets("Sheet8").Select Range("A1").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="N" Range("A2:C4").Select Selection.Copy Sheets("Sheet13").Select Range("A1").End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Sheets("Sheet8").Select Range("A1").Select Selection.AutoFilter Sheets("Sheet9").Select Range("A1").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="N" Range("A2:C4").Select Selection.Copy Sheets("Sheet13").Select Range("A1").End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Sheets("Sheet9").Select Range("A1").Select Selection.AutoFilter Sheets("Sheet10").Select Range("A1").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="N" Range("A2:C4").Select Selection.Copy Sheets("Sheet13").Select Range("A1").End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Sheets("Sheet10").Select Range("A1").Select Selection.AutoFilter Sheets("Sheet11").Select Range("A1").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="N" Range("A2:C4").Select Selection.Copy Sheets("Sheet13").Select Range("A1").End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Sheets("Sheet11").Select Range("A1").Select Selection.AutoFilter Sheets("Sheet12").Select Range("A1").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="N" Range("A2:C4").Select Selection.Copy Sheets("Sheet13").Select Range("A1").End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Sheets("Sheet12").Select Range("A1").Select Selection.AutoFilter End Sub
I don't know how your sheets are named, so I named them Sheet1 ... Sheet13.
Sheet1 being Januari, ..., Sheet12 being December and Sheet13 being the summary.
Change the names to match yours.
Every section of code has a bold range. Change this range to the amount of data you have for each sheet in each section. If this is the same for each sheet, select the entire code and press Ctrl+H to find and replace A2:C4 by your range.
To run the code go to excel, press Alt+F8 and double click "Test".
Does this suit your needs?
Best regards,
Trowa
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 1, 2010 at 09:29 AM
Feb 1, 2010 at 09:29 AM
Presuming your monthy sheets are names as jan, feb.,,,,,
Sub CreateSum()
months = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
On Error GoTo Cont
Sheets("Summary").Delete
Cont:
On Error GoTo 0
Sheets.Add
ActiveSheet.Name = "Summary"
Sheets("Summary").Select
Cells(1, 1) = "Comp."
Cells(1, 2) = "Date"
Cells(1, 3) = "Item"
lSummaryRow = 2
For x = 0 To Sheets.Count - 2
Sheets(months(x)).Select
If ActiveSheet.AutoFilterMode Then
Cells.Select
Selection.AutoFilter
End If
Cells.Select
Selection.Sort _
Key1:=Range("B2"), Order1:=xlAscending, _
Key2:=Range("A2"), Order2:=xlAscending, _
Key3:=Range("C2"), Order3:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
If ActiveSheet.AutoFilterMode = False Then
Rows(1).Select
Selection.AutoFilter
End If
Selection.AutoFilter Field:=1, Criteria1:="=N", Operator:=xlAnd
Dim lastRow As Long
lastRow = Cells(65536, 1).End(xlUp).Row
If (lastRow > 1) Then
Range(Cells(2, 1), Cells(lastRow, 3)).Select
Selection.Copy
Sheets("Summary").Select
Cells(lSummaryRow, 1).Select
Selection.PasteSpecial
lSummaryRow = Cells(65536, 1).End(xlUp).Row
lSummaryRow = lSummaryRow + 1
End If
If ActiveSheet.Name = "Dec" Then Exit Sub
Next
End Sub
Sub CreateSum()
months = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
On Error GoTo Cont
Sheets("Summary").Delete
Cont:
On Error GoTo 0
Sheets.Add
ActiveSheet.Name = "Summary"
Sheets("Summary").Select
Cells(1, 1) = "Comp."
Cells(1, 2) = "Date"
Cells(1, 3) = "Item"
lSummaryRow = 2
For x = 0 To Sheets.Count - 2
Sheets(months(x)).Select
If ActiveSheet.AutoFilterMode Then
Cells.Select
Selection.AutoFilter
End If
Cells.Select
Selection.Sort _
Key1:=Range("B2"), Order1:=xlAscending, _
Key2:=Range("A2"), Order2:=xlAscending, _
Key3:=Range("C2"), Order3:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
If ActiveSheet.AutoFilterMode = False Then
Rows(1).Select
Selection.AutoFilter
End If
Selection.AutoFilter Field:=1, Criteria1:="=N", Operator:=xlAnd
Dim lastRow As Long
lastRow = Cells(65536, 1).End(xlUp).Row
If (lastRow > 1) Then
Range(Cells(2, 1), Cells(lastRow, 3)).Select
Selection.Copy
Sheets("Summary").Select
Cells(lSummaryRow, 1).Select
Selection.PasteSpecial
lSummaryRow = Cells(65536, 1).End(xlUp).Row
lSummaryRow = lSummaryRow + 1
End If
If ActiveSheet.Name = "Dec" Then Exit Sub
Next
End Sub
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 2, 2010 at 07:15 AM
Feb 2, 2010 at 07:15 AM
b4 to g4 ? But you have only 3 columns in your sample. Am I missing some thing ?