VBA - Creating multiple files from list

Question

I am struggling with my VBA code. Instead of fixed values in a table, which contains the names how the workbooks should be saved as. My range needs to be variable (below example for starting with range "A3").

Sheets("CC").Select  'sheet with the names
Range("A3").Select   ' starting from this range are the names in a column
Selection.Copy
Sheets("CZK").Select  'going to different sheet to paste some value
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False      'pasting values to different sheet 
Application.CutCopyMode = False
Sheets("CC").Select        'returning back to the sheet with names
Nazev = Range("A3")
ActiveWorkbook.SaveAs Filename:=cesta & Nazev    'saving it with predefined path and name

I have to start like this:

Set MyRange = Sheets("CC").Range("A3")   ' predefining varible range
Set MyRange = Range(MyRange, MyRange.End(xlDown))

But then I am stuck.

See [how to avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251). That may help clarify how to work directly with data. — BruceWayne, Oct 18 '17 at 14:30

score 0 · Accepted Answer · answered Oct 18 '17 at 14:45

Something like this should work for you:

Sub tst()

    Dim wb As Workbook
    Dim wsNames As Worksheet
    Dim wsDest As Worksheet
    Dim NameCells As Range
    Dim NameCell As Range
    Dim cesta As String
    Dim Nazev As String

    cesta = "C:\Test\"
    Set wb = ActiveWorkbook
    Set wsNames = wb.Sheets("CC")
    Set wsDest = wb.Sheets("CZK")
    Set NameCells = wsNames.Range("A3", wsNames.Cells(wsNames.Rows.Count, "A").End(xlUp))

    Application.DisplayAlerts = False
    For Each NameCell In NameCells
        Nazev = NameCell.Value
        wsDest.Range("B2").Value = Nazev
        wb.SaveAs cesta & Nazev & ".xlsm", xlOpenXMLWorkbookMacroEnabled
    Next NameCell
    Application.DisplayAlerts = True

End Sub

VBA - Creating multiple files from list

1 Answers1