RunTime Error 91 (Leo Asked)

[Leo]

Dear Experts,
I have a worksheet with full of formulas, whose data parts need to be filled
by other users , and then I have to transfer this data to my worksheet.
I created the below Sub to copy all data , except formula cells, to my
original sheet at once, without selecting data parts and copy them one by one.
'====
Sub CopyValues()
Dim SourceSheet As Worksheet
Dim TargetSheet As Worksheet
Dim TargetRange As Range
Dim c As Range
Dim stAddress As String
'target sheet is selected by user
Set TargetRange = Application.InputBox(Prompt:="Go to Target WorkSheet!",
Title:="Target?", Type:=8)
Set TargetSheet = TargetRange.Parent

For Each c In SourceSheet.Range("A1:X2000")
If Not c.HasFormula Then
stAddress = c.Address
TargetSheet.Range(stAddress).Value = c.Value
End If
Next c


End Sub
'====
on the line of setting TargetSheet, I encountered error # 91, which I do not
know the reason.
Could you please tell me what is wrong, and how to correct???

 

[Howard31]

Hi Leo,

When the user gets the InputBox asking to select a sheet as the prompt says
"Go to Target WorkSheet!" all he needs to do is select a sheet he can do this
with out selecting any range which will cause 2 problems 1. If you chose 8
for the type argument of InputBox method then it has to reurn a valid Range.
2. If it doesn't return a range then the return value i.e. TargetRange is not
set to a Range than when you want to Set TargetSheet = TargetRange.Parent it
causes an error because the Parent can't be a sheet if the TargetRange is
Nothing. Also you have to keep in mind that the user can choose to Cancel the
InputBox and that will cause a error. Ideally you should create a UserForm
for this but, if you want to use InputBox you can refrase the Prompt as Go to
Target Sheet and select a range, also in case the user cancels the InputBox
you should put On Error Resume Next before Setting TargetRange, after setting
TargetRange you should check that TargetRange is NOT Nothing before Setting
TargetSheet as follows:

On Error Resume Next

Set TargetRange = Application.InputBox(Prompt:="Go to Target WorkSheet, And
Select A Cell!",Title:="Target?", Type:=8)

If NOT TargetRange Is Nothing Then
Set TargetSheet = TargetRange.Parent
Rest of the code....
Else
MsgBox You have not selected any cells
End If

Another aproach can be that instead of using Type 8 use Type 2 Which lets
the user enter a Text, so the user can enter the name of a sheet and do away
with TargetRange, as follows:

Dim ShtName As String

ShtName = Application.InputBox(Prompt:="Go to Target
WorkSheet!",Title:="Target?", Type:=2)

Set TargetSheet = ThisWorkbook.Worksheets(ShtName)

Of course if you use this method you have to provide error handling in case
the user enters text which doesn't correspond to ant sheet or as before the
user cancels

Hope I didn't drag on to much and, hope this helps

 

[Don Guillett]

Adapt this simplified version to your needs.

Option Explicit
Sub copynonformulas()
Dim c As Range
For Each c In Range("c7:c11")
If Not c.HasFormula Then
Sheets("sheet2").Range(c.Address).Value = c.Value
End If
Next c
End Sub

 

[Don Guillett]

To ask for the destination sheet.

Sub copynonformulas()
Dim mysheet As String
Dim c As Range
mysheet = InputBox("which sheet")
For Each c In Range("c7:c11")
If Not c.HasFormula Then
Sheets(mysheet).Range(c.Address).Value = c.Value
End If
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Adapt this simplified version to your needs.

Option Explicit
Sub copynonformulas()
Dim c As Range
For Each c In Range("c7:c11")
If Not c.HasFormula Then
Sheets("sheet2").Range(c.Address).Value = c.Value
End If
Next c
End Sub

 

[Leo]

thanks a lot,
I suppose the second one suits my purpose, where typing the name of sheet is
asked and less headache on error handling.