Changing between worksheets with similar names in macros

BetterAardvark

New Member
Joined
Nov 17, 2021
Messages
4
Office Version
  1. 365
Platform
  1. 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

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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

severynm

Active Member
Joined
Jan 8, 2021
Messages
273
Office Version
  1. 365
Platform
  1. Windows
Hi welcome to the forum! :)

VBA is a little different than some other programming languages in that if you do not specify certain items in your code, assumptions will be made for what the compiler assumes you want. It's nice because you can write working code without much basic programming knowledge, but if you want to do anything more complicated, it can be a headache - that's what you're encountering now.

The phrase Range("O15").Select makes two huge assumptions: that the range you are referring to is in the ActiveWorksheet, which is part of the ActiveWorkbook. Thus Range("O15").Select and ActiveWorkbook.ActiveWorksheet.Range("O15").Select are exactly identical - the missing items are automatically substituted in behind the scenes for you. For that exact reason, in my opinion it really pays off to be verbose in your code - it might be a little more to type, but it makes everything more readable and you can better prevent issues like this going forward.

With that said, your intuition is correct. You want to create some variables to represent your target workbooks, but also some to represent your target worksheets. All of your code you posted above will be accessible off of the worksheet object you set.
VBA Code:
    Dim workbook1 As Workbook
    Dim workbook2 As Workbook
    Dim workbook3 As Workbook
    
    Set workbook1 = Application.ThisWorkbook 'Set to the current workbook
    Set workbook2 = Application.Workbooks("NameOfOtherOpenWorkbook") 'Set to another currently opened workbook
    Set workbook3 = Application.Workbooks.Open("C:\PathOfOtherSavedWorkbook.xlsx") 'Open a currently closed workbook and set the variable to it
    
    
    Dim worksheet1 As Worksheet
    Dim worksheet2 As Worksheet
    
    Set worksheet1 = workbook1.Worksheets("SheetName") 'Refer to name as seen in the worksheet tabs in excel
    Set worksheet2 = workbook1.Sheet1 'Uses the VBA name from the project explorer - MUCH better to use as code will not break if the worksheet name is changed

    
    worksheet2.Range("O15").FormulaR1C1 = "=AVERAGE(RC[-12]:R[3]C[-12])"
    '...
 

Jamie McMillan

New Member
Joined
Nov 8, 2021
Messages
23
Office Version
  1. 2016
Platform
  1. 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

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
This will open a file in the same folder:

Workbooks.Open Filename:=ThisWorkbook.Path & "\yourfilename.xlsx"

Your code can be cut down a lot, :)


Range("O15").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-12]:R[3]C[-12])"

Range("o15").Value = "=Average(C3:R3)"

(remove the .select makes the code run faster and it looks nicer. :) (Using the the ranges instead of RC[-12] is a lot easier to follow).

The code above says - Cell O15 = TEXT =Average(C3:R3)

But if you just have that formula in Cell O15 it will calculate the average anyway. So you just need to copy Cell O15 and then paste it.

EG - Range("C5:F5").Copy
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
2,131
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
@BetterAardvark see how this works:

VBA Code:
Sub Shorten()
'
    Dim wb2 As Workbook
    Dim wb3 As Workbook
'
    Set wb2 = Workbooks("MT ASSAY 15 16 TET OFF 11152021 Nov Tue 16 2021 10-27-41-1723314.xlsx")    ' <--- Set this to the proper 2nd workbook
    Set wb3 = Workbooks("MT ASSAY 15 16 TET ON 11152021 Nov Tue 16 2021 10-12-19-9557578.xlsx")     ' <--- Set this to the proper 3rd workbook
'
    ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-12]:R[3]C[-12])"
'
    Range("O15").Formula = "=AVERAGE(C15:C18)"
    Range("C21").Formula = "=C11/$O$11*100"
    Range("C21").AutoFill Destination:=Range("C21:C24")
    Range("C21:C24").AutoFill Destination:=Range("C21:M24")
    Range("C25").Formula = "=C15/$O$15*100"
    Range("C25").AutoFill Destination:=Range("C25:C28")
    Range("C25:C28").AutoFill Destination:=Range("C25:M28")
    Range("O11:O15").Copy
'
    wb2.Activate
    Range("O11").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'
    wb3.Activate
    Range("C21:M28").Copy
'
    wb2.Activate
    Range("C21").Select
    ActiveSheet.Paste
'
    Application.CutCopyMode = False
End Sub

I gave you variables for the other two workbooks at the top of the code. Look for the comments that have ' <---' in them.
 

BetterAardvark

New Member
Joined
Nov 17, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks for your suggestions all! @johnnyL I tried your code and I am getting a subscript out of range error when I set the workbook variables to the intended workbook. Also, would I have to switch which workbooks are used for the variables when I want to analyze other workbooks? Is there a way to change which workbooks are used with the variables without changing the code each time? Maybe by opening the file explorer and choosing which workbooks to use as the variables?
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
2,131
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Are workbook2 & workbook 3 already going to be open, or do they need to be selected and opened?
 

BetterAardvark

New Member
Joined
Nov 17, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I could do either. The way I am doing it now, I usually will have everything open
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
2,131
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
How about:

VBA Code:
Sub ShortenV2()
'
    Dim UserSelectedFile    As Variant
    Dim UserSelectedFile1   As String
    Dim UserSelectedFile2   As String
    Dim wb2                 As Workbook
    Dim wb3                 As Workbook
'
    UserSelectedFile = Application.GetOpenFilename(Title:="Please select Workbook 2", FileFilter:="Excel Files *.xlsx (*.xlsx),")
    If UserSelectedFile = "False" Then Exit Sub                                     ' If user cancelled then exit sub
    Set wb2 = Workbooks.Open(UserSelectedFile)
'
    UserSelectedFile = Application.GetOpenFilename(Title:="Please select Workbook 3", FileFilter:="Excel Files *.xlsx (*.xlsx),")
    If UserSelectedFile = "False" Then Exit Sub                                     ' If user cancelled then exit sub
    Set wb3 = Workbooks.Open(UserSelectedFile)
'
    ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-12]:R[3]C[-12])"
'
    Range("O15").Formula = "=AVERAGE(C15:C18)"
    Range("C21").Formula = "=C11/$O$11*100"
    Range("C21").AutoFill Destination:=Range("C21:C24")
    Range("C21:C24").AutoFill Destination:=Range("C21:M24")
    Range("C25").Formula = "=C15/$O$15*100"
    Range("C25").AutoFill Destination:=Range("C25:C28")
    Range("C25:C28").AutoFill Destination:=Range("C25:M28")
    Range("O11:O15").Copy
'
    wb2.Activate
    Range("O11").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'
    wb3.Activate
    Range("C21:M28").Copy
'
    wb2.Activate
    Range("C21").Select
    ActiveSheet.Paste
'
    Application.CutCopyMode = False
End Sub
 
Solution

BetterAardvark

New Member
Joined
Nov 17, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
That works! Thank you very much for your help! You saved me a lot of time
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,564
Messages
5,770,886
Members
425,649
Latest member
cbTexas

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