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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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

willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
280
willow1985
willow1985
D
  • Solved
extract to another workbook and save using desktop path
2
Replies
12
Views
191
jolivanes
jolivanes
K
  • Question Question
Increasing row before the macro runs again
Replies
1
Views
211
kbk
K
N
  • Question Question
Adding to an existing MACRO
Replies
2
Views
103
Northern NY Design
N
S
  • Question Question
Dynamic Address by VBA
Replies
8
Views
152
MikeVol
MikeVol

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

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