BetterAardvark
New Member
- Joined
- Nov 17, 2021
- Messages
- 4
- Office Version
- 365
- Platform
- Windows
Hi all,
This is my first time making a macro in excel and I need some help. I am trying to get my macro to calculate % survival data. I currently have a macro that takes the average value for alive cells and calculates % survival, then copies the average value for alive cells and pastes it to a separate workbook with dying cell values and calculates % survival. My issue is that it is using the exact file names that I was using while making the macro, making it useless for the rest of the data I have to analyze. Is there a way for me to identify which workbooks I am trying to use with this macro? I was thinking about getting the name of the workbook I started with then use that to get the name of the other workbook (since they will have very similar names) or maybe clicking on the 2 workbooks I want to use and set those as variables, but I have no idea how to do this. Thanks
This is my first time making a macro in excel and I need some help. I am trying to get my macro to calculate % survival data. I currently have a macro that takes the average value for alive cells and calculates % survival, then copies the average value for alive cells and pastes it to a separate workbook with dying cell values and calculates % survival. My issue is that it is using the exact file names that I was using while making the macro, making it useless for the rest of the data I have to analyze. Is there a way for me to identify which workbooks I am trying to use with this macro? I was thinking about getting the name of the workbook I started with then use that to get the name of the other workbook (since they will have very similar names) or maybe clicking on the 2 workbooks I want to use and set those as variables, but I have no idea how to do this. Thanks
VBA Code:
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-12]:R[3]C[-12])"
Range("O15").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-12]:R[3]C[-12])"
Range("C21").Select
ActiveCell.FormulaR1C1 = "=R[-10]C/R11C15*100"
Range("C21").Select
Selection.AutoFill Destination:=Range("C21:C24"), Type:=xlFillDefault
Range("C21:C24").Select
Selection.AutoFill Destination:=Range("C21:M24"), Type:=xlFillDefault
Range("C21:M24").Select
Range("C24").Select
Selection.AutoFill Destination:=Range("C24:C25"), Type:=xlFillDefault
Range("C24:C25").Select
Range("C25").Select
ActiveCell.FormulaR1C1 = "=R[-10]C/R15C15*100"
Range("C25").Select
Selection.AutoFill Destination:=Range("C25:C28"), Type:=xlFillDefault
Range("C25:C28").Select
Selection.AutoFill Destination:=Range("C25:M28"), Type:=xlFillDefault
Range("C25:M28").Select
Range("O11:O15").Select
Selection.Copy
Windows("MT ASSAY 15 16 TET OFF 11152021 Nov Tue 16 2021 10-27-41-1723314.xlsx" _
).Activate
Range("O11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("MT ASSAY 15 16 TET ON 11152021 Nov Tue 16 2021 10-12-19-9557578.xlsx") _
.Activate
Range("C21:M28").Select
Application.CutCopyMode = False
Selection.Copy
Windows("MT ASSAY 15 16 TET OFF 11152021 Nov Tue 16 2021 10-27-41-1723314.xlsx" _
).Activate
Range("C21").Select
ActiveSheet.Paste
End Sub