S
simonsmith
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
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