Excel - dependant copying / summary sheet [Closed]

- - Latest reply: rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
- Feb 2, 2010 at 07:15 AM
Hello,
I want to set up a monthly sheet that records items in a table, first columm of table (A1) is headed completed, B1 is Date, C1 is item then for each row the column is ticked or unticked for the item being completed. Same sheet size and layout used on different sheets - 1 sheet for each month Jan ~ Dec
sheet 13 will be a Summary sheet, which will list all uncompleted (ie unticked) actions in date order, earliest first for the year to date that updates as items are added or Completed throught the year

Jan
A1 B1 C1
Comp. Date Item
Y 1/1/10 Widget1
N 4/1/10 Widget2
Y 9/1/10 Widget6

Feb
A1 B1 C1
Comp. Date Item
Y 2/2/10 Widget8
N 6/2/10 Widget12
N 8/2/10 Widget13

Summary - List of Not Completed for all months
A1 B1 C1
Comp. Date Item
N 4/1/10 Widget2
N 6/2/10 Widget12
N 8/2/10 Widget13
See more 

3 replies

0
Thank you
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:

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
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
754
0
Thank you
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
Thanks for that- not a VB expert but will give it a try over next few days.
On the "pretty" version Column headers are in row 4 (B4 ~ G4 to be exact ) on each Month Sheet with Comp. in B4 will have to learn about addressing the various cells to get it to work
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
754
0
Thank you
b4 to g4 ? But you have only 3 columns in your sample. Am I missing some thing ?