Method 'Range' of object '_Global' failed 2

[allenEd]

When I use the code below (to copy from sheet 2 to sheet 1, certain cells), it works fine. If I change the names of the sheets, (and amend the code), I get the error message. Can anyone put some light on this.

(thanks to Zathras for code)
Thanks
Allen

Sheet1:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Count = 1 Then
Selection1 = "Sheet1!" + Target.Address
End If
End Sub

Sheet2:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Count = 1 Then
Selection2 = "Sheet2!" + Target.Address
End If
End Sub

Insert a module:

Module1:
Option Explicit
Public Selection1 As String
Public Selection2 As String

Sub CopyThreeCells()
Dim FromCell As Range
Dim ToCell As Range
Set FromCell = Range(Selection2)
Set ToCell = Range(Selection1)
ToCell.Value = FromCell.Value
ToCell.Offset(-10, 0).Value = FromCell.Offset(-10, 0).Value
ToCell.Offset(-20, 0).Value = FromCell.Offset(-20, 0).Value
Set ToCell = Nothing
Set FromCell = Nothing
End Sub

Select Sheet2 and enter the following three cells:

E40: 'This is from E40
E50: 'This is from E50
E60: 'This is from E60

 

[Nikita6003]

allened,

I think this has probably to do with the fact that the 2 public variables

Selection1

and

Selection2

are set using the OnSelectionChange event of the Worksheets Sheet1 and Sheet2
When you open the worksheet & run the macro straight off, you'll get the error because the two public variables haven´t been set - i.e. have no value
When you change the names of the sheets _ update the macro accordingly, the two public variables need to be set to the new sheet names - so you have to move the cursor on both sheets to make your macro work

HTH

Cheers
Nikki

 

[allenEd]

Nikki

You are right, it is the OnSelectionChange event..... How do I set the variable to the sheet names?

thanks
Allen

 

[Nikita6003]

allenEd,

I'd suggest adding this function to your module (where you've got the

CopyThreeCells

subroutine) :

Function ExistsSelection(p_sSelection As String) As Boolean
    'Set to FALSE - assume selection is incorrect
    ExistsSelection = False
    
    'Set error-handler - this merely quits the function & returns FALSE to the calling macro
    On Error GoTo Err_ExistsSelection
    
    'Check to see if selection exists
    Range(p_sSelection).Select
    
    'No error, therefore p_sSelection points to a valid selection!
    'Return TRUE & continue calling macro
    ExistsSelection = True
    
    Exit Function
Err_ExistsSelection:
End Function

Now add the following lines to the

CopyThreeCells

module:

Before this line

  Set FromCell = Range(Selection2)

add:

  'If selection isn't valid set to valid cell
  If Not (ExistsSelection(Selection2)) Then Selection2 = ThisWorkbook.Sheets(2).Name & "!" & ThisWorkbook.Sheets(2).Cells(40, 5).Address

Before this line

  Set FromCell = Range(Selection1)

add:

  'If selection isn't valid set to valid cell
  If Not (ExistsSelection(Selection1)) Then Selection1 = ThisWorkbook.Sheets(1).Name & "!" & ThisWorkbook.Sheets(1).Cells(40, 5).Address

PLZ NOTE: This changes the selection on Sheet1 AND on Sheet2 to cell E40 - dunno if you want to use another default cell. If so, just substite the correct row and column numbers ;-)
HTH!

Cheers
Nikki

 

[Zathras]

I was afraid you might be changing sheet names and run into problems. Here is an alternative method that does not process any sheet events, all it needs is to be placed in a module:

Option Explicit

Sub Copy3Cells()

' Copy three cells from the most recent selection on one sheet
' to the current selection on the current sheet

Dim MySheet As String
Dim FromSheet As String
Dim FromCell As Range
Dim ToCell As Range

FromSheet = "Sheet2"

  '<----- Change here when necessary

MySheet = ActiveSheet.Name
Set ToCell = Selection
Application.ScreenUpdating = False
Sheets(FromSheet).Activate
Set FromCell = Selection
Sheets(MySheet).Activate
Application.ScreenUpdating = True
If ToCell.Count = 1 And FromCell.Count = 1 Then
  ToCell.Value = FromCell.Value
  ToCell.Offset(-10, 0).Value = FromCell.Offset(-10, 0).Value
  ToCell.Offset(-20, 0).Value = FromCell.Offset(-20, 0).Value
Else
  If ToCell.Count <> 1 Then
    MsgBox &quot;You must select a single cell to copy into.&quot;
  Else
    MsgBox &quot;You must select a single cell for copy from.&quot;
  End If
End If
Set ToCell = Nothing
Set FromCell = Nothing

End Sub

You should add more error traping code such as being sure the rows selected are within an appropriate range.

 

[allenEd]

Thanks Zathras,
That's magic.

Also thanks to Nikki.

You guys now your stuff, and this forum is No1.

Thanks again allen.