I have the following routine to copy a cell in the named range 'One', paste
the value into cell B4 of worksheet 'Trust', print the named range 'Two' and
then repeat this procedure for all of the cells in the named range 'One'. I
don't think this makes a difference, but the named ranges are on different
sheets. I am receiving a run-time error 13 - type mismatch. Not sure, but
the values to be copied from 'One' to cell B4 are stored as text.
Is there an easy fix to my macro?

Sub printAll()
Dim i
Dim myCount
myCount = Range("One")
For i = 0 To myCount
With Range("One")
.Copy
.Worksheets("Trust").Range("B4").Value
.Range("Two").Print
End With
If i = myCount Then
End If
Next i
End Sub

Your code is a long way off from what you describe. So you want to copy the
individual values in range One into a specific cell on Sheet Trust and then
print out a range from that sheet??? Assuming that to be the case this will
be a lot closer to what you have asked...

dim rng as range

for each rng in worksheets("Sheet1").Range("One")
with worksheets("Trust")
.range("B4").value = rng.value
.range("Two").PrintPreview 'Change to printout
end with
next rng
--
HTH...

Jim Thomlinson


"iperlovsky" wrote:

> I have the following routine to copy a cell in the named range 'One', paste
> the value into cell B4 of worksheet 'Trust', print the named range 'Two' and
> then repeat this procedure for all of the cells in the named range 'One'. I
> don't think this makes a difference, but the named ranges are on different
> sheets. I am receiving a run-time error 13 - type mismatch. Not sure, but
> the values to be copied from 'One' to cell B4 are stored as text.
> Is there an easy fix to my macro?
>
> Sub printAll()
> Dim i
> Dim myCount
> myCount = Range("One")
> For i = 0 To myCount
> With Range("One")
> .Copy
> .Worksheets("Trust").Range("B4").Value
> .Range("Two").Print
> End With
> If i = myCount Then
> End If
> Next i
> End Sub

Maybe...

Option Explicit
Sub printAll()
Dim myCell As Range
Dim OneRng As Range

Set OneRng = ActiveWorkbook.Names("One").RefersToRange

For Each myCell In OneRng.Cells
myCell.Copy _
Destination:=Worksheets("Trust").Range("b4")
ActiveWorkbook.Names("Two").RefersToRange.PrintOut preview:=True
Next myCell

End Sub

After you get it working, delete that Preview:=true stuff.

iperlovsky wrote:
>
> I have the following routine to copy a cell in the named range 'One', paste
> the value into cell B4 of worksheet 'Trust', print the named range 'Two' and
> then repeat this procedure for all of the cells in the named range 'One'. I
> don't think this makes a difference, but the named ranges are on different
> sheets. I am receiving a run-time error 13 - type mismatch. Not sure, but
> the values to be copied from 'One' to cell B4 are stored as text.
> Is there an easy fix to my macro?
>
> Sub printAll()
> Dim i
> Dim myCount
> myCount = Range("One")
> For i = 0 To myCount
> With Range("One")
> .Copy
> .Worksheets("Trust").Range("B4").Value
> .Range("Two").Print
> End With
> If i = myCount Then
> End If
> Next i
> End Sub

--

Dave Peterson

Yes, your code is correct and efficient. Thanks for the help!

"Jim Thomlinson" wrote:

> Your code is a long way off from what you describe. So you want to copy the
> individual values in range One into a specific cell on Sheet Trust and then
> print out a range from that sheet??? Assuming that to be the case this will
> be a lot closer to what you have asked...
>
> dim rng as range
>
> for each rng in worksheets("Sheet1").Range("One")
> with worksheets("Trust")
> .range("B4").value = rng.value
> .range("Two").PrintPreview 'Change to printout
> end with
> next rng
> --
> HTH...
>
> Jim Thomlinson
>
>
> "iperlovsky" wrote:
>
> > I have the following routine to copy a cell in the named range 'One', paste
> > the value into cell B4 of worksheet 'Trust', print the named range 'Two' and
> > then repeat this procedure for all of the cells in the named range 'One'. I
> > don't think this makes a difference, but the named ranges are on different
> > sheets. I am receiving a run-time error 13 - type mismatch. Not sure, but
> > the values to be copied from 'One' to cell B4 are stored as text.
> > Is there an easy fix to my macro?
> >
> > Sub printAll()
> > Dim i
> > Dim myCount
> > myCount = Range("One")
> > For i = 0 To myCount
> > With Range("One")
> > .Copy
> > .Worksheets("Trust").Range("B4").Value
> > .Range("Two").Print
> > End With
> > If i = myCount Then
> > End If
> > Next i
> > End Sub