changing from activewindow to select worksheet

[phil009]

Hi everybody. I currently have a code that will work but only on the active window. I would like it to work on a select worksheet so that you don't have to be looking at the worksheet for the macro to run correctly. My code looks like this:

ChDir "G:\All\Phil"
    Workbooks.Open FileName:="G:\All\Phil\newa.xls"
    Windows("test.xls").Activate
  Workbooks("test.xls").Worksheets("all companies").Cells.ClearContents
  Workbooks("newa.xls").Worksheets("all_companies").[A1].CurrentRegion.Copy _
  Destination:=Workbooks("test.xls").Worksheets("all companies").[A1]
  Workbooks("newa.xls").Close
  Kill "G:\All\Phil\newa.xls"
    
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 36
    ActiveWindow.ScrollColumn = 37
    ActiveWindow.ScrollColumn = 38
    ActiveWindow.ScrollColumn = 39
    ActiveWindow.ScrollColumn = 40
    ActiveWindow.ScrollColumn = 41
    ActiveWindow.ScrollColumn = 42
    ActiveWindow.ScrollColumn = 43
    ActiveWindow.ScrollColumn = 44
    Range("BD1").Select
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 1
    End With
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 1
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveCell.FormulaR1C1 = "Total"
    With ActiveCell.Characters(Start:=1, Length:=5).Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Range("BD2").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-52]:RC[-1])"
    Range("BD2").Select
    Selection.AutoFill Destination:=Range("BD2:BD1100"), Type:=xlFillDefault
    Range("BD2:BD1100").Select
End Sub

So instead of it saying active window I would like it to use the worksheet "all companies", is this possible?
Any help would be much appreciated.

Thanks,
Phil

 

[PHV]

Simply activate the desired sheet before playing wit activewindow.
You may also consider playing with Application.ScreenUpdating to avoid annoying flickers.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

Phil,

Don't know what all the scrolling does...

Sub test()
   ChDir "G:\All\Phil"
   Set wbNewa = Workbooks.Open(Filename:="G:\All\Phil\newa.xls")
   Set wsAllCo = Workbooks("test.xls").Worksheets("all companies")
'    Windows("test.xls").Activate
   wsAllCo.Cells.ClearContents
   wbNewa.Worksheets("all_companies").[A1].CurrentRegion.Copy _
      Destination:=wsAllCo.[A1]
   wbNewa.Close
   Kill "G:\All\Phil\newa.xls"
    
'what does all this scrolling do!!!!!
    
'    ActiveWindow.ScrollColumn = 2
'    ActiveWindow.ScrollColumn = 3
'    ActiveWindow.ScrollColumn = 4
'    ActiveWindow.ScrollColumn = 5
'    ActiveWindow.ScrollColumn = 6
'    ActiveWindow.ScrollColumn = 7
'    ActiveWindow.ScrollColumn = 9
'    ActiveWindow.ScrollColumn = 11
'    ActiveWindow.ScrollColumn = 12
'    ActiveWindow.ScrollColumn = 13
'    ActiveWindow.ScrollColumn = 16
'    ActiveWindow.ScrollColumn = 17
'    ActiveWindow.ScrollColumn = 19
'    ActiveWindow.ScrollColumn = 20
'    ActiveWindow.ScrollColumn = 21
'    ActiveWindow.ScrollColumn = 24
'    ActiveWindow.ScrollColumn = 25
'    ActiveWindow.ScrollColumn = 26
'    ActiveWindow.ScrollColumn = 28
'    ActiveWindow.ScrollColumn = 29
'    ActiveWindow.ScrollColumn = 31
'    ActiveWindow.ScrollColumn = 32
'    ActiveWindow.ScrollColumn = 33
'    ActiveWindow.ScrollColumn = 34
'    ActiveWindow.ScrollColumn = 35
'    ActiveWindow.ScrollColumn = 36
'    ActiveWindow.ScrollColumn = 37
'    ActiveWindow.ScrollColumn = 38
'    ActiveWindow.ScrollColumn = 39
'    ActiveWindow.ScrollColumn = 40
'    ActiveWindow.ScrollColumn = 41
'    ActiveWindow.ScrollColumn = 42
'    ActiveWindow.ScrollColumn = 43
'    ActiveWindow.ScrollColumn = 44
    
   With wsAllCo
       With .Range("BD1")
         With .Interior
           .ColorIndex = 15
           .Pattern = xlSolid
           .PatternColorIndex = xlAutomatic
         .Borders(xlDiagonalDown).LineStyle = xlNone
         .Borders(xlDiagonalUp).LineStyle = xlNone
         With .Borders(xlEdgeLeft)
             .LineStyle = xlContinuous
             .Weight = xlThin
             .ColorIndex = 1
         End With
         .Borders(xlEdgeTop).LineStyle = xlNone
         .Borders(xlEdgeBottom).LineStyle = xlNone
         With .Borders(xlEdgeRight)
             .LineStyle = xlContinuous
             .Weight = xlThin
             .ColorIndex = 1
         End With
         .Borders(xlInsideVertical).LineStyle = xlNone
         .Borders(xlInsideHorizontal).LineStyle = xlNone
          .HorizontalAlignment = xlCenter
          .VerticalAlignment = xlBottom
          .WrapText = False
          .Orientation = 0
          .AddIndent = False
          .ShrinkToFit = False
          .ReadingOrder = xlContext
          .MergeCells = False
         .FormulaR1C1 = "Total"
         With .Characters(Start:=1, Length:=5).Font
             .Name = "Arial"
             .FontStyle = "Regular"
             .Size = 10
             .Strikethrough = False
             .Superscript = False
             .Subscript = False
             .OutlineFont = False
             .Shadow = False
             .Underline = xlUnderlineStyleNone
             .ColorIndex = 1
         End With
       End With
       .Range("BD2").FormulaR1C1 = "=SUM(RC[-52]:RC[-1])"
       .Range("BD2").AutoFill _
         Destination:=.Range("BD2:BD1100"), Type:=xlFillDefault
       .Range("BD2:BD1100").Select
   End With
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[phil009]

I figured it out, I had let excel record this for me and the scrolling was just unnecessary.

Thanks for your help guys,
Phil