A macro drop down that will repeat on every row

Granternz

New Member
Joined
Feb 17, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
What I want it to do:
I have a macro selecting from a dropdown in cell "A2" sheet1 with 2 options
1st drop down option copies a row of values and formulas from sheet2 and paste them into "B2"
2nd drop down option copies a differentrow of values and formulas from sheet2 and paste them into "B2"

The problem is I want to repeat this with a copied macro placed in "A3" with the same drop down option, the same 2 formula choices the only difference I want it to paste to"B3"
Then repeat this with A4, A5 and so on.
The Why:
2 different formula sets, one positive, one negative will calculate different data that is specific to that event.
The drop down text in "A:A" will label the row event for quick visual reference of it being a +ve or -ve event
The process is continuous and the events are random Hench the need for the macro in row "A"

The code I used works fine with a drop down macro in "A2" for row "2"

Sub Macro1()
Sheets("macroSelection").Select
Range("B2:R2").Select
Selection.Copy
Sheets("StartLloss03").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End Sub


Sub Macro2()
Sheets("macroSelection").Select
Range("B3:R3").Select
Selection.Copy
Sheets("StartLloss03").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End Sub
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,057
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Maybe like this

VBA Code:
Sub MM1()
Dim lr As Long, r As Long
lr = Sheets("macroSelection").Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lr
    With Sheets("macroSelection")
       .Range("B" & r & ":R" & r).Copy
        Sheets("StartLloss03").Range("B" & r).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
    End With
Next r
End Sub
 

Granternz

New Member
Joined
Feb 17, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Thanks Micheal

I had a quick look at your code I was unable to link it to my drop down to run as a selection in column "A" will have a better look at it when I finish work
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,057
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
It doesn't do anything related to Col "A" because you didn't tell us what was in Col "A"
But I'm guessing it will need an IF statement in the For....Next loop
 

Granternz

New Member
Joined
Feb 17, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Thanks Michael I appreciate your help
Yes, I thought so, and possibly gets messy from there?

I over analysed the problem I have come up with a simple solution and works exactly how I want it to.

To help other who may be interested in my solution?
To save people time reading stuff they don't need to read - I will explain the 'why' first then the 'how' later for those who are still interested.

The 'Why' and one of its practical uses:
New to and want to manually back test the stock market using a spreadsheet
"Long" trade - select entry price, share value, stop loss, buy/sell commission, profit/loss % etc (resulting values risk/reward on +ve trend to make a profit) will mimic the result of actual trade
"Short" trade - select entry price, share value, stop loss, buy/sell commission, profit/loss % etc (resulting values risk/reward on - ve trend to make a profit) will mimic the result of actual trade
[Short trade is a strange 'beast' you make a profit as the price goes down] - Hench the need for -ve values and formula to express correct profit and loss. (DO NOT SHORT TRADE IN THE REAL WORLD IF YOU ARE NEW TO TRADING you could quickly loss the farm)

Other uses could be to manually test a large chunk of data that have 2 different events, regular occurrence, different parameters that deviate from mean to better understand, make changes for the better.

The 'How': (I will keep this short as possible)
deleted all the macros relating to the workbook
copied and pasted the "long"+ve values and formulas into row "F2" drag copy paste these values and formulas in row 2 down to row "?"
I made a Command tool bar "use relative reference" macro button, with macro selection [Store macro in: Personal Macro Workbook], recorded a copy/paste macro of the "Short" -ve values and formulas
Select the where you want to enter the "Short" macro (this is why the macro needs to be a "use relative reference" macro it will insert in any cell that you have selected otherwise it will only insert in the cell that the macro was created) select the following link for more details on how to do this Macro to toolbar
I kept the drop-down selection in column "A" (that was used to select either of the 2 original macros, now deleted) I added conditional format "Long" 'green' "Short" 'red'

Summary how it works for me: I place the curser in in row "F" where I want to paste - select smiley face button top of bar menu - will paste -ve "Short" formulas
stockSheetPic.PNG


If you have read this far and I have confused the "F" out of you then select 'sorry'
Otherwise enjoy
 
Solution

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,057
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Glad you achieved the desired result.....no matter how you get there... :cool: (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,128,165
Messages
5,629,068
Members
416,363
Latest member
zaveedd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top