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
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