I recently had a problem, I had a summary sheet which was calculating all of the data between a beginning sheet and an end sheet. However, the sheets in-between the begin and end tabs were changing based on a selection from a list. What I wanted was the flexibility to move the sheets in and out based on what had been selected in this list. The summary will be updating once the procedure runs and the end result needs to be flexible.
Once again we will need VBA to produce a result. Firstly I will move the end sheet next to the start sheet. Then it is just a matter of putting all of the sheets inbetween these two tabs.
The data in the list is a sample of what will be pushed inbetween my Beginning and End tabs. My example file has 6 sheets, these are:
NSW
TAS
QLD
SA
WA
VIC
The general idea is if NSW, TAS and QLD appear in the list above then after the procedure has run the file will look as follows.
Notice how only the sheets from our drop down are included between the two blue sheets. The ramifications are in the summary sheet where only the data inside the blue tabs will be summariesd. SA, WA and VIC in the above example are omitted.
The following is the VBA coding to achieve the task.
Sub Move() 'Excel VBA code to move sheets inside Begin and End tabs
Dim i As Integer
Sheet5.Move after:=Sheet2
For i = 1 To Range("B" & Rows.Count).End(xlUp).Row - 10
Sheets(CStr(Sheet1.Range("B" & i + 10))).Move after:=Sheet2
Next i
End Sub
As the list starts in row 11 I will need to take 10 lines off the dynamic upper bound range of the loop. This will give the code the abilty to pick up every sheet in the loop.
The Excel file attached allows you to check the workings of the above procedure.
