Can anyone figure this code problem please

[simonsmith]

It comes up with runtime error 13
Someone very kindly did this for me (because I dont know VBA) but cant get hold of him to help fix it. It chokes on the P5 thing when it should be looking at cell P5 then going to the sheet named in that cell
Set sh = Worksheets(sh.Range("p5"))


Cheers

Simon

' Dim sh as Worksheet, sh2 as Worksheet
Dim strSheetName As String
strSheetName = Cells(5, 16).Value

Dim i As Long, j As Long
Dim rng As Range, cell As Range
Set sh = Worksheets("sheet1")
Set sh1 = Worksheets("Compiler")
j = 1
Do While sh.Name <> sh1.Name
Set rng = sh.Range(sh.Range(sh.Range("p3")), _
sh.Range(sh.Range("p4")))
i = 0
For Each cell In rng
i = i + 1
sh1.Cells(i, j).Value = cell.Value
Next
j = j + 1
Set sh = Worksheets(sh.Range("p5"))
'(sh.Range("P5"))
Loop
sh1.Activate
Range("p5").Select
End Sub

[Dave Peterson]

Sh is a variable that represents a worksheet.

Your line of code is looking at the value in P5 of Sheet1. Then it's trying to
find a worksheet with that name.

So if P5 of Sheet1 contained "Simon", your code is looking for a worksheet named
Simon.

If there were no worksheets named Simon, you'd get a "subscript out of range
error (9)". But since you're getting an error 13, I'm betting that you have
some error in P5 of Sheet1--like #n/a or Div/0.



simonsmith wrote:
>
> It comes up with runtime error 13
> Someone very kindly did this for me (because I dont know VBA) but cant
> get hold of him to help fix it. It chokes on the P5 thing when it
> should be looking at cell P5 then going to the sheet named in that
> cell
> Set sh = Worksheets(sh.Range("p5"))
>
> Cheers
>
> Simon
>
> ' Dim sh as Worksheet, sh2 as Worksheet
> Dim strSheetName As String
> strSheetName = Cells(5, 16).Value
>
> Dim i As Long, j As Long
> Dim rng As Range, cell As Range
> Set sh = Worksheets("sheet1")
> Set sh1 = Worksheets("Compiler")
> j = 1
> Do While sh.Name <> sh1.Name
> Set rng = sh.Range(sh.Range(sh.Range("p3")), _
> sh.Range(sh.Range("p4")))
> i = 0
> For Each cell In rng
> i = i + 1
> sh1.Cells(i, j).Value = cell.Value
> Next
> j = j + 1
> Set sh = Worksheets(sh.Range("p5"))
> '(sh.Range("P5"))
> Loop
> sh1.Activate
> Range("p5").Select
> End Sub
>
> --
> simonsmith
> ------------------------------------------------------------------------
> simonsmith's Profile: http://www.excelforum.com/member.php...o&userid=34235
> View this thread: http://www.excelforum.com/showthread...hreadid=543360

--

Dave Peterson