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

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