Previous Topic Next Topic

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
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
Related:

3 responses

Answer 1 / 3
Trowa
Feb 1, 2010 at 09:23 AM
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
0
Answer 2 / 3
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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
0
Keith T
Feb 2, 2010 at 02:20 AM
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
0
Answer 3 / 3
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 2, 2010 at 07:15 AM
b4 to g4 ? But you have only 3 columns in your sample. Am I missing some thing ?
0