Macro VBA Help please :)
 

Hi, I have created with the very much appreciated help from these discussion
boards a workbook that includes a tab that has a high level summary price
based on users input and then a hyperlink that once clicked on will break out
the prices based on their choices at a more detailed level


I'm struggling with 3 things
1) Getting the project price to move over to column E it is currently in
Column C
2) Adding a Total Addt'l products price between the new rows and the
Project Price
3) Is there a way to incorporate all of this data into a Word letter when
it's all done?

Here's the code
Sub CreateTab1()

Dim ws As Worksheet
Set ws = gettab("tab1")

Dim rPart As Range
Dim Target As Range

With ws

.Range("B3") = "Developers"
.Range("C3") = Worksheets("Planner").Range("B5")
Set Target = .Range("C4")
End With
Set rPart = Worksheets("Addtl Products").Range("C12")

Do Until rPart = ""
Target = rPart.Value
Target.Offset(, 1) = rPart.Offset(, -1).Value
Target.Offset(, 2) = rPart.Offset(, 1)
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)
Loop
Target.Offset(, -1) = "Project Price"
Target = Worksheets("Planner").Range("ProjectModel.price")



End Sub

Sub CreateTab2()

Dim ws As Worksheet
Set ws = gettab("tab2")

Dim rPart As Range
Dim Target As Range

With ws

.Range("B3") = "Developers"
.Range("C3") = Worksheets("Planner").Range("B5")
Set Target = .Range("C4")
End With
Set rPart = Worksheets("Addtl Products").Range("C12")

Do Until rPart = ""
Target = rPart.Value
Target.Offset(, 1) = rPart.Offset(, -1).Value
Target.Offset(, 2) = rPart.Offset(, 1)
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)
Loop
Target.Offset(, -1) = "Project Price"
Target = Worksheets("Planner").Range("TermModel.price")




End Sub

Macro VBA Help please :)
 

I really don't like your programming style. it is hard to follow. I rewrote
youcode below. It is much easier to understand like the changes I made
below. You should easily be able to change to column E with the code below.
The problem with your orignal code is something the old column d is being put
in column e on the new sheet.

to get an addition row on the target sheet you have to increment Targetrow
twice in the code below

---------------------------------------------------------------------------------------
sample of how to add an additional row. TargetRow is incremented twice.

Targetws.Range("C" & Targetrow) = .Range("C" & Sourcerow).value

Targetws.Range("D" & Targetrow) = .Range("B" & Sourcerow).value
Targetws.Range("E" & Targetrow) = .Range("D" & Sourcerow).value
TargetRow = Targetrow + 1
'put the subtotals in here
TargetRow = Targetrow + 1
SourceRowCount = SourceRowCount + 1

--------------------------------------------------------------------------------------------

Sub CreateTab1()

Dim Targetws As Worksheet
Set Targetws = gettab("tab1")

Set Sourcews = Worksheets("Addtl Products")


With Targetws

.Range("B3") = "Developers"
.Range("C3") = Worksheets("Planner").Range("B5")
TargetRow = 4
End With

with Sourcews
SourceRowCount = 12
Do while .Range("C" SourcerowCount) < ""
Targetws.Range("C" & Targetrow) = .Range("C" & Sourcerow).value

Targetws.Range("D" & Targetrow) = .Range("B" & Sourcerow).value
Targetws.Range("E" & Targetrow) = .Range("D" & Sourcerow).value
TargetRow = Targetrow + 1
SourceRowCount = SourceRowCount + 1
loop
end with

Targetws.Range("B" & Targetrow) = "Project Price"

Set rPart = Worksheets("Addtl Products").Range("C12")

End Sub


"Heather" wrote:

Hi, I have created with the very much appreciated help from these discussion
boards a workbook that includes a tab that has a high level summary price
based on users input and then a hyperlink that once clicked on will break out
the prices based on their choices at a more detailed level


I'm struggling with 3 things
1) Getting the project price to move over to column E it is currently in
Column C
2) Adding a Total Addt'l products price between the new rows and the
Project Price
3) Is there a way to incorporate all of this data into a Word letter when
it's all done?

Here's the code
Sub CreateTab1()

Dim ws As Worksheet
Set ws = gettab("tab1")

Dim rPart As Range
Dim Target As Range

With ws

.Range("B3") = "Developers"
.Range("C3") = Worksheets("Planner").Range("B5")
Set Target = .Range("C4")
End With
Set rPart = Worksheets("Addtl Products").Range("C12")

Do Until rPart = ""
Target = rPart.Value
Target.Offset(, 1) = rPart.Offset(, -1).Value
Target.Offset(, 2) = rPart.Offset(, 1)
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)
Loop
Target.Offset(, -1) = "Project Price"
Target = Worksheets("Planner").Range("ProjectModel.price")



End Sub

Sub CreateTab2()

Dim ws As Worksheet
Set ws = gettab("tab2")

Dim rPart As Range
Dim Target As Range

With ws

.Range("B3") = "Developers"
.Range("C3") = Worksheets("Planner").Range("B5")
Set Target = .Range("C4")
End With
Set rPart = Worksheets("Addtl Products").Range("C12")

Do Until rPart = ""
Target = rPart.Value
Target.Offset(, 1) = rPart.Offset(, -1).Value
Target.Offset(, 2) = rPart.Offset(, 1)
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)
Loop
Target.Offset(, -1) = "Project Price"
Target = Worksheets("Planner").Range("TermModel.price")




End Sub

Macro VBA Help please :)
 

Thank you, but I'm getting syntax errors on this line
Do while .Range("C" SourcerowCount) < ""

"Joel" wrote:

I really don't like your programming style. it is hard to follow. I rewrote
youcode below. It is much easier to understand like the changes I made
below. You should easily be able to change to column E with the code below.
The problem with your orignal code is something the old column d is being put
in column e on the new sheet.

to get an addition row on the target sheet you have to increment Targetrow
twice in the code below

---------------------------------------------------------------------------------------
sample of how to add an additional row. TargetRow is incremented twice.

Targetws.Range("C" & Targetrow) = .Range("C" & Sourcerow).value

Targetws.Range("D" & Targetrow) = .Range("B" & Sourcerow).value
Targetws.Range("E" & Targetrow) = .Range("D" & Sourcerow).value
TargetRow = Targetrow + 1
'put the subtotals in here
TargetRow = Targetrow + 1
SourceRowCount = SourceRowCount + 1

--------------------------------------------------------------------------------------------

Sub CreateTab1()

Dim Targetws As Worksheet
Set Targetws = gettab("tab1")

Set Sourcews = Worksheets("Addtl Products")


With Targetws

.Range("B3") = "Developers"
.Range("C3") = Worksheets("Planner").Range("B5")
TargetRow = 4
End With

with Sourcews
SourceRowCount = 12
Do while .Range("C" SourcerowCount) < ""
Targetws.Range("C" & Targetrow) = .Range("C" & Sourcerow).value

Targetws.Range("D" & Targetrow) = .Range("B" & Sourcerow).value
Targetws.Range("E" & Targetrow) = .Range("D" & Sourcerow).value
TargetRow = Targetrow + 1
SourceRowCount = SourceRowCount + 1
loop
end with

Targetws.Range("B" & Targetrow) = "Project Price"

Set rPart = Worksheets("Addtl Products").Range("C12")

End Sub


"Heather" wrote:

Hi, I have created with the very much appreciated help from these discussion
boards a workbook that includes a tab that has a high level summary price
based on users input and then a hyperlink that once clicked on will break out
the prices based on their choices at a more detailed level


I'm struggling with 3 things
1) Getting the project price to move over to column E it is currently in
Column C
2) Adding a Total Addt'l products price between the new rows and the
Project Price
3) Is there a way to incorporate all of this data into a Word letter when
it's all done?

Here's the code
Sub CreateTab1()

Dim ws As Worksheet
Set ws = gettab("tab1")

Dim rPart As Range
Dim Target As Range

With ws

.Range("B3") = "Developers"
.Range("C3") = Worksheets("Planner").Range("B5")
Set Target = .Range("C4")
End With
Set rPart = Worksheets("Addtl Products").Range("C12")

Do Until rPart = ""
Target = rPart.Value
Target.Offset(, 1) = rPart.Offset(, -1).Value
Target.Offset(, 2) = rPart.Offset(, 1)
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)
Loop
Target.Offset(, -1) = "Project Price"
Target = Worksheets("Planner").Range("ProjectModel.price")



End Sub

Sub CreateTab2()

Dim ws As Worksheet
Set ws = gettab("tab2")

Dim rPart As Range
Dim Target As Range

With ws

.Range("B3") = "Developers"
.Range("C3") = Worksheets("Planner").Range("B5")
Set Target = .Range("C4")
End With
Set rPart = Worksheets("Addtl Products").Range("C12")

Do Until rPart = ""
Target = rPart.Value
Target.Offset(, 1) = rPart.Offset(, -1).Value
Target.Offset(, 2) = rPart.Offset(, 1)
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)
Loop
Target.Offset(, -1) = "Project Price"
Target = Worksheets("Planner").Range("TermModel.price")




End Sub

Macro VBA Help please :)
 

Typo alert, the line should read:

Do while .Range("C" & SourceRowCount) < ""

Regards,
Per



On 20 Aug., 04:45, Heather wrote:
Thank you, but I'm getting syntax errors on this line
Do while .Range("C" SourcerowCount) < ""



"Joel" wrote:
I really don't like your programming style. *it is hard to follow. *I rewrote
youcode below. *It is much easier to understand like the changes I made
below. *You should easily be able to change to column E with the code below. *
The problem with your orignal code is something the old column d is being put
in column e on the new sheet.

to get an addition row on the target sheet you have to increment Targetrow
twice in the code below

---------------------------------------------------------------------------*------------
sample of how to add an additional row. *TargetRow is incremented twice.

* * * * * Targetws.Range("C" & Targetrow) = .Range("C" & Sourcerow).value * *

* * * * * Targetws.Range("D" & Targetrow) = .Range("B" & Sourcerow).value
* * * * * Targetws.Range("E" & Targetrow) = .Range("D" & Sourcerow).value
* * * * * TargetRow = Targetrow + 1
* * * * * 'put the subtotals in here
* * * * * TargetRow = Targetrow + 1
* * * * * SourceRowCount = SourceRowCount + 1

---------------------------------------------------------------------------*-----------------

Sub CreateTab1()

* * Dim Targetws As Worksheet
* * Set Targetws = gettab("tab1")

* * Set Sourcews = Worksheets("Addtl Products")

* * With Targetws

* * * * .Range("B3") = "Developers"
* * * * .Range("C3") = Worksheets("Planner").Range("B5")
* * * * TargetRow = 4
* * End With

* * with Sourcews
* * * *SourceRowCount = 12
* * * *Do while .Range("C" SourcerowCount) < ""
* * * * * Targetws.Range("C" & Targetrow) = .Range("C" & Sourcerow).value * *

* * * * * Targetws.Range("D" & Targetrow) = .Range("B" & Sourcerow).value
* * * * * Targetws.Range("E" & Targetrow) = .Range("D" & Sourcerow).value
* * * * * TargetRow = Targetrow + 1
* * * * * SourceRowCount = SourceRowCount + 1
* * * *loop
* * end with

* * Targetws.Range("B" & Targetrow) = "Project Price"

* * * * Set rPart = Worksheets("Addtl Products").Range("C12")

End Sub

"Heather" wrote:

Hi, *I have created with the very much appreciated help from these discussion
boards a workbook that includes a tab that has a high level summary price
based on users input and then a hyperlink that once clicked on will break out
the prices based on their choices at a more detailed level

I'm struggling with 3 things
1) *Getting the project price to move over to column E it is currently in
Column C
2) *Adding a Total Addt'l products price between the new rows and the
Project Price
3) Is there a way to incorporate all of this data into a Word letter when
it's all done?

Here's the code
Sub CreateTab1()

* * Dim ws As Worksheet
* * Set ws = gettab("tab1")

* * Dim rPart As Range
* * Dim Target As Range

* * With ws

* * * * .Range("B3") = "Developers"
* * * * .Range("C3") = Worksheets("Planner").Range("B5")
* * * * Set Target = .Range("C4")
* * End With
* * * * Set rPart = Worksheets("Addtl Products").Range("C12")

* * * * Do Until rPart = ""
* * * * * * Target = rPart.Value
* * * * * * Target.Offset(, 1) = rPart.Offset(, -1).Value
* * * * * * Target.Offset(, 2) = rPart.Offset(, 1)
* * * * * * Set Target = Target.Offset(1)
* * * * * * Set rPart = rPart.Offset(1)
* * * * Loop
* * * * Target.Offset(, -1) = "Project Price"
* * * * Target = Worksheets("Planner").Range("ProjectModel.price")

End Sub

Sub CreateTab2()

* * Dim ws As Worksheet
* * Set ws = gettab("tab2")

* * Dim rPart As Range
* * Dim Target As Range

* * With ws

* * * * .Range("B3") = "Developers"
* * * * .Range("C3") = Worksheets("Planner").Range("B5")
* * * * Set Target = .Range("C4")
* * End With
* * * * Set rPart = Worksheets("Addtl Products").Range("C12")

* * * * Do Until rPart = ""
* * * * * * Target = rPart.Value
* * * * * * Target.Offset(, 1) = rPart.Offset(, -1).Value
* * * * * * Target.Offset(, 2) = rPart.Offset(, 1)
* * * * * * Set Target = Target.Offset(1)
* * * * * * Set rPart = rPart.Offset(1)
* * * * Loop
* * * * Target.Offset(, -1) = "Project Price"
* * * * Target = Worksheets("Planner").Range("TermModel.price")

End Sub- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Macro VBA Help please :)
 

thank you :) but now it doesn't like the next line either
Targetws.Range("C" & Targetrow) = .Range("C" & Sourcerow).Value

it's saying it's not defined?


"Per Jessen" wrote:

Typo alert, the line should read:

Do while .Range("C" & SourceRowCount) < ""

Regards,
Per



On 20 Aug., 04:45, Heather wrote:
Thank you, but I'm getting syntax errors on this line
Do while .Range("C" SourcerowCount) < ""



"Joel" wrote:
I really don't like your programming style. it is hard to follow. I rewrote
youcode below. It is much easier to understand like the changes I made
below. You should easily be able to change to column E with the code below.
The problem with your orignal code is something the old column d is being put
in column e on the new sheet.

to get an addition row on the target sheet you have to increment Targetrow
twice in the code below

---------------------------------------------------------------------------Â*------------
sample of how to add an additional row. TargetRow is incremented twice.

Targetws.Range("C" & Targetrow) = .Range("C" & Sourcerow).value

Targetws.Range("D" & Targetrow) = .Range("B" & Sourcerow).value
Targetws.Range("E" & Targetrow) = .Range("D" & Sourcerow).value
TargetRow = Targetrow + 1
'put the subtotals in here
TargetRow = Targetrow + 1
SourceRowCount = SourceRowCount + 1

---------------------------------------------------------------------------Â*-----------------

Sub CreateTab1()

Dim Targetws As Worksheet
Set Targetws = gettab("tab1")

Set Sourcews = Worksheets("Addtl Products")

With Targetws

.Range("B3") = "Developers"
.Range("C3") = Worksheets("Planner").Range("B5")
TargetRow = 4
End With

with Sourcews
SourceRowCount = 12
Do while .Range("C" SourcerowCount) < ""
Targetws.Range("C" & Targetrow) = .Range("C" & Sourcerow).value

Targetws.Range("D" & Targetrow) = .Range("B" & Sourcerow).value
Targetws.Range("E" & Targetrow) = .Range("D" & Sourcerow).value
TargetRow = Targetrow + 1
SourceRowCount = SourceRowCount + 1
loop
end with

Targetws.Range("B" & Targetrow) = "Project Price"

Set rPart = Worksheets("Addtl Products").Range("C12")

End Sub

"Heather" wrote:

Hi, I have created with the very much appreciated help from these discussion
boards a workbook that includes a tab that has a high level summary price
based on users input and then a hyperlink that once clicked on will break out
the prices based on their choices at a more detailed level

I'm struggling with 3 things
1) Getting the project price to move over to column E it is currently in
Column C
2) Adding a Total Addt'l products price between the new rows and the
Project Price
3) Is there a way to incorporate all of this data into a Word letter when
it's all done?

Here's the code
Sub CreateTab1()

Dim ws As Worksheet
Set ws = gettab("tab1")

Dim rPart As Range
Dim Target As Range

With ws

.Range("B3") = "Developers"
.Range("C3") = Worksheets("Planner").Range("B5")
Set Target = .Range("C4")
End With
Set rPart = Worksheets("Addtl Products").Range("C12")

Do Until rPart = ""
Target = rPart.Value
Target.Offset(, 1) = rPart.Offset(, -1).Value
Target.Offset(, 2) = rPart.Offset(, 1)
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)
Loop
Target.Offset(, -1) = "Project Price"
Target = Worksheets("Planner").Range("ProjectModel.price")

End Sub

Sub CreateTab2()

Dim ws As Worksheet
Set ws = gettab("tab2")

Dim rPart As Range
Dim Target As Range

With ws

.Range("B3") = "Developers"
.Range("C3") = Worksheets("Planner").Range("B5")
Set Target = .Range("C4")
End With
Set rPart = Worksheets("Addtl Products").Range("C12")

Do Until rPart = ""
Target = rPart.Value
Target.Offset(, 1) = rPart.Offset(, -1).Value
Target.Offset(, 2) = rPart.Offset(, 1)
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)
Loop
Target.Offset(, -1) = "Project Price"
Target = Worksheets("Planner").Range("TermModel.price")

End Sub- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Macro VBA Help please :)
 

Looks like Joel introduced the Sourcerow and Targetrow variables to
store the row numbers that you had hard coded. You need to define
these variables and assign them values.

Phil Hibbs.

Macro VBA Help please :)
 

hi Phil, sorry, I'm new to this so I'm not completely sure what you mean by
assigning them values?

"Phil Hibbs" wrote:

Looks like Joel introduced the Sourcerow and Targetrow variables to
store the row numbers that you had hard coded. You need to define
these variables and assign them values.

Phil Hibbs.

Macro VBA Help please :)
 

there were a few typos in my code. There are additional macros in your code
that you didn't post. the following line won't work unless there if a
function in your workbook called gettab

Set TargetWs = gettab("tab1")





Sub CreateTab1()

Dim TargetWs As Worksheet
Set TargetWs = gettab("tab1")

Set Sourcews = Worksheets("Addtl Products")

With TargetWs

.Range("B3") = "Developers"
.Range("C3") = Worksheets("Planner").Range("B5")
TargetRow = 4
End With

With Sourcews
SourceRow = 12
Do While .Range("C" & SourceRow) < ""
TargetWs.Range("C" & TargetRow) = .Range("C" & SourceRow).Value

TargetWs.Range("D" & TargetRow) = .Range("B" & SourceRow).Value
TargetWs.Range("E" & TargetRow) = .Range("D" & SourceRow).Value
TargetRow = TargetRow + 1
SourcerowCount = SourcerowCount + 1
Loop
End With

TargetWs.Range("B" & TargetRow) = "Project Price"

Set rPart = Worksheets("Addtl Products").Range("C12")

End Sub


"Heather" wrote:

hi Phil, sorry, I'm new to this so I'm not completely sure what you mean by
assigning them values?

"Phil Hibbs" wrote:

Looks like Joel introduced the Sourcerow and Targetrow variables to
store the row numbers that you had hard coded. You need to define
these variables and assign them values.

Phil Hibbs.