VBA - Macro for copy sheets, insert formulas and change row reference by +1

Question

I have macro for copy one sample sheet. Numbers of sheets copies are based by different sheet values. I need insert different formulas for every copy to specific range, where row number of formula is elevated by +1. Is it possible to do this?

Example what I need:

- Sheet1 "=DATA_SELECTED!$N$2"
- Sheet2 "=DATA_SELECTED!$N$3"
- Sheet3 "=DATA_SELECTED!$N$4"

This is what I have right now without +1 in formulas.

Sub CopySheetsFromAList()

Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("DATA").Range("A1")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
Set ws = Sheets("Sheet1")

For Each MyCell In MyRange
    ws.Copy after:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = MyCell.Value
    Sheets(Sheets.Count).Range("C3").Formula = "=DATA_SELECTED!$M$2"
    Sheets(Sheets.Count).Range("C4").Formula = "=DATA_SELECTED!$N$2"
    Sheets(Sheets.Count).Range("C6").Formula = "=DATA_SELECTED!$K$2"
    Sheets(Sheets.Count).Range("C7").Formula = "=DATA_SELECTED!$Y$2"
Next MyCell

End Sub

user3598756 user3598756 · Accepted Answer · 2017-01-25T07:17:15

maybe something like this

Option Explicit

Sub CopySheetsFromAList()    
    Dim MyCell As Range, MyRange As Range
    Dim ws As Worksheet
    Dim iRow As Long

    Set MyRange = Sheets("DATA").Range("A1")
    Set MyRange = Range(MyRange, MyRange.End(xlDown))
    Set ws = Sheets("Sheet1")

    For Each MyCell In MyRange
        ws.Copy after:=Sheets(Sheets.Count)
        iRow = iRow + 1
        With Sheets(Sheets.Count)
            .Name = MyCell.Value
            .Range("C3").Formula = "=DATA_SELECTED!$M$" & (1 + iRow)
            .Range("C4").Formula = "=DATA_SELECTED!$N$" & (1 + iRow)
            .Range("C6").Formula = "=DATA_SELECTED!$K$" & (1 + iRow)
            .Range("C7").Formula = "=DATA_SELECTED!$Y$" & (1 + iRow)
        End With
    Next MyCell    
End Sub

VBA - Macro for copy sheets, insert formulas and change row reference by +1

1 Answers