Macro to recognize a workbook with variable name

[Mangisqa]

Hello,

I am trying to write a macro to reference workbooks that have diffrent prefixes.

Windows("Regulatory Report.XLSB").Activate
Columns("A:A").Select
Selection.Copy
Windows("BOReport.XLSX").Activate

I would like the code to recognize the above files if they contain a variable prefix (ie Area1Regulatory Report or Area7BOReport) depending on the location using the macro.

Thank you in advance!

 

[Damon Ostrander]

Hi Mangisqa,

Here is a user-defined function (UDF) you can use to enable you to find the workbook that matches a name pattern:

Function WkbkName(MatchName As String) As String
   'Returns the name of a workbook matching the name MatchName.  MatchName is a workbook
   'name pattern that may contain wildcards *, ?, [abc].  If no workbook matches, the
   'null string is returned
   Dim Wkbk     As Workbook
   For Each Wkbk In Workbooks
      If Wkbk.Name Like MatchName Then
         WkbkName = Wkbk.Name
         Exit Function
      End If
   Next Wkbk
   WkbkName = ""
End Function

Just place this code in your macro module. Then you can use it in your code like this:

Dim SourceWB   As String   'Name of source workbook
Dim DestWB      As String   'Name of destination workbook

SourceWB  = WkbkName("*Regulatory Report*")
DestWB = WkbkName("*BOReport*")

Workbooks(SourceWB).Columns("A:A").Copy

Windows(DestWB).Activate
etc.

Note that you don't actually have to activate the source workbook in order to copy a range from it. Nor do you have to activate the destination workbook to paste it, but I left it that way because you probably want to view it when the data are pasted.

Keep Excelling.

Damon

 

[Mangisqa]

This absolutely did the trick! and thank you for the explanations, as some one who is has a lot of learning to do in this realm, it definitely helps me understand what the coding means and how I can use it in different situations!

Thank you!