0

So I have a workbook with a master sheet column f has my citys. I need to copy a unique list of citys from the master sheet to each existing sheet.

I've looked at :

sheets("MasterSheet").Select
    Columns("F:F").Select
    Selection.Copy
    sheets("Existingsheet").Select
    Columns("F:F").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("F1:F3125").AdvancedFilter Action:=xlFilterInPlace, Unique:=True

But I'm missing a loop as well as grabbing the existing sheet names dynamically. I'm thinking there must be a quicker way to get to where I want to be.

Any direction would be most welcome. Thanks.

okmred
  • 147
  • 2
  • 12
  • Can you clarify a little more? Your list from your "Master Sheet" is to be copied in each worksheet, range F:F? What loop are you missing? Why do you need to grab the sheet names? – BruceWayne Oct 14 '15 at 19:33
  • Yes, I want to copy a unique list from the citys list in the master sheet to the other sheets. (how would I get my unique list to the other sheets with out the names of them??) – okmred Oct 14 '15 at 19:37

2 Answers2

0

You can shorten your code a little by avoiding using .Select and loop through each worksheet.

Sub test()
Dim ws as worksheet, mainWS as worksheet
Dim cityNames as Range
Set mainWS = Sheets("MasterSheet")

cityNames = mainWS.Range("F:F")

'Let's now loop through each worksheet in your activebook
For each ws in ActiveWorkbook.Worksheets
  With ws
     If ws.Name <> "additional" or ws.Name <> "MasterSheet" Then
      .Range("F:F").Value = cityNames.Value
      .Range("F1:F3125").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
     End if
End with
Next ws

End sub

This is untested, but should work. Please let me know if there are any errors, or it doesn't quite work.

You don't need the name of each worksheet, since you can use a For loop. Do you see how that works in the above?

Community
  • 1
  • 1
BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • How does it avoid over writing the main ws? I also have a hidden ws with additionl data that I don't want "updated" with this info. Thanks – okmred Oct 15 '15 at 13:43
0

Here is a solution that provides the loop you need to move through each worksheet and copy the unique cities to column F for each worksheet (not named "MasterSheet"). It will leave you with just the unique list and not a unique filtered list on each sheet as well as show the entire data on the MasterSheet as it was before you started.

This code assumes the city list in the MasterSheet is a contiguous list of cities starting in cell F1

Sub CopyUniqueCityList()

Dim rCityList As Range

With Sheets("MasterSheet")
    With .Range(.Range("F1"), .Range("F1").End(xlDown))
        .AdvancedFilter Action:=xlFilterInPlace, Unique:=True
        Set rCityList = .SpecialCells(xlCellTypeVisible)
    End With
    .ShowAllData
End With

Dim ws As Worksheet
For Each ws In Worksheets

    If ws.Visible = xlSheetVisible And ws.Name <> "MasterSheet" Then

        rCityList.Copy ws.Range("F1")

    End If

Next

End Sub
Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
  • What if I have more than 1 sheet I want to not add these cities to? I have several hidden sheets with other data that I don't want to be affected by this . Thanks – okmred Oct 15 '15 at 13:58
  • I have edited my code to not include hidden sheets in the update. If you need to add more names, you can just and conditions to the `If` statement. If it is a lot, you may want a different approach ... – Scott Holtzman Oct 15 '15 at 14:25
  • Got it thanks. So how would I sort the list? I have the unique list copied to all the necessary tabs like I want it to be, with the exception the order is mixed up. I see where we have : "Action:=xlFilterInPlace" , would this not be for sorting? – okmred Oct 15 '15 at 14:50
  • sorting is a different animal. use the macro recorder to get the code for sorting (record a macro of you sorting a random range), then apply it to your code - of course, you may need to make some adjustments to fit your exact code.. If you get stuck on this or have another question, create a new question on SO. Please mark this one as answered so people know its solved. One more tip - You'll probably be based served sorting the `MasterSheet` first, then your filter will be sorted as well with the unique values. – Scott Holtzman Oct 15 '15 at 14:52
  • Thanks, will do on all counts. – okmred Oct 15 '15 at 15:08