Macro VBA Help please :)

[Heather]

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

 

[Joel]

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]

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

 

[Per Jessen]

Typo alert, the line should read:

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

Regards,
Per

 

[Heather]

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?

 

[Phil Hibbs]

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.

 

[Heather]

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

 

[Joel]

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