VBA Loop through selected Worksheets 3

[TekNewby]

Let me preface this question by saying I am brand new to VBA…

I am trying to loop a macro through selected worksheets in an Excel workbook. I am currently trying this on a small scale with the following example. I have created 8 worksheets, named A, B, C, D, E, F, G, and H. I have entered these names in cells A1:A8 on worksheet H, and I have given the range of cells A3:A6 (containing C-F) the name Beta. For this simple exercise, I want to run a macro that will insert the letter X in cell C1 only on the worksheets included in the named range (C-F).

I have looked at several different questions and examples related to VBA loops and have tried taking bits and pieces from them to perform this function. Here is my latest attempt:

Sub

Dim wksht As Worksheet
Dim i As String
Dim Beta() 'This is an array definition
i = C
For Each wksht In ActiveWorkbook.Worksheets
i = i + 1
ReDim Preserve Beta(C To i)
Beta(i) = wksht.Name
Next wksht

For i = LBound(Beta) To UBound(Beta)
wksht.Range("C1").Value = X

Next i
End Sub


Sidenote: The function I actually want to perform in selected worksheets is regression analysis. Where in the loop code would I insert this command?

I would greatly appreciate any guidance you experts out there could provide. Thanks!

 

[SkipVought]

Hi,

Don't really need all that...

dim ws as worksheet
for each ws in worksheets
  with ws
    select case ws.name
      case "A","B","C","D","E","F","G","H"
         'now do something with this sheet
         For i = LBound(Beta) To UBound(Beta)
             .Range("C1").Value = X  'but you never define what X is???

         Next i
    end with
  end with
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TekNewby]

Thanks for such a quick reply, Skip.

X is simply the letter I want to be inserted in cell C1 of worksheets C-F (Cells A3:A6 on ws H, the range I named Beta).

Is this what the code above is supposed to do? I tried running it and did not see any results.

 

[SkipVought]

The reason that you saw NOTHING is that ther was NOTHING in X. X was being interpreted as a VARIABLE.

dim ws as worksheet
for each ws in worksheets
  with ws
    select case .name
      case [b]"C","D","E","F"[/b]
         'now do something with this sheet
         .Range("C1").Value = [b]"X"[/b]
    end select
  end with
next

and I had missed the End Select statement.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TekNewby]

Skip,

Thank you for that explanation. The code ran successfully and transferred beautifully over to my regression file.

Thanks!

 

[TekNewby]

Skip,

The large macro that I am trying to run does not appear to run on every tab. Is it possible that my coding is in a format that does not work with the loop? The macro piece runs on the tab I am on, but not on the other tabs. I am pasting the code I'm trying to loop through below.

Thanks for all your help! By the way? How do you get the cool window for your code on your post?

TN
----------------------------------------------------------
Range("AI3:AR400").Select
Selection.ClearContents
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$b$54:$b$90"), ActiveSheet.Range("$q$54:$ab$90"), False, True, , ActiveSheet.Range("$ai$3") _
, False, False, False, False, , False
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$e$54:$e$90"), ActiveSheet.Range("$q$54:$ab$90"), False, True, , ActiveSheet.Range("$ai$36") _
, False, False, False, False, , False
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$f$54:$f$90"), ActiveSheet.Range("$q$54:$ab$90"), False, True, , ActiveSheet.Range("$ai$69") _
, False, False, False, False, , False
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$g$54:$g$90"), ActiveSheet.Range("$q$54:$ab$90"), False, True, , ActiveSheet.Range("$ai$102") _
, False, False, False, False, , False
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$i$54:$i$90"), ActiveSheet.Range("$q$54:$ab$90"), False, True, , ActiveSheet.Range("$ai$135") _
, False, False, False, False, , False
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$j$54:$j$90"), ActiveSheet.Range("$q$54:$ab$90"), False, True, , ActiveSheet.Range("$ai$168") _
, False, False, False, False, , False
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$l$54:$l$90"), ActiveSheet.Range("$q$54:$ab$90"), False, True, , ActiveSheet.Range("$ai$201") _
, False, False, False, False, , False
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$n$54:$n$90"), ActiveSheet.Range("$q$54:$ab$90"), False, True, , ActiveSheet.Range("$ai$234") _
, False, False, False, False, , False
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$k$54:$k$90"), ActiveSheet.Range("$q$54:$ab$90"), False, True, , ActiveSheet.Range("$ai$267") _
, False, False, False, False, , False
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$h$54:$h$90"), ActiveSheet.Range("$q$54:$ab$90"), False, True, , ActiveSheet.Range("$ai$300") _
, False, False, False, False, , False

'This section of code runs regressions on the revenue data, plugging in the appropriate sales data according to channel.
Range("AS4:BB400").Select
Selection.ClearContents
Range("O3").Select
ActiveCell.FormulaR1C1 = "Total Sales"
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$b$3:$b$39"), ActiveSheet.Range("$o$3:$ab$39"), False, True, , ActiveSheet.Range("$as$4") _
, False, False, False, False, , False
Range("O3").Select
ActiveCell.FormulaR1C1 = "AGENCY"
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$e$3:$e$39"), ActiveSheet.Range("$o$3:$ab$39"), False, True, , ActiveSheet.Range("$as$39") _
, False, False, False, False, , False
Range("O3").Select
ActiveCell.FormulaR1C1 = "CO ATO"
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$f$3:$f$39"), ActiveSheet.Range("$o$3:$ab$39"), False, True, , ActiveSheet.Range("$as$74") _
, False, False, False, False, , False
Range("O3").Select
ActiveCell.FormulaR1C1 = "CO CTO"
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$g$3:$g$39"), ActiveSheet.Range("$o$3:$ab$39"), False, True, , ActiveSheet.Range("$as$109") _
, False, False, False, False, , False
Range("O3").Select
ActiveCell.FormulaR1C1 = "CO RES"
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$j$3:$j$39"), ActiveSheet.Range("$o$3:$ab$39"), False, True, , ActiveSheet.Range("$as$144") _
, False, False, False, False, , False
Range("O3").Select
ActiveCell.FormulaR1C1 = "CO WEB"
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$k$3:$k$39"), ActiveSheet.Range("$o$3:$ab$39"), False, True, , ActiveSheet.Range("$as$179") _
, False, False, False, False, , False
Range("O3").Select
ActiveCell.FormulaR1C1 = "EXP & INTERNET"
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$l$3:$l$39"), ActiveSheet.Range("$o$3:$ab$39"), False, True, , ActiveSheet.Range("$as$214") _
, False, False, False, False, , False
Range("O3").Select
ActiveCell.FormulaR1C1 = "OTHER AIRLINE"
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$m$3:$m$39"), ActiveSheet.Range("$o$3:$ab$39"), False, True, , ActiveSheet.Range("$as$249") _
, False, False, False, False, , False
Range("O3").Select
ActiveCell.FormulaR1C1 = "CO GSA"
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$i$3:$i$39"), ActiveSheet.Range("$o$3:$ab$39"), False, True, , ActiveSheet.Range("$as$284") _
, False, False, False, False, , False
Range("O3").Select
ActiveCell.FormulaR1C1 = "OTHER"
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$h$3:$h$39"), ActiveSheet.Range("$o$3:$ab$39"), False, True, , ActiveSheet.Range("$as$319") _
, False, False, False, False, , False
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$n$3:$n$39"), ActiveSheet.Range("$o$3:$ab$39"), False, True, , ActiveSheet.Range("$as$354") _
, False, False, False, False, , False

'This section of code fills in the labels for each regression table.
Range("AI35").Select
ActiveCell.FormulaR1C1 = "Sales - Agency"
Range("AI68").Select
ActiveCell.FormulaR1C1 = "Sales - ATO"
Range("AI101").Select
ActiveCell.FormulaR1C1 = "Sales - CTO"
Range("AI134").Select
ActiveCell.FormulaR1C1 = "Sales - Reservations"
Range("AI167").Select
ActiveCell.FormulaR1C1 = "Sales - CO.Com"
Range("AI200").Select
ActiveCell.FormulaR1C1 = "Sales - Internet"
Range("AI233").Select
ActiveCell.FormulaR1C1 = "Sales - OAL"
Range("AI266").Select
ActiveCell.FormulaR1C1 = "Sales - Expedia"
Range("AI299").Select
ActiveCell.FormulaR1C1 = "Sales - GSA"
Range("AS38").Select
ActiveCell.FormulaR1C1 = "Agency"
Range("AS73").Select
ActiveCell.FormulaR1C1 = "ATO"
Range("AS108").Select
ActiveCell.FormulaR1C1 = "CTO"
Range("AS143").Select
ActiveCell.FormulaR1C1 = "Reservations"
Range("AS178").Select
ActiveCell.FormulaR1C1 = "CO.Com"
Range("AS213").Select
ActiveCell.FormulaR1C1 = "Internet"
Range("AS248").Select
ActiveCell.FormulaR1C1 = "OAL"
Range("AS283").Select
ActiveCell.FormulaR1C1 = "GSA"
Range("AS318").Select
ActiveCell.FormulaR1C1 = "Direct Connect"
Range("AS353").Select
ActiveCell.FormulaR1C1 = "Unknown"

Range("C1").Select

 

[TekNewby]

Skip,

I figured out the ActiveSheet thing - duh!!!

TN

 

[mscallisto]

If you Preview Post before you Submit Post, you can scroll down to see the following:

* EDITING TIPS

Use TGML (Tecumseh Group Mark-up Language) to format your posts to bold, underline, indent, color, and cursive your text. See examples below. Click Here for the full list of TGML tags and click Here for the full list of Emoticons/Smileys

good luck

 

[SkipVought]

Yes, use the Worksheet Object and a range reference, rather than Active...whatever.

Also

'not this
    Range("AI35").Select
    ActiveCell.FormulaR1C1 = "Sales - Agency"
        Range("AI68").Select
    ActiveCell.FormulaR1C1 = "Sales - ATO"

'rather this
   [b]With [i]SomeSheetObject[/i][/b]
       .Range("AI35").Value = "Sales - Agency"
       .Range("AI68").Value = "Sales - ATO"

   [b]End With[/b]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}