gheyman
Well-known Member
- Joined
- Nov 14, 2005
- Messages
- 2,332
- Office Version
- 365
- Platform
- Windows
This button is one Sheet1 (Home). But when I run it using the button, its pasting the tab names down another tab.
What do I need to change to get it to paste the list of tabs in sheet1 / Home tab?
What do I need to change to get it to paste the list of tabs in sheet1 / Home tab?
Code:
Private Sub CommandButton1_Click()
'Clear any Filters in A5
ClearFilterHomeA5
'Clear existing Data first
Sheet1.Range("A6:A7000").Select
Selection.Clear
Range("A6").Select
'Past all Tab Names starting in A6
Dim i As Integer
For i = 1 To ThisWorkbook.Sheets.Count
Sheets(1).Cells(i + 5, 1) = Sheets(i).Name
Next i
'Sort the Tab Names
Sheet1.Range("A6:A665").Select
ActiveWorkbook.Worksheets("Home").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Home").Sort.SortFields.Add Key:=Range("A6:A9"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Home").Sort
.SetRange Range("A5:A665")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A6").Select
'Refresh the Power Query for Quote Database
' ActiveWorkbook.RefreshAll
End Sub