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

[simonsmith]

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

 

[Guest]

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

 

[simonsmith]

You Beauty, works great with Value

Cheers

Simon

 

[Rick Hansen]

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]

instead of columns onto the compiler page?

 

[simonsmith]

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