PDA

View Full Version : [SOLVED] Using a string in names of dublicates



lauritsjust
07-29-2019, 02:29 AM
Hello VBA Express

I have created the following working Sub that dublicates an array of sheets and charts (those are connected) and replaces a specific value in the input sheets (named e_xxxx inp).

How can i name the new sheets and charts based on a specific string in a cell in the "Evaluations -->" sheet e.g. Sheets("template inp (2)").Name = "e_"Evaluations --> "C14"" inp"?
I have the same question in regard to changing the activeCell.FormulaR1C1 = "Organization" to activeCell.FormulaR1C1 = "Evaluations --> "C14"" where D14 also represents a string in a cell?




Sub makemesmarter()


Application.DisplayAlerts = False


'Deletes former dublicates of the copied sheets


For Each S In ActiveWorkbook.Worksheets
If S.Name Like "e_*" Then
S.Delete
End If
Next S
For Each F In ActiveWorkbook.Charts
If F.Name Like "e_*" Then
F.Delete
End If
Next F


'Dublicating the template


Sheets(Array("template inp", "template fig", "template fig 2")).Select
Sheets(Array("template inp", "template fig", "template fig 2")).Copy _
Before:=Sheets("Products -->")
Sheets(Array("template inp", "template fig", "template fig 2")).Select
Sheets(Array("template inp", "template fig", "template fig 2")).Copy _
Before:=Sheets("Products -->")
Sheets(Array("template inp", "template fig", "template fig 2")).Select
Sheets(Array("template inp", "template fig", "template fig 2")).Copy _
Before:=Sheets("Products -->")
Sheets(Array("template inp", "template fig", "template fig 2")).Select
Sheets(Array("template inp", "template fig", "template fig 2")).Copy _
Before:=Sheets("Products -->")

'Renames the new sheets


Sheets("template inp (2)").Name = "e_Org inp"
Sheets("template fig (2)").Name = "e_Org fig"
Sheets("template fig 2 (2)").Name = "e_Org fig 2"
Sheets("template inp (3)").Name = "e_Perf inp"
Sheets("template fig (3)").Name = "e_Perf fig"
Sheets("template fig 2 (3)").Name = "e_Perf fig 2"
Sheets("template inp (4)").Name = "e_Serv inp"
Sheets("template fig (4)").Name = "e_Serv fig"
Sheets("template fig 2 (4)").Name = "e_Serv fig 2"
Sheets("template inp (5)").Name = "e_Know inp"
Sheets("template fig (5)").Name = "e_Knonw fig"
Sheets("template fig 2 (5)").Name = "e_Know fig 2"


'Replaces the B1 Value

Sheets("e_Org inp").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "Organization"

Sheets("e_Perf inp").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "Performance"

Sheets("e_Serv inp").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "Service"

Sheets("e_ESG inp").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "Knowledge"


'Returns to Evaluations sheet

Sheets("Evaluations -->").Select

Application.DisplayAlerts = False

End Sub

Bob Phillips
07-29-2019, 04:19 AM
WorkSheets("template inp (2)").Name = Worksheets("e_"Evaluations").Range("C14").Value

and


Worksheets("e_Org inp").Range("B1).Formula = Worksheets("e_"Evaluations").Range("D14").Value

Paul_Hossler
07-29-2019, 05:41 AM
I have the same question in regard to changing the activeCell.FormulaR1C1 = "Organization" to activeCell.FormulaR1C1 = " Evaluations --> "C14" where D14 also represents a string in a cell?


D14 or C14?

lauritsjust
07-29-2019, 05:54 AM
Thank you, exactly what i was looking for!