-
Notifications
You must be signed in to change notification settings - Fork 0
/
Macro.vbs
69 lines (57 loc) · 1.93 KB
/
Macro.vbs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function
Private Sub InsertSheets_Click()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim wS As Worksheet, sh As Worksheet
Dim Rws As Long, rng As range, c As range
Set sh = Sheets("Codes")
Set wS = Sheets("Default_Test")
With sh
Rws = .Cells(Rows.Count, "A").End(xlUp).Row
Set rng = .range(.Cells(2, 1), .Cells(Rws, 1))
End With
For Each c In rng.Cells
If WorksheetExists(c.Value) Then
MsgBox "Sheet " & c & " exists"
Else:
wS.Copy After:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = "Price_Plan_" & c.Value
End If
Next c
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Private Sub Reset_Click_Click()
Application.DisplayAlerts = False
Dim wS As Worksheet
For Each wS In ThisWorkbook.Sheets
If wS.Name Like "*Price_Plan_*" Then wS.Delete
Next wS
Application.DisplayAlerts = True
End Sub
Private Sub Start_Click_Click()
Dim wS As Worksheet, LastRow As Long
Dim codeArray As Variant
Dim lastCell As String
Dim curSheet As Worksheet
Dim ArraySheets() As String
Dim x As Variant
Set wS = ThisWorkbook.Worksheets("Codes")
'Here we look in Column A
LastRow = wS.Cells(wS.Rows.Count, "A").End(xlUp).Row
'Here we create array of our row values
ThisWorkbook.Sheets("Codes").Activate
codeArray = wS.range("A2:A" & LastRow).Value
'Getting Array of Price Plan Sheets
For Each curSheet In ActiveWorkbook.Worksheets
If curSheet.Name Like "Price_Plan*" Then
ReDim Preserve ArraySheets(x)
ArraySheets(x) = curSheet.Name
x = x + 1
End If
Next curSheet
End Sub