seragrefaat
Board Regular
- Joined
- Nov 16, 2020
- Messages
- 53
- Office Version
- 365
- Platform
- Windows
I want to create and rename number of sheets based on values in range of cells, also after that i want to distribute each value of another list in a fixed cell, B39, of each sheet created.
I have already done the first part but i want someone to modify it to not fixed list (or range), i make the code with the range Q4:Q11, how to make the module executable when the rnge changes to Q4:Q7 or Q4:Q20? Thanks
/
I have already done the first part but i want someone to modify it to not fixed list (or range), i make the code with the range Q4:Q11, how to make the module executable when the rnge changes to Q4:Q7 or Q4:Q20? Thanks
/
VBA Code:
Option Explicit
Sub CreateAndName6s()
Dim c As Range
Application.ScreenUpdating = False
Sheets("Data").Select
For Each c In Range("Q4:Q11")
c.Select
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = c.Value
Sheets("Template").Cells.Copy
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
Range("E4").Select
ActiveSheet.Range("E4").Value = c.Value
Sheets("Data").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=c.Value & "!A1", TextToDisplay:=c.Value
Next c
Application.ScreenUpdating = True
End Sub