Macro used in conjunction with Data Validation

[stringfolk]

I found this bit of code that the user came up with to run through a data validation list and print each unique page. i'd like to take this code and instead of print, copy and paste values+formats to a new workbook.

can someone to direct me on how?

Sorry everyone...

I just figured it out.

Sub PrintSheet()
 
    Dim Cell As Range
 
    For Each Cell In Range("PlanList")
        X = Cell.Value
        Range("C9") = X
        ActiveWindow.SelectedSheets.PrintOut copies:=1
 
    Next Cell
 
End Sub

 

[goldfish]

Does something like this work for you?

Sub Macro5()
    Dim WkSht As Worksheet, Cell As Range, X As Variant
    
    For Each Cell In Range("PlanList")
        
        'Sets new value
        X = Cell.Value
        Sheets("Sheet1").Range("C9") = X
        
        'Creates new workbook with selected sheets (still formula based though)
        ActiveWindow.SelectedSheets.Copy
        
        'Cycles through each sheet and creates a values only version of each
        For Each WkSht In ActiveWorkbook.Sheets
            WkSht.Cells.Copy
            WkSht.Cells.PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False
        Next WkSht
        
        'Saves and closes the workbook (Without this code, the activeworkbook
        'is still the newly created one, so I think the cycle would break when
        'it tries to set a new value to X again on the next time through)
        ActiveWorkbook.Save
        ActiveWorkbook.Close
    Next Cell
End Sub

Hope this helps,
~Gold fish

 

[stringfolk]

yes that helped so much.

now what if i just want my print_area copied/pasted and not the whole worksheet?

 

[goldfish]

Is it only one sheet's print area you have to copy paste? if so you can use this:

Sub Macro6()
    Dim WkSheet As Worksheet, Cell As Range, X As Variant
    
    For Each Cell In Range("PlanList")
        
        'Sets new value
        X = Cell.Value
        Sheets("Sheet1").Range("C9") = X
        
        'Copy Print_area
        Range("Print_Area").Copy
        
        'Add a new sheet (and stores the name of the new sheet
        'into the WkSheet variable so we can reference it later)
        Set WkSheet = Sheets.Add
        
        'Paste the copied values into the new sheet
        WkSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
        
        'Moves the new sheet to its own workbook
        WkSheet.Move
        
        ActiveWorkbook.Save
        ActiveWorkbook.Close
    Next Cell
End Sub

 

[stringfolk]

i like what's occurring better in the first macro but if i could only paste my particular print area instead.