Can anyone figure this code problem please

S

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
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top