Can you see what's wrong withthis code please..
 

It comes up with runtime error 13
Someone very kindly did this for me (because I dont know VBA) but can
get hold of him to help fix it. It chokes on the P5 thing when i
should be looking at cell P5 then going to the sheet named in tha
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 Su

--
simonsmit
-----------------------------------------------------------------------
simonsmith's Profile: http://www.excelforum.com/member.php...fo&userid=3423
View this thread: http://www.excelforum.com/showthread.php?threadid=54334

Can you see what's wrong withthis code please..
 

try:
Set sh = Worksheets(sh.Range("p5").Value)
or
Set sh = Worksheets(sh.Range("p5").Text)
--
- K Dales


"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=543345

Can you see what's wrong withthis code please..
 

You Beauty, works great with Value

Cheers

Simon


--
simonsmith
------------------------------------------------------------------------
simonsmith's Profile: http://www.excelforum.com/member.php...o&userid=34235
View this thread: http://www.excelforum.com/showthread...hreadid=543345

Can you see what's wrong withthis code please..
 

simon, I tried to straighten out some of your code. I had a few typo's,
Maybe if you can tell me what the code is supposed to do I can help further.

enjoy ,Rick

==========================
Option Explicit

Sub Test()
Dim sh As Worksheet, sh1 As Worksheet
Dim strSheetName As String
Dim i As Long, j As Long
Dim rng As Range, cell As Range

Set sh = Worksheets("sheet1")
Set sh1 = Worksheets("Compiler")
strSheetName = Cells(5, 16).Value

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" wrote
in message ...

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=543345

Can you see what's wrong withthis code please..
 

instead of columns onto the compiler page?

--
simonsmit
-----------------------------------------------------------------------
simonsmith's Profile: http://www.excelforum.com/member.php...fo&userid=3423
View this thread: http://www.excelforum.com/showthread.php?threadid=54334

Can you see what's wrong withthis code please..
 

Hi Rick,
I got my inital answer , I needed to add value after the P5. I did not
do this code as I am not code savvy. The macro copies between cells as
specified on P3, P4 of a sheet onto the compiler sheet, it then goes
back to the sheet it was on and reads P5 then goes to the sheet that is
specified in that cell. When on the next sheet the procedure is
repeated.
It is a wonderful piece of code as I can make a decision tree from it
to analyse packets of data and based on the values in that data the
macro emulates what the system would do if reading the data packet.
Only prob I have now is that the paste onto the compiler sheet is in
columns rather than rows.
The cells it is copying are in 8 columns and about 40 rows. It copies
exactly what I specify but pastes it in columns when it should be
rows.
e.g.
two rows..
12345678
abcdefgh

is pasting onto the compiler page as two columns
1a
2b
3c
4d
5e
6f
7g
h8

when it should be
12345678
abcdefgh


I know a bit about excel but nothing about VBA so any help you can give
me is much appreciated.
Any recommended reading for learning VBA?

Cheers

Simon


--
simonsmith
------------------------------------------------------------------------
simonsmith's Profile: http://www.excelforum.com/member.php...o&userid=34235
View this thread: http://www.excelforum.com/showthread...hreadid=543345