0

Hiyall!

This is a followup to previous post. So here is the code which works.

Sub MarcoTemplate()
Dim c As Range
Dim n As String
For Each c In Sheets("ref").Range("A2:A3")
    n = c
    Vl = Application.WorksheetFunction.VLookup(n, Sheets("ref").Range("A2:D3"), 2, False)
    Worksheets.Add.Name = c
    ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)
    Worksheets("temp").Range("A1:D3").Copy ActiveSheet.Range("A1")
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=ref!R2C2" + "+1"
    Selection.AutoFill Destination:=Range("C2:C3")
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=ref!R2C2" + "*4"
    Selection.AutoFill Destination:=Range("D2:D3")
    Range("G2").Select
    ActiveCell.FormulaR1C1 = Vl
Next c
End Sub

However I'd like to know how do I add formula change within it? I need for each c move formula 1 column or row in any direction in ActiveCell.FormulaR1C1 = "=ref!R2C2" + "+1"

First thing I thought was to add new variable lets say "d" and assing value to it and add +1 in the end like "R2C"&"d" but I dont know how to make d+=1 in the end.

Also another question, how do I change ActiveSheet to the name of newly created list? Sheets("n") does not work.

JayJayAbrams
  • 195
  • 1
  • 16
  • you said that this code works. this site is for resolving errors in code. if you are getting errors, then please describe what you are getting. – jsotola Nov 02 '17 at 02:24
  • [Avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Use `Range` variables instead, it will help greatly. – BruceWayne Nov 02 '17 at 02:26
  • 1
    ^^ and use `Worksheet` object variables too - it will allow you to do things like `Dim ws As Worksheet : Set ws = Worksheets.Add : ws.Name = n` and then `ws.Move After:=Sheets(ActiveWorkbook.Sheets.Count)` and then `Worksheets("temp").Range("A1:D3").Copy ws.Range("A1")` (thus avoiding the need to even use `Worksheets(n)` which I assume was what you wanted to use when you instead used `Sheets("n")`) – YowE3K Nov 02 '17 at 03:09

0 Answers0