Select Various tabs using VBA

[Chris]

Hello,

I am working on a Macro that is very simple for the most part. The purpose
of the Macro is to select a tab and certain range of cells and then paste
values of this data a few rows lower.

My question is that this Macro will need to be able to be performed on
multiple tabs. While I could write the macro to select Tab A, then once
complete select Tab B. Unfortunately some of the users who will use this
macro will have more tabs than others.

So in this example, lets assume that there are 5 tabs in the workbook.

Tab 1 = "Data" (This tab should not be touched)
Tab 2 = "Basic"
Tab 3 = "2-15"
Tab 4 = "4-15"
Tab 5 = "7-15"

The following code will need to be performed on each tab except the Data tab:


Range("C5:EK5").Select
Application.CutCopyMode = False
Selection.Copy
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("C11:EK11").Select
Application.CutCopyMode = False
Selection.Copy
Range("C12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("C27:EK27").Select
Application.CutCopyMode = False
Selection.Copy
Range("C28").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


I have been thinking that maybe it would be best to try to bring up a msg
box to check off the tabs that would be executed. Using this thought
process, I found a post from Bob that has the following code that I am sure
part of it could definately work:

Sub TestBrowseSheets()
MsgBox BrowseSheets
End Sub

'----------------------------------------------------------------
Function BrowseSheets()
'----------------------------------------------------------------
Const nPerColumn As Long = 35 'number of items per column
Const nWidth As Long = 7 'width of each letter
Const nHeight As Long = 18 'height of each row
Const sID As String = "___SheetSelect" 'name of dialog sheet
Const kCaption As String = " Select sheet to goto"
'dialog caption
Dim i As Long
Dim TopPos As Long
Dim iBooks As Long
Dim cCols As Long
Dim cLetters As Long
Dim cMaxLetters As Long
Dim iLeft As Long
Dim thisDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox

Application.ScreenUpdating = False

If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Function
End If

On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.DialogSheets(sID).Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set CurrentSheet = ActiveSheet
Set thisDlg = ActiveWorkbook.DialogSheets.Add

With thisDlg

.Name = sID
.Visible = xlSheetHidden

'sets variables for positioning on dialog
iBooks = 0
cCols = 0
cMaxLetters = 0
iLeft = 78
TopPos = 40

For i = 1 To ActiveWorkbook.Worksheets.Count

If i Mod nPerColumn = 1 Then
cCols = cCols + 1
TopPos = 40
iLeft = iLeft + (cMaxLetters * nWidth)
cMaxLetters = 0
End If

Set CurrentSheet = ActiveWorkbook.Worksheets(i)
cLetters = Len(CurrentSheet.Name)
If cLetters > cMaxLetters Then
cMaxLetters = cLetters
End If

iBooks = iBooks + 1
.CheckBoxes.Add iLeft, TopPos, cLetters * nWidth, 16.5
.CheckBoxes(iBooks).Text = _
ActiveWorkbook.Worksheets(iBooks).Name
TopPos = TopPos + 13

Next i

.Buttons.Left = iLeft + (cMaxLetters * nWidth) + 24

CurrentSheet.Activate

With .DialogFrame
.Height = Application.Max(68, _
Application.Min(iBooks, nPerColumn) * nHeight + 10)
.Width = iLeft + (cMaxLetters * nWidth) + 24
.Caption = kCaption
End With

.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront

Application.ScreenUpdating = True
If .Show Then
For Each cb In thisDlg.CheckBoxes
If cb.Value = xlOn Then
BrowseSheets = cb.Caption
Exit For
End If
Next cb
Else
MsgBox "Nothing selected"
End If

Application.DisplayAlerts = False
.Delete

End With

End Function



Unfortunately I am pretty new to VBA so I am having trouble trying to
combine this all. Any suggestions?

Thanks.

 

[Gary Keramidas]

i don't know exactly what you're trying to do, but i would try to keep the
sheets in some kind of order, with data always being first. then maybe add a
sheet called end that is blank at the end. then you could perform your macro on
all of the sheets in between.

this may help, maybe not:

dim i as long
For i = 2 To Worksheets.Count - 1
Worksheets(i).Activate
Worksheets(i).Range("a1").Value = 40 'or something like this
' your code here
Next

 

[Nigel]

When you say "be able to perform on multiple tabs" do you mean automatically
or one at a time? You could for example change the code to refer to the
ActiveSheet, and trigger your code in the sheet activate event, or you might
consider using sheet index number rather than tab names, but it all depends
on when and how you wish the code to run.

 

[Stopher]

Why not put the code that you have in a seperate sub then in a new sub
simply

Sub PerformCalc()

Sheets("{insert sheet name here}").select

{Insert name of Calc sub here}

Sheets("{insert sheet name here}").select

{Insert name of Calc sub here}

.....
......
.....

Stopher

 

[Stopher]

Tab 1 = "Data" (This tab should not be touched)
Tab 2 = "Basic"
Tab 3 = "2-15"
Tab 4 = "4-15"
Tab 5 = "7-15"

So:

Sub PerforCalc()

Sheets("Basic").select

TheFormatCalcThing

Sheets("2-15").select

TheFormatCalcThing

Sheets("4-15").select

TheFormatCalcThing

Sheets("7-15").select

TheFormatCalcThing

End Sub

Sub TheFormatCalcThing()
Range("C5:EK5").Select
Application.CutCopyMode = False
Selection.Copy
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("C11:EK11").Select
Application.CutCopyMode = False
Selection.Copy
Range("C12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("C27:EK27").Select
Application.CutCopyMode = False
Selection.Copy
Range("C28").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

End Sub

That way if somehow you get the sheet orders changed somehow, the calcs
will only ever ne on those specifc sheets.

Stopher

 

[Chris]

Thanks for all of your suggestions. Essentially would like to perform the
operation on all tabs except basic. The reason I am not including the tab
name in the code ist aht the tab names may not be the wame from worksheet to
worksheet.

How would I like this to perform? Honestly, I think taht Tab 2 would be
executed, then tab 3 would be selected and executed and then tab 4 etc. I do
not know that since the data will be different in each tab that all of the
tabs could be executed all at once.

I would exepect the exec to be done automatically. Essentially once I get
over this hurdle, I will write this code so that when the VBA is started,
this process will be done on every sorkbook within a directory. Generally
there are 40-50 files for this to be done on so as you can see I am hoping to
be able to execute this macro on all 50 workbooks with one click. I can
handle that part. I am just having trouble with the dynamic tabs portion.

thanks.

 

[Tom Ogilvy]

If you don't want to process Basic or Date, then

Dim sh as Worksheet
for each sh in Activeworkbook.Worksheets
if sh.name <> "Basic" and sh.Name <> "Data" then
sh.Activate
Range("C5:EK5").Select
Application.CutCopyMode = False
Selection.Copy
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C11:EK11").Select
Application.CutCopyMode = False
Selection.Copy
Range("C12").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C27:EK27").Select
Application.CutCopyMode = False
Selection.Copy
Range("C28").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End if
Next
End sub

 

[Don Wiss]

The following code will need to be performed on each tab except the Data tab:

Range("C5:EK5").Select
Application.CutCopyMode = False
Selection.Copy
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("C11:EK11").Select
Application.CutCopyMode = False
Selection.Copy
Range("C12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("C27:EK27").Select
Application.CutCopyMode = False
Selection.Copy
Range("C28").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

When I see glurpy code like this I know it was recorded. I'd simplify it
to:

Range("C5:EK5").Copy
Range("C6").PasteSpecial Paste:=xlPasteValues
Range("C11:EK11").Copy
Range("C12").PasteSpecial Paste:=xlPasteValues
Range("C27:EK27").Copy
Range("C28").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Much easier to read.

Don <www.donwiss.com> (e-mail link at home page bottom).