Excel Macro

  • Thread starter Thread starter Frank
  • Start date Start date
F

Frank

I am linking a subtotal workbook to three different
workbooks. I have 72 cells on each of the 3 workbooks to
link to. I have created a macro basically by using the
macro builder in excel, however this takes approximately
72 lines of code for each file because each file has a
different name. Is there a simpler VBA code that I could
use for all three files?

Thanks
Frank
 
Sub TTWks()
'
' Links Macro
' Macro recorded 8/29/2003 by Intuit
'

'
Range("B8").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R63C4"
Range("C6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R38C4"
Range("C7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R40C4"
Range("D6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R42C4"
Range("D7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R44C4"
Range("E6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R46C4"
Range("E7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R48C4"
Range("F6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R50C4"
Range("F7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R52C4"
Range("G6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R54C4"
Range("G7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R56C4"
Range("H6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R58C4"
Range("H7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R60C4"
Range("I8").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R63C5"
'Range("I21").Select
'ActiveWindow.WindowState = xlNormal
'ActiveWindow.WindowState = xlNormal
Range("J6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R38C5"
Range("J7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R40C5"
Range("K6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R42C5"
Range("K7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R44C5"
Range("L6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R46C5"
Range("L7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R48C5"
Range("M6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R50C5"
Range("M7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R52C5"
Range("N6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R54C5"
Range("N7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R56C5"
Range("O6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R58C5"
Range("O7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R60C5"
Range("P8").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R63C6"
'Range("P21").Select
'ActiveWindow.WindowState = xlNormal
'ActiveWindow.WindowState = xlNormal
'ActiveWindow.WindowState = xlNormal
Range("Q6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R38C6"
Range("Q7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R40C6"
Range("R6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R42C6"
Range("R7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R44C6"
Range("S6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R46C6"
Range("S7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R48C6"
Range("T6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R50C6"
Range("T7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R52C6"
Range("U6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R54C6"
Range("U7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R56C6"
Range("V6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R58C6"
Range("V7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R60C6"
Range("W8").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R63C7"
'Range("W21").Select
'ActiveWindow.SmallScroll ToRight:=21
Range("X6").Select
'ActiveWindow.WindowState = xlNormal
'ActiveWindow.WindowState = xlNormal
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R38C7"
Range("X7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R40C7"
Range("Y6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R42C7"
Range("Y7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R44C7"
Range("Z6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R46C7"
Range("Z7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R48C7"
Range("AA6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R50C7"
Range("AA7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R52C7"
Range("AB6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R54C7"
Range("AB7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R56C7"
Range("AC6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R58C7"
Range("AC7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R60C7"
Range("AD6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R63C8"
Range("AD7").Select
'ActiveWindow.SmallScroll ToRight:=8
'ActiveWorkbook.Save
Range("AE6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R38C8"
Range("AE7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R40C8"
Range("AE8").Select
'ActiveWindow.WindowState = xlNormal
'ActiveWindow.WindowState = xlNormal
'ActiveWindow.WindowState = xlNormal
Range("AF6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R42C8"
Range("AF7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R44C8"
Range("AG6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R46C8"
Range("AG7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R48C8"
Range("AH6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R50C8"
Range("AH7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R52C8"
Range("AI6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R54C8"
Range("AI7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R56C8"
Range("AJ6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R58C8"
Range("AJ7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R60C8"
Range("AK6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R63C9"
Range("AK7").Select
'ActiveWindow.SmallScroll ToRight:=7
Range("AL6").Select
'ActiveWorkbook.Save
'ActiveWindow.WindowState = xlNormal
'ActiveWindow.WindowState = xlNormal
'ActiveWindow.WindowState = xlNormal
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R38C9"
Range("AL7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R40C9"
Range("AM6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R42C9"
Range("AM7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R44C9"
Range("AN6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R46C9"
Range("AN7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R48C9"
Range("AO6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R50C9"
Range("AO7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R52C9"
Range("AP6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R54C9"
Range("AP7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R56C9"
Range("AQ18").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R58C9"
Range("AQ19").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R60C9"
Range("AR8").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R63C10"
'Range("AR21").Select
'ActiveWindow.SmallScroll ToRight:=7
'Range("AS18").Select
'ActiveWorkbook.Save
'ActiveWindow.WindowState = xlNormal
'ActiveWindow.WindowState = xlNormal
'ActiveWindow.WindowState = xlNormal
'ActiveWindow.WindowState = xlNormal
'ActiveWindow.WindowState = xlNormal
Range("AS6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R38C10"
Range("AS7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R40C10"
Range("AT6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R42C10"
Range("AT7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R44C10"
Range("AU6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R46C10"
Range("AU7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R48C10"
Range("AV6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R50C10"
Range("AV7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R52C10"
Range("AW6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R54C10"
Range("AW7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R56C10"
Range("AX6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R58C10"
Range("AX7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R60C10"
Range("AY8").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R63C11"
'Range("AY21").Select
'ActiveWindow.SmallScroll ToRight:=7
Range("AZ6").Select
'ActiveWorkbook.Save
'ActiveWindow.WindowState = xlNormal
'ActiveWindow.WindowState = xlNormal
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R38C11"
Range("AZ7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R40C11"
Range("BA6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R42C11"
Range("BA7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R44C11"
Range("BB6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R46C11"
Range("BB7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R48C11"
Range("BC6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R50C11"
Range("BC7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R52C11"
Range("BD6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R54C11"
Range("BD7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R56C11"
Range("BE6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R58C11"
Range("BE7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R60C11"
Range("BF8").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R63C12"
'Range("BF21").Select
'ActiveWindow.SmallScroll ToRight:=7
Range("BG6").Select
'ActiveWorkbook.Save
'ActiveWindow.WindowState = xlNormal
'ActiveWindow.WindowState = xlNormal
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R38C12"
Range("BG7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R40C12"
Range("BH6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R42C12"
Range("BH7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R44C12"
Range("BI6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R46C12"
Range("BI7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R48C12"
Range("BJ6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R50C12"
Range("BJ7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R52C12"
Range("BK6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R54C12"
Range("BK7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R56C12"
Range("BL6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R58C12"
Range("BL7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R60C12"
Range("BM8").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R63C12"
'Range("BM21").Select
'ActiveWindow.SmallScroll ToRight:=7
Range("BN6").Select
'ActiveWorkbook.Save
'ActiveWindow.WindowState = xlNormal
'ActiveWindow.WindowState = xlNormal
'ActiveWindow.WindowState = xlNormal
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R38C13"
Range("BN7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R40C13"
Range("BO6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R42C13"
Range("BO7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R44C13"
Range("BP6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R46C13"
Range("BP7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R48C13"
Range("BQ6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R50C13"
Range("BQ7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R52C13"
Range("BR6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R54C13"
Range("BR7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R56C13"
Range("BS6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R58C13"
Range("BS7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R60C13"
Range("BT8").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R63C14"
'Range("BT21").Select
'ActiveWindow.SmallScroll ToRight:=7
Range("BU6").Select
'ActiveWorkbook.Save
'ActiveWindow.WindowState = xlNormal
'ActiveWindow.WindowState = xlNormal
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R38C14"
Range("BU7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R40C14"
Range("BV6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R42C14"
Range("BV7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R44C14"
Range("BW6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R46C14"
Range("BW7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R48C14"
Range("BX6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R50C14"
Range("BX7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R52C14"
Range("BY6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R54C14"
Range("BY7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R52C14"
Range("BZ6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R58C14"
Range("BZ7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R60C14"
Range("CA8").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R63C15"
'Range("CA21").Select
'ActiveWindow.SmallScroll ToRight:=7
Range("CB6").Select
'ActiveWorkbook.Save
'ActiveWindow.WindowState = xlNormal
'ActiveWindow.WindowState = xlNormal
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R38C15"
Range("CB7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R40C15"
Range("CC6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R42C15"
Range("CC7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R44C15"
Range("CD6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R46C15"
Range("CD7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R48C15"
Range("CE6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R50C15"
Range("CE7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R52C15"
Range("CF6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R54C15"
Range("CF7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R56C15"
Range("CG6").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R58C15"
Range("CG7").Select
ActiveCell.FormulaR1C1 = "='[TTW Cap
ModelDverticaltest1.xls]Vertical'!R60C15"
Range("CG8").Select
End Sub
 
Use this loop

Sub loopwbs()
Dim myarr() As Variant
Dim wbName As Variant
Dim wb As Workbook
myarray = Array("wb1.xls", "wb2.xls", "wb3.xls")
For Each wbName In myarray
Set wb = Workbooks.Open(ThisWorkbook.Path & "\" _
& wbName)
wb.Activate

With Sheets("sheet1")
'=====code below
' .Range("a1").Formula = "=1*2"
..Range("B8").Formula= "='[TTW Cap ModelDverticaltest1.xls]Vertical'!R63C4"
..Range("C6").Formula= "='[TTW Cap ModelDverticaltest1.xls]Vertical'!R38C4"
..Range("C7").Formula= "='[TTW Cap ModelDverticaltest1.xls]Vertical'!R40C4"
..Range("D6").Formula= "='[TTW Cap ModelDverticaltest1.xls]Vertical'!R42C4"
..Range("D7").Formula = "='[TTW Cap ModelDverticaltest1.xls]Vertical'!R44C4"
'======code above
End With

wb.Close True
Next wbName
End Sub


 
Back
Top