Slight prob with this code

Hi Someone very kindly made this for me but there is a narly little problem, example best explains it...

Lets say this represents two rows of data
1 would be in position A1
H would be in position H2

12345678
abcdefgh


The below macro uses what is specified in user input cells P3 and P4 to know the range of cells to copy.
If the user input cells in the spreadsheet are p3=A1 , p4=h2 it will copy all (like I want it to)

BUT

If p3 = A1 and P4 = b2

I would like it to copy
12345678
ab

but it does not, instead it copies
12
ab

I can sort of see why it is doing what it is doing but what it needs to do (in this example) is copy the full rows (of 8 columns) previous to P4, then copy the partial row that the P4 specified cell is in!!

Also macro copies to columns (in the compiler sheet)instead of rows like the it was in the original sheets

In case you want to know what the macro is for...
it is for modeling packet data. A system copies data in a packet (starting a P3) until it reaches a decision point (P4)it will then read which partial packet to send next (P5),it will then have a new partial set of data to make up more of the packet (P3,P4) before reaching the next decision point (P4) then reading where to get the next lot of data from (p5) etc. The idea behind using the macro is to show that with different data content, different decisions will be made which will result in additional data making up the packet.

Any help really appreciated. The project seems to be easy compared to VBA magic!!

Cheers

Simon














12345678
abcdefgh




Sub Macro3()
Dim sh As Worksheet, sh2 As Worksheet
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").Value)
Loop
sh1.Activate
End Sub

You have other replies at your other post.

simonsmith wrote:
>
> Hi Someone very kindly made this for me but there is a narly little
> problem, example best explains it...
>
> Lets say this represents two rows of data
> 1 would be in position A1
> H would be in position H2
>
> 12345678
> abcdefgh
>
> The below macro uses what is specified in user input cells P3 and P4 to
> know the range of cells to copy.
> If the user input cells in the spreadsheet are p3=A1 , p4=h2 it will
> copy all (like I want it to)
>
> BUT
>
> If p3 = A1 and P4 = b2
>
> I would like it to copy
> 12345678
> ab
>
> but it does not, instead it copies
> 12
> ab
>
> I can sort of see why it is doing what it is doing but what it needs to
> do (in this example) is copy the full rows (of 8 columns) previous to
> P4, then copy the partial row that the P4 specified cell is in!!
>
> Also macro copies to columns (in the compiler sheet)instead of rows
> like the it was in the original sheets
>
> In case you want to know what the macro is for...
> it is for modeling packet data. A system copies data in a packet
> (starting a P3) until it reaches a decision point (P4)it will then read
> which partial packet to send next (P5),it will then have a new partial
> set of data to make up more of the packet (P3,P4) before reaching the
> next decision point (P4) then reading where to get the next lot of data
> from (p5) etc. The idea behind using the macro is to show that with
> different data content, different decisions will be made which will
> result in additional data making up the packet.
>
> Any help really appreciated. The project seems to be easy compared to
> VBA magic!!
>
> Cheers
>
> Simon
>
> 12345678
> abcdefgh
>
> Sub Macro3()
> Dim sh As Worksheet, sh2 As Worksheet
> 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").Value)
> Loop
> sh1.Activate
> End Sub
>
> --
> simonsmith
> ------------------------------------------------------------------------
> simonsmith's Profile: http://www.excelforum.com/member.php...o&userid=34235
> View this thread: http://www.excelforum.com/showthread...hreadid=543595

--

Dave Peterson