Changing between worksheets with similar names in macros

B

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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
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])"
    '...
 
Upvote 0
BetterAardvark said:
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
Click to expand...
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
 
Upvote 0
@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.
 
Upvote 0
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?
 
Upvote 0
Are workbook2 & workbook 3 already going to be open, or do they need to be selected and opened?
 
Upvote 0
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
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

P
  • Solved
Recorded Macro Ammendments
Replies
3
Views
506
Joe4
Joe4
F
  • Solved
Copy selected range to another worksheet, stop, and go to the next record to select (VBA)
Replies
3
Views
204
Alex Blakenburg
A
B
  • Question Question
VBA causing cell to change incorrectly
Replies
9
Views
540
Bouncy Fire
B
Lacan
  • Solved
Make VBA codes according to one specific sheet and not all entire workbook?
2
Replies
11
Views
365
Lacan
Lacan
J
  • Question Question
Issue with Macros and renaming files
Replies
6
Views
1K
kevin9999
K

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

Share this page

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
Back
Top