Macro that can save a workbook multiple times with changes in each one.

[glide2131]

I have a macro (that I have attached) that currently does the following:

1. Copy a cell "A2" in "Sheet1" to "Input"cell "A4" then
2. Save the entire workbook as is to "C:\Macro" with the naming convention being: (The contents of “A4”.2013) in the "Input" sheet
3. Then repeat for every cell in Column A that has data.
So the end result is that I have a workbook saved for every line in column A.

However, I need to make the following change and I am not sure how to go about it:

I added some information in Columns C and E of "Sheet1". I need all of the C column data to go to "Input" "C2" and all of the E column data to go to "Input" "E5".

So for example, in the first workbook copy the "Input sheet should read: "15-14-16" in "A1" and "John" in "C2" and "AZ" in “E5" and the second workbook copy should read: “18-19-21 in “A1” Steven in “C2” and “AZ” in “E5”.

Does anyone know how to help me with this?
Thanks a ton

[AlphaFrog]

Try something like this. Put the code in a standard code module (e.g.; Module1) and not a worksheet's code module.

Sub WorksheetCopyPaste()
    Dim c As Range
    With Worksheets("Sheet1")
        For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp))  'Loop from A1 to last used cell in column A
            Worksheets("Input").Range("A4").Value = c.Value             'Column A
            Worksheets("Input").Range("C2").Value = c.Offset(, 2).Value 'Column C
            Worksheets("Input").Range("E5").Value = c.Offset(, 4).Value 'Column E
            ThisWorkbook.SaveAs "C:\Macro" & c.Value & ".xlsm"
        Next c
    End With
End Sub

[glide2131]

Works perfectly. Thank you very much.