Recognize workbooks by partial name / Recorded Macro / Novice user (very novice)

N

nathan1981ss

New Member
Joined
Jun 16, 2018
Messages
2
Help! my recorded macro is below. My issue is, workbook "billing.xlsm" name will vary such as: "billing 1" or "billing 2" etc.
I have tried applying solutions i have read in forums but i might be going about it wrong.



Code: 
[COLOR=#0000ff]Sub Auto_Copy_Invoice_From_Color()[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]    Range("Table12[[#Headers],[INV '#]]").Select[/COLOR]
[COLOR=#0000ff]    ActiveWorkbook.Worksheets("Color").ListObjects("Table12").Sort.SortFields.Clear[/COLOR]
[COLOR=#0000ff]    ActiveWorkbook.Worksheets("Color").ListObjects("Table12").Sort.SortFields.Add _[/COLOR]
[COLOR=#0000ff]        Key:=Range("Table12[[#Headers],[INV '#]]"), SortOn:=xlSortOnValues, Order _[/COLOR]
[COLOR=#0000ff]        :=xlDescending, DataOption:=xlSortNormal[/COLOR]
[COLOR=#0000ff]    With ActiveWorkbook.Worksheets("Color").ListObjects("Table12").Sort[/COLOR]
[COLOR=#0000ff]        .Header = xlYes[/COLOR]
[COLOR=#0000ff]        .MatchCase = False[/COLOR]
[COLOR=#0000ff]        .Orientation = xlTopToBottom[/COLOR]
[COLOR=#0000ff]        .SortMethod = xlPinYin[/COLOR]
[COLOR=#0000ff]        .Apply[/COLOR]
[COLOR=#0000ff]    End With[/COLOR]
[COLOR=#0000ff]    Rows("5:5").Select[/COLOR]
[COLOR=#0000ff]    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove[/COLOR]
[COLOR=#0000ff]    Range("A6:A7").Select[/COLOR]
[COLOR=#0000ff]    Range("A7").Activate[/COLOR]
[COLOR=#0000ff]    Selection.AutoFill Destination:=Range("A5:A7"), Type:=xlFillDefault[/COLOR]
[COLOR=#0000ff]    Range("A5:A7").Select[/COLOR]
[COLOR=#0000ff]    Range("A1").Select[/COLOR]
[COLOR=#0000ff]    Windows("Billing.xlsm").Activate[/COLOR]
[COLOR=#0000ff]    ActiveSheet.Unprotect[/COLOR]
[COLOR=#0000ff]    Sheets("G702 ").Select[/COLOR]
[COLOR=#0000ff]    ActiveSheet.Unprotect[/COLOR]
[COLOR=#0000ff]    Sheets("G 703").Select[/COLOR]
[COLOR=#0000ff]    ActiveSheet.Unprotect[/COLOR]
[COLOR=#0000ff]    Range("J2").Select[/COLOR]
[COLOR=#0000ff]    Selection.Copy[/COLOR]
[COLOR=#0000ff]    Windows("AR Log.xlsm").Activate[/COLOR]
[COLOR=#0000ff]    Range("H5").Select[/COLOR]
[COLOR=#0000ff]    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/COLOR]
[COLOR=#0000ff]        :=False, Transpose:=False[/COLOR]
[COLOR=#0000ff]    Windows("Billing.xlsm").Activate[/COLOR]
[COLOR=#0000ff]    Range("H2").Select[/COLOR]
[COLOR=#0000ff]    Application.CutCopyMode = False[/COLOR]
[COLOR=#0000ff]    Selection.Copy[/COLOR]
[COLOR=#0000ff]    Windows("AR Log.xlsm").Activate[/COLOR]
[COLOR=#0000ff]    Range("D5").Select[/COLOR]
[COLOR=#0000ff]    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/COLOR]
[COLOR=#0000ff]        :=False, Transpose:=False[/COLOR]
[COLOR=#0000ff]    Windows("Billing.xlsm").Activate[/COLOR]
[COLOR=#0000ff]    Sheets("Billing Cover Sheet").Select[/COLOR]
[COLOR=#0000ff]    Range("D5").Select[/COLOR]
[COLOR=#0000ff]    Application.CutCopyMode = False[/COLOR]
[COLOR=#0000ff]    Selection.Copy[/COLOR]
[COLOR=#0000ff]    Windows("AR Log.xlsm").Activate[/COLOR]
[COLOR=#0000ff]    Range("B5").Select[/COLOR]
[COLOR=#0000ff]    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/COLOR]
[COLOR=#0000ff]        :=False, Transpose:=False[/COLOR]
[COLOR=#0000ff]    Windows("Billing.xlsm").Activate[/COLOR]
[COLOR=#0000ff]    Range("D34").Select[/COLOR]
[COLOR=#0000ff]    Application.CutCopyMode = False[/COLOR]
[COLOR=#0000ff]    Selection.Copy[/COLOR]
[COLOR=#0000ff]    Windows("AR Log.xlsm").Activate[/COLOR]
[COLOR=#0000ff]    Range("C5").Select[/COLOR]
[COLOR=#0000ff]    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/COLOR]
[COLOR=#0000ff]        :=False, Transpose:=False[/COLOR]
[COLOR=#0000ff]    Range("E5").Select[/COLOR]
[COLOR=#0000ff]    Application.CutCopyMode = False[/COLOR]
[COLOR=#0000ff]    ActiveCell.FormulaR1C1 = "Yes"[/COLOR]
[COLOR=#0000ff]    Windows("Billing.xlsm").Activate[/COLOR]
[COLOR=#0000ff]    Range("C18").Select[/COLOR]
[COLOR=#0000ff]    Selection.Copy[/COLOR]
[COLOR=#0000ff]    Windows("AR Log.xlsm").Activate[/COLOR]
[COLOR=#0000ff]    Range("F5").Select[/COLOR]
[COLOR=#0000ff]    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/COLOR]
[COLOR=#0000ff]        :=False, Transpose:=False[/COLOR]
[COLOR=#0000ff]    Windows("Billing.xlsm").Activate[/COLOR]
[COLOR=#0000ff]    Range("C14").Select[/COLOR]
[COLOR=#0000ff]    Application.CutCopyMode = False[/COLOR]
[COLOR=#0000ff]    Selection.Copy[/COLOR]
[COLOR=#0000ff]    Windows("AR Log.xlsm").Activate[/COLOR]
[COLOR=#0000ff]    Range("G5").Select[/COLOR]
[COLOR=#0000ff]    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/COLOR]
[COLOR=#0000ff]        :=False, Transpose:=False[/COLOR]
[COLOR=#0000ff]    Windows("Billing.xlsm").Activate[/COLOR]
[COLOR=#0000ff]    Range("B6").Select[/COLOR]
[COLOR=#0000ff]    Application.CutCopyMode = False[/COLOR]
[COLOR=#0000ff]    Selection.Copy[/COLOR]
[COLOR=#0000ff]    Windows("AR Log.xlsm").Activate[/COLOR]
[COLOR=#0000ff]    Range("I5").Select[/COLOR]
[COLOR=#0000ff]    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/COLOR]
[COLOR=#0000ff]        :=False, Transpose:=False[/COLOR]
[COLOR=#0000ff]    Windows("Billing.xlsm").Activate[/COLOR]
[COLOR=#0000ff]    Range("B7").Select[/COLOR]
[COLOR=#0000ff]    Application.CutCopyMode = False[/COLOR]
[COLOR=#0000ff]    Selection.Copy[/COLOR]
[COLOR=#0000ff]    Windows("AR Log.xlsm").Activate[/COLOR]
[COLOR=#0000ff]    Range("J5").Select[/COLOR]
[COLOR=#0000ff]    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/COLOR]
[COLOR=#0000ff]        :=False, Transpose:=False[/COLOR]
[COLOR=#0000ff]    Windows("Billing.xlsm").Activate[/COLOR]
[COLOR=#0000ff]    Range("D30").Select[/COLOR]
[COLOR=#0000ff]    Application.CutCopyMode = False[/COLOR]
[COLOR=#0000ff]    Selection.Copy[/COLOR]
[COLOR=#0000ff]    Windows("AR Log.xlsm").Activate[/COLOR]
[COLOR=#0000ff]    Range("Q5").Select[/COLOR]
[COLOR=#0000ff]    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/COLOR]
[COLOR=#0000ff]        :=False, Transpose:=False[/COLOR]
[COLOR=#0000ff]    Windows("Billing.xlsm").Activate[/COLOR]
[COLOR=#0000ff]    Range("D31").Select[/COLOR]
[COLOR=#0000ff]    Application.CutCopyMode = False[/COLOR]
[COLOR=#0000ff]    Selection.Copy[/COLOR]
[COLOR=#0000ff]    Windows("AR Log.xlsm").Activate[/COLOR]
[COLOR=#0000ff]    Range("R5").Select[/COLOR]
[COLOR=#0000ff]    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/COLOR]
[COLOR=#0000ff]        :=False, Transpose:=False[/COLOR]
[COLOR=#0000ff]    Windows("Billing.xlsm").Activate[/COLOR]
[COLOR=#0000ff]    Range("D22").Select[/COLOR]
[COLOR=#0000ff]    Application.CutCopyMode = False[/COLOR]
[COLOR=#0000ff]    Selection.Copy[/COLOR]
[COLOR=#0000ff]    Windows("AR Log.xlsm").Activate[/COLOR]
[COLOR=#0000ff]    Range("S5").Select[/COLOR]
[COLOR=#0000ff]    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/COLOR]
[COLOR=#0000ff]        :=False, Transpose:=False[/COLOR]
[COLOR=#0000ff]    Range("A1").Select[/COLOR]
[COLOR=#0000ff]    Windows("Billing.xlsm").Activate[/COLOR]
[COLOR=#0000ff]    Application.CutCopyMode = False[/COLOR]
[COLOR=#0000ff]    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True[/COLOR]
[COLOR=#0000ff]    Sheets("G702 ").Select[/COLOR]
[COLOR=#0000ff]    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True[/COLOR]
[COLOR=#0000ff]    Sheets("G 703").Select[/COLOR]
[COLOR=#0000ff]    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True[/COLOR]
[COLOR=#0000ff]    Sheets("Billing Cover Sheet").Select[/COLOR]
[COLOR=#0000ff]    Windows("AR Log.xlsm").Activate[/COLOR]
[COLOR=#0000ff]    Range("A5").Select[/COLOR]
[COLOR=#0000ff]    Selection.Copy[/COLOR]
[COLOR=#0000ff]    Windows("Billing.xlsm").Activate[/COLOR]
[COLOR=#0000ff]    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/COLOR]
[COLOR=#0000ff]        :=False, Transpose:=False[/COLOR]
[COLOR=#0000ff]    Windows("AR Log.xlsm").Activate[/COLOR]
[COLOR=#0000ff]    Range("A1").Select[/COLOR]
[COLOR=#0000ff]    Application.CutCopyMode = False[/COLOR]
[COLOR=#0000ff]    Range("A6:S6").Select[/COLOR]
[COLOR=#0000ff]    Selection.Copy[/COLOR]
[COLOR=#0000ff]    Range("A5:S5").Select[/COLOR]
[COLOR=#0000ff]    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _[/COLOR]
[COLOR=#0000ff]        SkipBlanks:=False, Transpose:=False[/COLOR]
[COLOR=#0000ff]    Application.CutCopyMode = False[/COLOR]
[COLOR=#0000ff]    Range("A1").Select[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]
 
Last edited by a moderator:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Welcome to the MrExcel board!

Recording macros is a great way to start but that tends to produce fairly long and inefficient code and yours certainly appears to be like that. I'm not attempting to iron out any of those inefficiencies, but address your specific question about dealing with the different possible "billing" workbook names. I'm assuming that the "billing" workbook, no matter what its exact name is will be open when the code is run.

Put this near the start of your code
Code: 
Dim wb As Workbook, wbBilling As Workbook

For Each wb In Workbooks
  If LCase(wb.Name) Like "billing*.xlsm" Then
    Set wbBilling = wb
    Exit For
  End If
Next wb

Then everywhere that you have
Code: 
Windows("Billing.xlsm").Activate
you can replace that whole line with
Code: 
wbBilling.Activate
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
162
shknbk2
S
S
  • Solved
VBA does not continue to copy after blank cell
Replies
8
Views
520
jolivanes
jolivanes
P
  • Solved
Counter in For Each loop
Replies
2
Views
192
Possum17
P
P
  • Solved
Need help with macro Dynamic Range??
Replies
6
Views
169
pennbowl
P
A
  • Question
Macro to update list to last row on another sheet copy source from multiple sheets
Replies
0
Views
303
Azamamir
A

Forum statistics

Threads
1,214,817
Messages
6,121,720
Members
449,050
Latest member
MiguekHeka

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