0

Please help! The last line of my code

'Sheets("Original Data from Server").Cells(4, 2).Select

Will not work! Keep getting error "Select method of range class failed' Whenever I only run that one line, it works perfect. But whenever I run the rest of the code, I get the error.

NO ONE can seem to help me figure this out!

Sub FormatAdjacencyReport()

Dim iLastRow As Integer '<-- Interger for Counting Number of Rows
Dim iLastColumn As Integer '<-- Interger for Counting Number of Columns
Dim OriginalOutput As Worksheet '<-- Sheet Containing Orignal Report
Dim AdjacencyData As Worksheet '<-- Sheet Crated for Final Output

Set OriginalOutput = ActiveWorkbook.Worksheets(1)
OriginalOutput.Name = "Original Data from Server"

'Determines how many different rows we have in original output
iLastRow = Range("B1").Rows.End(xlDown).Row

'Txt to Columns for Each Row
For i = 2 To iLastRow
    Sheets("Original Data from Server").Cells(i, 5).TextToColumns DataType:=xlDelimited, _
    ConsecutiveDelimiter:=True, Space:=True
Next i

'Create a new sheet for our output
Set AdjacencyData = Sheets.Add
AdjacencyData.Name = "Adjacency Data Output"

'Need to Paste the Entire List of Store Numbers into a New Tab


iLastColumn = Sheets("Original Data from Server").Cells(2, 1).Columns.End(xlToRight).Column
'Sheets("Original Data from Server").Cells(4, 2).Select


End Sub
Community
  • 1
  • 1
Holly Kat
  • 51
  • 1
  • 4
  • Possible duplicate of [Select method of Range class failed via VBA](http://stackoverflow.com/questions/3902818/select-method-of-range-class-failed-via-vba) – GSerg Jan 09 '16 at 14:17

2 Answers2

2

You need to set focus back on Sheets("Original Data from Server") before calling a select on that sheet. So put line 'Sheets("Original Data from Server").Select before the last line. When the line throwing error is being executed, the focus is on another sheet.

Maertin
  • 384
  • 1
  • 8
1

To avoid this kind of errors I suggest you stop working with select and/or activate generally, and only use it when absolutely necessary.
Your code could look like this: 

Sub FormatAdjacencyReport()
    Dim iLastRow As Long '<--  for Counting Number of Rows
    Dim iLastColumn As Long '<--  for Counting Number of Columns
    Dim OriginalOutput As Worksheet '<-- Sheet Containing Orignal Report
    Dim AdjacencyData As Worksheet '<-- Sheet Crated for Final Output

    Set OriginalOutput = ActiveWorkbook.Worksheets(1)
    OriginalOutput.Name = "Original Data from Server"

    'Determines how many different rows we have in original output
    iLastRow = OriginalOutput.Range("B1").End(xlDown).Row  ' changed
    iLastColumn = OriginalOutput.Range("A2").End(xlToRight).Column  ' changed

    'Txt to Columns for Each Row
    For i = 2 To iLastRow
        OriginalOutput.Cells(i, 5).TextToColumns DataType:=xlDelimited, _
        ConsecutiveDelimiter:=True, Space:=True
    Next i

    'Create a new sheet for our output
    Set AdjacencyData = Sheets.Add
    AdjacencyData.Name = "Adjacency Data Output"

    'Need to Paste the Entire List of Store Numbers into a New Tab

    ' OriginalOutput.Activate -- not needed
    ' OriginalOutput.Cells(4, 2).Select  -- not needed
    OriginalOutput.Range("B4:B17").Value = AdjacencyData.Range("A10:A23") ' direct copy!

End Sub

1- work with qualified ranges/cells whenever you work on more than one sheet or workbook. See the line where iLastRow is determined.
2- keep table limit calculation in one place (as soon as the limits are fixed)
3- avoid an explict qualifier (Sheets("Original Data from Server").) in favor of the assigned variable (OrignalOutput). This way you can fully qualify (with workbook name and sheet name) in just one place. Imagine you'd change the sheet name later...
4- use direct assignment from range to range for copying. This circumvents the reference problem (if using qualifiers!), and the clipboard contents are kept intact.

user1016274
  • 4,071
  • 1
  • 23
  • 19