MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Updating duplicated sheets


Posted by Celeste on June 13, 2001 5:50 AM

I have a row of column of invoice numbers. Each Invoice has a specific Node & Hub number associated to it in another column. I managed to write some code to create an invoice per sheet and duplicate the sheet(naming each one the invoice number) for as many invoices as are listed. I'm struggling with how to populate each individual sheet with the specific Node & Hub numbers that ae associated to it. Can anyone help?


Posted by Joe Was on June 13, 2001 2:14 PM

This may help?

Some of this code may help you. As it finds unique items, sort and counts the occurrences on one sheet and prints to another?
You could change the count module to a copy module! JSW

Sub Find_Names()
'Finds all the unique names, count the number of times each name is listed and builds a sorted list with counts.
'Data is on Sheet2: Names(G), Values(I), Results are listed on Sheet3: Names(H), Count(I).
Application.ScreenUpdating = False

'Find unique names on Sheet2(G), col(7) & list on Sheet3(H).
With Intersect(Columns(7), ActiveSheet.UsedRange)
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
.SpecialCells(xlCellTypeVisible).Copy Destination:=Worksheets("Sheet3").Range("H1")
ActiveSheet.ShowAllData
End With

'Sort unique names on sheet3(H).
Sheets("Sheet3").Select
Columns(8).Sort Key1:=Range("H1")

'Count occurrences of names on Sheet2(G), col(7).
'List the number of times each unique name occurred on Sheet2(G), next to the list on sheet3(I).
With Worksheets("Sheet3")
.Range("I1").Formula = "=CountIf(Sheet2!" & Intersect(Sheet2.Columns(7), Sheet2.UsedRange).Address & ",H1)"
.Range("I1:I" & .Range("H1").End(xlDown).Row).FillDown
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

This code works with a form button or hot-key. JSW