How to extract from sheet a, multiply by a factor in sheet b and paste in sheet c while including preceding data

Question

im trying to figure out a way to automate this process. I've broken it down in to the following steps but don't know how to start.. VBA code is a slight edit from record macro. Any guidance would be really appreciated!

1) Check how many cities starting in current month in 'Launch Matrix tab', this

2) Extract data from 'sample data tab', this

3) multiply (2)'s data with the amount of cities starting, in order to see full impact of X amount of cities

4) and paste in 'Consolidated Tab' under the right month tab

Consolidated tab looks exactly the same as the sample data tab, only difference is it shows a consolidated view.

Code for first 2 months looks like the following:

'
' Macro2 Macro
'

'
    ActiveCell.FormulaR1C1 = _
        "=('Launch Matrix'!R2C4*'Small City Data'!R[-1]C+'Launch Matrix'!R3C4*'Medium City Data'!R[-1]C+'Launch Matrix'!R4C4*'Large City Data'!R[-1]C)"
    Range("C5").Select
    Selection.Copy
    Range("C3").Select
    Selection.End(xlToRight).Select
    Range("L5").Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range("D5:L5").Select
    Range("L5").Activate
    ActiveSheet.Paste
    Range("D5").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=('Launch Matrix'!R2C4*'Small City Data'!R[-1]C+'Launch Matrix'!R3C4*'Medium City Data'!R[-1]C+'Launch Matrix'!R4C4*'Large City Data'!R[-1]C)+('Launch Matrix'!R2C5*'Small City Data'!R[-1]C[-1]+'Launch Matrix'!R3C5*'Medium City Data'!R[-1]C[-1]+'Launch Matrix'!R4C5*'Large City Data'!R[-1]C[-1])"
    Range("D5").Select
    Selection.Copy
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveSheet.Paste
End Sub

I want Feb-20 in consolidated tab to include launches in Feb-20 (i.e. Month 1 rev/costs * # of launches in Feb-20) and Month 2 data of Jan-20 launches (Month 2 rev/costs * # of launches in Jan-20) and so on and so forth. Thank you!

@SJR this is with all small, medium and large city active ![this](https://i.imgur.com/5fMoNS2.png) and with only small city active ![this](https://i.imgur.com/ybesiuJ.png) — wasimakram101, Feb 19 '20 at 15:08
So you have a working formula? Why the need for VBA? Isn't it just a case of inserting the same formula into a range of cells in your consolidated sheet? — SJR, Feb 19 '20 at 16:17
There are 50 launch months, my formula just goes till 3 months. and it's too manual to add prior months data, then add current month data as you can see from my post 22mins ago. — wasimakram101, Feb 19 '20 at 16:18

SJR · Accepted Answer · 2020-02-19T17:09:12.797

1

I don't have time to simulate your whole set-up so here is a slimmed-down example which should give you some pointers.

This is worth a read.

Sub x()

Dim rLaunch As Range, c As Long

'table showing monthly totals of small etc
Set rLaunch = Worksheets("Launch").Range("C2").CurrentRegion
'remove headings so just have numbers
Set rLaunch = rLaunch.Offset(, 2).Resize(, rLaunch.Columns.Count - 2)

'loop through each column of table (months)
For c = 1 To rLaunch.Columns.Count
    'revenue
    'multiply small number by small revenue and add to same for medium and large
    Worksheets("Consolidated").Range("A3").Offset(, c).Value = _
        rLaunch(1, c) * Worksheets("Small").Range("B4").Offset(, c) + _
        rLaunch(2, c) * Worksheets("Medium").Range("B4").Offset(, c) + _
        rLaunch(3, c) * Worksheets("Large").Range("B4").Offset(, c)
Next c

End Sub

Launch sheet

Small city sheet (medium and large are the same)

Consolidated sheet

edited Feb 19 '20 at 17:09

answered Feb 19 '20 at 16:36

SJR

22,986
6
18
26

THIS IS EXACTLY HOW IT SHOULD BE WORKINGGGG! Could you please provide a comment as to what each line does, so i can replicate and expand it for my whole set up? – wasimakram101 Feb 19 '20 at 16:47
What does this line do: Set rLaunch = rLaunch.Offset(, 2).Resize(, rLaunch.Columns.Count - 2) – wasimakram101 Feb 19 '20 at 17:04
Have added a few comments. That removes the extraneous columns so you just have the table of numbers D2:I4 and none of the headings. The rest just loops through each column of the table and multiplies each number by respective sheet revenue. Perhaps you could accept the answer? – SJR Feb 19 '20 at 17:08
It's working but im encountering an issue. Where a month doesnt have a launch, it just shows zero rather than continuing prior months data? – wasimakram101 Feb 20 '20 at 10:27
A zero in the consolidated sheet? Why should it continue the prior month? – SJR Feb 20 '20 at 11:42

How to extract from sheet a, multiply by a factor in sheet b and paste in sheet c while including preceding data

1 Answers1