none
how to activate "sheet1" after a named workbook was opened and activated RRS feed

  • Question

  • would like to copy rows from an external sheet to "sheet1" of the workbook that contains the running macro.

    I open and activate the extenal sheet. Then I locate the row to start copying from:

      Dim excelFile As String
      excelFile = "lae orders.xlsx"
      Workbooks.Open "c:\SkyDrive\excel\" & excelFile
      
      ' activate the workbook
      Workbooks(excelFile).Sheets("New Rel").Activate
      
      ' find first detail line
      i = 1
      Do While True
        Set topLine = ActiveSheet.Cells(i, 1)
        If IsNumeric(topLine.Value) = True Then
          Exit Do
        End If
        i = i + 1
      Loop
    

    At this point I want to set "sheet1" of the workbook running the macro to be the "active" sheet. How do I do that. I am getting errors on variations of the following:

    Sheets("Sheet1").Activate

    thanks,

    Friday, September 14, 2012 12:25 AM

Answers

  • Remember, you rarely need to activate - you could set an object (see below)

    Sub t()
    Dim excelFile As String
    Dim Wb As Workbook
    Dim ws As Worksheet

      excelFile = "lae orders.xlsx"
      Set Wb = Workbooks.Open("c:\SkyDrive\excel\" & excelFile)
     
      ' activate the worksheet
      Set ws = Wb.Sheets("New Rel")
     
      'No need to loop - if you are looking for the first number
      ' find first detail line (if your numbers are not formulas)
      i = ws.Range("A:A").SpecialCells(xlCellTypeConstants, 1).Row
     
      ' find first detail line (if your numbers ARE from formulas)
      'i = ws.Range("A:A").SpecialCells(xlCellTypeFormulas, 1).Row
     
      'To select sheet1 of the workbook with the code

    'to use the ws object
      Set ws = ThisWorkbook.Sheets("Sheet1") 

    'used like ws.Cells(i,1).Value = "This is cell A1"

      ' or if you really want to activate
      ThisWorkbook.Activate
      ThisWorkbook.Worksheets("Sheet1").Activate
    End Sub

    Friday, September 14, 2012 1:15 AM
  • Activate and Select make macro working much slower. Unfortunately it is recorded by the wizard.

    As Bernie wrote, assign Set to use range by name variable.

    p.s.
    If you want to move across sheets I can recommend you small add-in: Move between worksheets active workbook Add-in


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    • Marked as answer by Steve Richter Friday, September 14, 2012 2:56 PM
    Friday, September 14, 2012 8:41 AM
    Answerer

All replies

  • Remember, you rarely need to activate - you could set an object (see below)

    Sub t()
    Dim excelFile As String
    Dim Wb As Workbook
    Dim ws As Worksheet

      excelFile = "lae orders.xlsx"
      Set Wb = Workbooks.Open("c:\SkyDrive\excel\" & excelFile)
     
      ' activate the worksheet
      Set ws = Wb.Sheets("New Rel")
     
      'No need to loop - if you are looking for the first number
      ' find first detail line (if your numbers are not formulas)
      i = ws.Range("A:A").SpecialCells(xlCellTypeConstants, 1).Row
     
      ' find first detail line (if your numbers ARE from formulas)
      'i = ws.Range("A:A").SpecialCells(xlCellTypeFormulas, 1).Row
     
      'To select sheet1 of the workbook with the code

    'to use the ws object
      Set ws = ThisWorkbook.Sheets("Sheet1") 

    'used like ws.Cells(i,1).Value = "This is cell A1"

      ' or if you really want to activate
      ThisWorkbook.Activate
      ThisWorkbook.Worksheets("Sheet1").Activate
    End Sub

    Friday, September 14, 2012 1:15 AM
  • Activate and Select make macro working much slower. Unfortunately it is recorded by the wizard.

    As Bernie wrote, assign Set to use range by name variable.

    p.s.
    If you want to move across sheets I can recommend you small add-in: Move between worksheets active workbook Add-in


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    • Marked as answer by Steve Richter Friday, September 14, 2012 2:56 PM
    Friday, September 14, 2012 8:41 AM
    Answerer