1
votes

I have written the following code which creates a variable # of additional sheets in my workbook based on how many rows have names in them on my "Master" sheet column B. This macro also copies the various names in Column B on the Master and pastes it in cell A7 on the additional sheets. The last step I am having problems with is to also copy the following cells from the Master sheet to the newly created sheets:

Master: C7, E7, F7, J7, R7, S7, U7, V7, W7, X7, Y7, AA7
Created Sheets: A62, D21, D29, D23, D25, D36, I21, I29, I23, I25, I36, D45

Any thoughts are greatly appreciated.

Sub CreateAddtlSheets()

    Dim ListSh As Worksheet, BaseSh As Worksheet
    Dim NewSh As Worksheet
    Dim ListOfNames As Range, LRow As Long, Cell As Range

    With ThisWorkbook
        Set ListSh = .Sheets("Master")
        Set BaseSh = .Sheets("Stmt")
    End With

    LRow = ListSh.Cells(Rows.Count, "B").End(xlUp).Row
    Set ListOfNames = ListSh.Range("B7:B" & LRow)

    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    For Each Cell In ListOfNames
        BaseSh.Copy After:=Sheets(Sheets.Count)
        Set NewSh = ActiveSheet
        With NewSh
        On Error GoTo 0
            .Range("A7") = Cell.Value
            .Calculate
            .Cells.Copy
            .Cells.PasteSpecial xlPasteValues
        End With
    Next Cell

    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    BaseSh.Activate '--Select Base.

    Sheets("Setup").Select
End Sub
1
If I follow, within with newSh, do .range("A62")=ListSh.range("C7") and so on. - findwindow

1 Answers

0
votes
.Cells.Copy
.Cells.PasteSpecial xlPasteValues

does nothing as it copys then pastes to the same space. The comment from findwindow is correct just set the cells values the way you did

.Range("A7") = Cell.Value

Or are we missing somthing?