1

I'm trying to copy a whole sheet, which includes a couple tables with formulas. I've set up a macro for coyping the sheet with the tables, but when pasted the formula in the tables refer to the previous sheet instead the one which it is on.

    Cells.Select
Range("C2").Activate
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Range("B5").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "10/12/2020"
Range("B6:W21").Select
Selection.ClearContents
Range("B5").Select
Selection.ClearContents
Dim username As String
username = InputBox("What is the starting date of your planning? (In the format mm-dd)")
Range("B5").Value = username
Dim sheetname As String
sheetname = InputBox("What will be the name of the sheet")
ActiveSheet.Name = sheetname
    Range("B25").Select
ActiveCell.Formula2R1C1 = "=SUM(IF("sheetname"!R6C2:R21C16=[@Activiteit],1,0))"

And the "sheetname" in the last line of code I would like to be the variable sheetname.

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
EagleStrike
  • 13
  • 2
  • 1
    Please read [How to avoid using select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Ron Rosenfeld Oct 15 '20 at 12:47

1 Answers1

1

So your last line should be as follows I believe:

ActiveCell.Formula2R1C1 = "=SUM(IF(" + sheetname + "!R6C2:R21C16=[@Activiteit],1,0))"
Atanas Atanasov
  • 359
  • 1
  • 10