Geeting value from one sheet and multiplying it with data on other sheet.

[Sari99]

I have workbook with data on 2 spreadsheets. On Sheet 1 I have text and numeric data(A1:B8).
Ex:
A B
Pen 7
toys 2
Eraser 9

ON Sheet 2 I have Data range( A1:E59).

I want a macro which can pull the values in coulmn b of sheet 1 one by one and multiply it with the data on sheet 2 and paste it on a new sheet

Ex:
Sheet 3= Data on sheet 2 * 7
Sheet 4= Data on sheet 2 * 2

I am not sure if this can be done or not?

[mudraker]

Have a look at my reply to the following message.

Which was a very similar query to yours and will give you a starting point.



http://www.excelforum.com/showthread.php?t=360611

[sarita]

Hi Mudraker,

Thanks, for the link. But it seems that the macro is getting the data from sheet 1 and multiply it with 2 and 5 each and put the calculated data on separate sheets.
What I want is instead of giving the values for multipication(2 and 5) it should automatically get these values (multiplier) from the sheet 1.
Ex.
The result should be
sheet 3 = data on sheet 2 * B1 of sheet 1
sheet 4 = data on sheet 2 * b3 of sheet 1
and so on till the last value of column B in sheet 1.

Do you think it is posible to do so or not?

Thanks!

[mudraker]

sarita

As I said in my original reply the link was just a starting point to give you a start.

I gather from your reply thet you have no or very little expeirece with macro's

I need more details before I can complete yhe macro coding.

Sheet 1 has range a1 to b8 which equals a possible of 16 mulipliers for each number on sheet2 range a1:e59.

How do you determine what cell the answer is to go into on sheets 3 & 4


this is an eample on how to multiply sheet 2 by sheet1 b1 with answer on sheet3
and multiply sheet 2 by sheet1 b3with answer on sheet4


Sub Multiplier()
Dim Rng As Range
Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Dim Ws3 As Worksheet
Dim Ws4 As Worksheet

Set Ws1 = Worksheets("Sheet1")
Set Ws2 = Worksheets("Sheet2")
Set Ws3 = Worksheets("Sheet3")
Set Ws4 = workshhets("Sheet4")

For Each Rng In Ws2.Range("a1:e59")
If IsNumeric(Rng.Value) Then

Ws3.Range(Rng.Address).Value = _
Rng.Value * Ws1.Range("b1").Value
Ws4.Range(Rng.Address).Value = _
Rng.Value * Ws1.Range("b3").Value
End If
Next Rng
End Sub

[sarita]

Hi Mudraker,

Thanks for all your help. Yes, you are very much right I don't
have any experience with maco's. Infact I never
worked with macros before.
This is something I need to do as a favour to my boss.
Here is the initial description of the problem which was given to me.

On sheet1("All Funds") they have the following data:

Name : Deals Corp. Ltd

Pr.Amt: $ 40000( At 100%)

Date : 10/11/05

Unknown Pmt Amt: $5000 (Calculated according to Pr.
Amt)
Known Pmt. Amt: $5550 (Calculated according to Pr.
Amt)
also data in the tabular form ranging from s16 to v160 (All numeric values like $50,0000,$208,000 etc).

They need a macro which will copy the data(only values, not formulas)from sheet1 to new worksheets in the same workbook and multiply the dollar amount on the copied sheet with certain value based on Investor name.
After reading few books and going through the articles posted on the forum webpage I was able to write the following code:

Sub Add_sheet()
Dim wSht As Worksheet
Dim shtname As String
Dim myvalue As Double
Dim mypercent As Double
Dim rngtocopy As Range
Dim rng As Range
Set ws1 = Worksheets("All Funds")
shtname = InputBox("enter the name")
For Each wSht In Worksheets
If wSht.Name = shtname Then
MsgBox "Sheet already Exists"
Exit Sub
End If
Next wSht
Sheets.Add.Name = shtname
Sheets(shtname).Move after:=Sheets(Sheets.Count)
Set rngtocopy = Sheets("All Funds").Range("A1:v160")
With rngtocopy
Sheets(shtname).Range("A1") _
.Resize(Rows.Count, .Columns.Count).Value _
= rngtocopy.Value
End With
myvalue = Worksheets("All Funds").Range("D8").Value
myvalue1 = Worksheets("All Funds").Range("D18").Value
myvalue2 = Worksheets("All Funds").Range("D19").Value
mypercent = InputBox("Please enter percentage")
If mypercent = 0 Then Exit Sub
ActiveSheet.Range("D8").Value = myvalue * mypercent / 100
ActiveSheet.Range("D18").Value = myvalue1 * mypercent / 100
ActiveSheet.Range("D19").Value = myvalue2 * mypercent / 100
For Each rng In ws1.Range("s16:v160")
If IsNumeric(rng.Value) Then
Debug.Print rng.Address
Sheets(shtname).Range(rng.Address).Value = rng.Value * mypercent / 100
End If
Next rng
End Sub

They did not like the concept of inputting the investor name( which will be the copied sheet name) and the %age value at run time. Instead they want me to add another sheet(like an index page) with the Investor names and the %age owned(which gets multiplied with the $ amount values on the copied sheets). And create another macro which will go to Index sheet, pull the name and value of the investor and create a new sheet with the investor name at the bottom tab and multiply the values on sheet 1 with the % age owned on Index sheet. And this should repeat for all the rows on the index sheet.

Data on Index Sheet:

Inv. Name %age Owed

abc 94
xyz 89
pqr 55
stu 37

I am not sure if it is possible or not?
I will appreciate any help on this problem.

Thanks.

[mudraker]

sarita

You can loop through a index sheet to get the company name and you can also have myPercent value set from a cell.

This macro shows how to loop through an index page. adding new sheets and setting some cell values on the new sheet.

Some of the code is mine and some is based on your pasted code.

I was not sure from reading your discription on what sheet the percentage figure was to come from.

If you live in Australia send me a private message and i will make arrangements to contact you by phone if possible to discuss this further.


Sub ddddd()
Dim iRow As Integer ' change to long if more than 32000 rows
Dim WsIndex As Worksheet
Dim WsFund As Worksheet
Dim wsName As Worksheet
Dim Ws1 As Worksheet
Dim sName As String


Set WsIndex = ThisWorkbook.Sheets("Index")
Set WsFund = ThisWorkbook.Sheets("All funds")
Set Ws1 = ThisWorkbook.Sheets("Sheet1")
For iRow = 1 To WsIndex. _
Cells(WsIndex.Rows.Count, "a"). _
End(xlUp).Row Step 1
sName$ = WsIndex.Cells(iRow, "a").Value
On Error Resume Next
Sheets(sName).Activate
If Err = 0 Then
MsgBox sName & " Sheet already Exists"
Exit Sub
End If
On Error GoTo 0
Err.Clear
Set wsName = Sheets.Add.Name = sName
Sheets(sName).Move after:=Sheets(Sheets.Count)

If WsIndex.Cells(iRow, "b").Value = 0 Then
Exit Sub
End If

wsName.Range("D8").Value = _
WsFund.Range("d8").Value * _
WsIndex.Cells(iRow, "b").Value / 100

wsName.Range("D18").Value = _
WsFund.Range("d18").Value * _
WsIndex.Cells(iRow, "b").Value / 100

wsName.Range("D19").Value = _
WsFund.Range("d19").Value * _
WsIndex.Cells(iRow, "b").Value / 100
Next iRow

End Sub