make specific workbook macrorecording work with any workbook

V

valmir

Active Member
Joined
Feb 10, 2021
Messages
270
Office Version
  1. 365
Platform
  1. Windows
Hi! I have this macrorecording that was made for this workbook named "Stats 2013". Can it be changed so that it will work with any workbook regardless of the name?
VBA Code: 
Sub Adding_G_P_2()
'
' Adding_G_P_2 Macro
'

'
    Cells.Select
    Selection.EntireColumn.Hidden = False
    Range("A5:B42").Select
    Selection.FormatConditions.Delete
    Windows("zStats test.xlsx").Activate
    Range("A5:B42").Select
    Selection.Copy
    Windows("Stats 2013.xlsx").Activate
    Range("A5:B42").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("D5").Select
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("D5:D42"), Type:=xlFillDefault
    Range("D5:D42").Select
    Range("G5").Select
    Selection.AutoFill Destination:=Range("G5:G42"), Type:=xlFillDefault
    Range("G5:G42").Select
    Range("I5").Select
    Selection.AutoFill Destination:=Range("I5:I42"), Type:=xlFillDefault
    Range("I5:I42").Select
    Range("K5").Select
    Selection.AutoFill Destination:=Range("K5:K42"), Type:=xlFillDefault
    Range("K5:K42").Select
    Range("M5").Select
    Selection.AutoFill Destination:=Range("M5:M42"), Type:=xlFillDefault
    Range("M5:M42").Select
    Range("O5:P5").Select
    Selection.AutoFill Destination:=Range("O5:P45"), Type:=xlFillDefault
    Range("O5:P45").Select
    Range("M43:R46").Select
    Range("R46").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("G5:G42,I5:I42,K5:K42,M5:M42,O5:O42,Q5:Q42").Select
    Range("Q5").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("R5:DL43").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("BD50").Select
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("I:I,J:J,M:M,N:N").Select
    Range("N1").Activate
    Selection.EntireColumn.Hidden = True
    Range("C45").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 36
    ActiveWindow.ScrollColumn = 37
    ActiveWindow.ScrollColumn = 38
    ActiveWindow.ScrollColumn = 39
    ActiveWindow.ScrollColumn = 40
    ActiveWindow.ScrollColumn = 41
    ActiveWindow.ScrollColumn = 45
    ActiveWindow.ScrollColumn = 46
    ActiveWindow.ScrollColumn = 47
    ActiveWindow.ScrollColumn = 51
    ActiveWindow.ScrollColumn = 53
    ActiveWindow.ScrollColumn = 54
    ActiveWindow.ScrollColumn = 55
    ActiveWindow.ScrollColumn = 60
    ActiveWindow.ScrollColumn = 61
    ActiveWindow.ScrollColumn = 63
    ActiveWindow.ScrollColumn = 67
    ActiveWindow.ScrollColumn = 68
    ActiveWindow.ScrollColumn = 69
    ActiveWindow.ScrollColumn = 70
    ActiveWindow.ScrollColumn = 71
    ActiveWindow.ScrollColumn = 73
    ActiveWindow.ScrollColumn = 74
    ActiveWindow.ScrollColumn = 75
    ActiveWindow.ScrollColumn = 76
    ActiveWindow.ScrollColumn = 79
    ActiveWindow.ScrollColumn = 80
    ActiveWindow.ScrollColumn = 81
    ActiveWindow.ScrollColumn = 83
    ActiveWindow.ScrollColumn = 84
    ActiveWindow.ScrollColumn = 87
    ActiveWindow.ScrollColumn = 92
    ActiveWindow.ScrollColumn = 93
    ActiveWindow.ScrollColumn = 94
    ActiveWindow.ScrollColumn = 95
    ActiveWindow.ScrollColumn = 96
    ActiveWindow.ScrollColumn = 97
    ActiveWindow.ScrollColumn = 98
    ActiveWindow.ScrollColumn = 100
    ActiveWindow.ScrollColumn = 101
    ActiveWindow.ScrollColumn = 104
    ActiveWindow.ScrollColumn = 105
    ActiveWindow.ScrollColumn = 106
    ActiveWindow.ScrollColumn = 109
    ActiveWindow.ScrollColumn = 110
    ActiveWindow.ScrollColumn = 111
    ActiveWindow.ScrollColumn = 116
    ActiveWindow.ScrollColumn = 117
    ActiveWindow.ScrollColumn = 118
    ActiveWindow.ScrollColumn = 119
    ActiveWindow.ScrollColumn = 120
    ActiveWindow.ScrollColumn = 121
    ActiveWindow.ScrollColumn = 128
    ActiveWindow.ScrollColumn = 129
    ActiveWindow.ScrollColumn = 130
    ActiveWindow.ScrollColumn = 131
    ActiveWindow.ScrollColumn = 132
    ActiveWindow.ScrollColumn = 134
    ActiveWindow.ScrollColumn = 136
    ActiveWindow.ScrollColumn = 137
    ActiveWindow.ScrollColumn = 138
    ActiveWindow.ScrollColumn = 139
    ActiveWindow.ScrollColumn = 140
    Columns("EY:FL").Select
    ActiveWindow.ScrollColumn = 141
    ActiveWindow.ScrollColumn = 142
    ActiveWindow.ScrollColumn = 143
    ActiveWindow.ScrollColumn = 144
    ActiveWindow.ScrollColumn = 145
    ActiveWindow.ScrollColumn = 146
    ActiveWindow.ScrollColumn = 147
    ActiveWindow.ScrollColumn = 148
    ActiveWindow.ScrollColumn = 149
    ActiveWindow.ScrollColumn = 150
    ActiveWindow.ScrollColumn = 151
    ActiveWindow.ScrollColumn = 152
    Range("EY:FL,FZ:GL").Select
    Range("FZ1").Activate
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollColumn = 150
    ActiveWindow.ScrollColumn = 149
    ActiveWindow.ScrollColumn = 148
    ActiveWindow.ScrollColumn = 146
    ActiveWindow.ScrollColumn = 138
    ActiveWindow.ScrollColumn = 135
    ActiveWindow.ScrollColumn = 134
    ActiveWindow.ScrollColumn = 129
    ActiveWindow.ScrollColumn = 128
    ActiveWindow.ScrollColumn = 127
    ActiveWindow.ScrollColumn = 126
    ActiveWindow.ScrollColumn = 125
    ActiveWindow.ScrollColumn = 124
    ActiveWindow.ScrollColumn = 123
    ActiveWindow.ScrollColumn = 121
    ActiveWindow.ScrollColumn = 120
    ActiveWindow.ScrollColumn = 119
    ActiveWindow.ScrollColumn = 118
    ActiveWindow.ScrollColumn = 117
    ActiveWindow.ScrollColumn = 116
    ActiveWindow.ScrollColumn = 115
    ActiveWindow.ScrollColumn = 114
    ActiveWindow.ScrollColumn = 113
    ActiveWindow.ScrollColumn = 112
    ActiveWindow.ScrollColumn = 110
    ActiveWindow.ScrollColumn = 109
    ActiveWindow.ScrollColumn = 108
    ActiveWindow.ScrollColumn = 107
    ActiveWindow.ScrollColumn = 106
    ActiveWindow.ScrollColumn = 105
    ActiveWindow.ScrollColumn = 104
    ActiveWindow.ScrollColumn = 103
    ActiveWindow.ScrollColumn = 102
    ActiveWindow.ScrollColumn = 101
    ActiveWindow.ScrollColumn = 100
    ActiveWindow.ScrollColumn = 99
    ActiveWindow.ScrollColumn = 98
    ActiveWindow.ScrollColumn = 97
    ActiveWindow.ScrollColumn = 96
    ActiveWindow.ScrollColumn = 94
    ActiveWindow.ScrollColumn = 93
    ActiveWindow.ScrollColumn = 92
    ActiveWindow.ScrollColumn = 91
    ActiveWindow.ScrollColumn = 92
    Range("DX:DX,DZ:DZ,EG:EG,EI:EI").Select
    Range("EI1").Activate
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollColumn = 91
    ActiveWindow.ScrollColumn = 90
    ActiveWindow.ScrollColumn = 89
    ActiveWindow.ScrollColumn = 88
    ActiveWindow.ScrollColumn = 86
    ActiveWindow.ScrollColumn = 85
    ActiveWindow.ScrollColumn = 84
    ActiveWindow.ScrollColumn = 76
    ActiveWindow.ScrollColumn = 75
    ActiveWindow.ScrollColumn = 72
    ActiveWindow.ScrollColumn = 70
    ActiveWindow.ScrollColumn = 69
    ActiveWindow.ScrollColumn = 68
    ActiveWindow.ScrollColumn = 61
    ActiveWindow.ScrollColumn = 60
    ActiveWindow.ScrollColumn = 59
    ActiveWindow.ScrollColumn = 55
    ActiveWindow.ScrollColumn = 53
    ActiveWindow.ScrollColumn = 52
    ActiveWindow.ScrollColumn = 51
    ActiveWindow.ScrollColumn = 47
    ActiveWindow.ScrollColumn = 46
    ActiveWindow.ScrollColumn = 45
    ActiveWindow.ScrollColumn = 42
    ActiveWindow.ScrollColumn = 41
    ActiveWindow.ScrollColumn = 40
    ActiveWindow.ScrollColumn = 36
    Columns("DP:DP").Select
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 1
End Sub
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
What is the name of the sheet in the "zStats test.xlsx" workbook that you are copying from?
 
Upvote 0
In that case replace this
VBA Code: 
    Windows("zStats test.xlsx").Activate
    Range("A5:B42").Select
    Selection.Copy
    Windows("Stats 2013.xlsx").Activate
with
VBA Code: 
    Workbooks("zStats test.xlsx").Sheets("PRI").Range("A5:B42").Copy
 
Upvote 0
Solution
Fluff said:
In that case replace this
VBA Code: 
    Windows("zStats test.xlsx").Activate
    Range("A5:B42").Select
    Selection.Copy
    Windows("Stats 2013.xlsx").Activate
with
VBA Code: 
    Workbooks("zStats test.xlsx").Sheets("PRI").Range("A5:B42").Copy
Click to expand...
Thanks Fluff! It works. I'll try to replicate the solution in other recordings that I created. This is a one-for-all solution, I guess?
 
Upvote 0
You can also get rid of all the select & selections like
VBA Code: 
    Cells.EntireColumn.Hidden = False
    Range("A5:B42").FormatConditions.Delete
    Workbooks("zStats test.xlsx").Sheets("PRI").Range("A5:B42").Copy
    Range("A5:B42").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("D5").AutoFill Destination:=Range("D5:D42"), Type:=xlFillDefault
    Range("G5").AutoFill Destination:=Range("G5:G42"), Type:=xlFillDefault
    Range("I5").AutoFill Destination:=Range("I5:I42"), Type:=xlFillDefault
    Range("K5").AutoFill Destination:=Range("K5:K42"), Type:=xlFillDefault
    Range("M5").AutoFill Destination:=Range("M5:M42"), Type:=xlFillDefault
    Range("O5:P5").AutoFill Destination:=Range("O5:P45"), Type:=xlFillDefault
    With Range("M43:R46")
      .Borders(xlDiagonalDown).LineStyle = xlNone
      .Borders(xlDiagonalUp).LineStyle = xlNone
      .Borders(xlEdgeLeft).LineStyle = xlNone
      .Borders(xlEdgeTop).LineStyle = xlNone
      .Borders(xlEdgeBottom).LineStyle = xlNone
      .Borders(xlEdgeRight).LineStyle = xlNone
      .Borders(xlInsideVertical).LineStyle = xlNone
      .Borders(xlInsideHorizontal).LineStyle = xlNone
    End With
    With Range("G5:G42,I5:I42,K5:K42,M5:M42,O5:O42,Q5:Q42")
      .Borders(xlDiagonalDown).LineStyle = xlNone
      .Borders(xlDiagonalUp).LineStyle = xlNone
      With .Borders(xlEdgeLeft)
          .LineStyle = xlContinuous
          .ColorIndex = 0
          .TintAndShade = 0
          .Weight = xlThin
      End With
      .Borders(xlEdgeTop).LineStyle = xlNone
      .Borders(xlEdgeBottom).LineStyle = xlNone
      .Borders(xlEdgeRight).LineStyle = xlNone
      .Borders(xlInsideVertical).LineStyle = xlNone
      .Borders(xlInsideHorizontal).LineStyle = xlNone
    End With
 
Upvote 0
Fluff said:
You can also get rid of all the select & selections like
VBA Code: 
    Cells.EntireColumn.Hidden = False
    Range("A5:B42").FormatConditions.Delete
    Workbooks("zStats test.xlsx").Sheets("PRI").Range("A5:B42").Copy
    Range("A5:B42").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("D5").AutoFill Destination:=Range("D5:D42"), Type:=xlFillDefault
    Range("G5").AutoFill Destination:=Range("G5:G42"), Type:=xlFillDefault
    Range("I5").AutoFill Destination:=Range("I5:I42"), Type:=xlFillDefault
    Range("K5").AutoFill Destination:=Range("K5:K42"), Type:=xlFillDefault
    Range("M5").AutoFill Destination:=Range("M5:M42"), Type:=xlFillDefault
    Range("O5:P5").AutoFill Destination:=Range("O5:P45"), Type:=xlFillDefault
    With Range("M43:R46")
      .Borders(xlDiagonalDown).LineStyle = xlNone
      .Borders(xlDiagonalUp).LineStyle = xlNone
      .Borders(xlEdgeLeft).LineStyle = xlNone
      .Borders(xlEdgeTop).LineStyle = xlNone
      .Borders(xlEdgeBottom).LineStyle = xlNone
      .Borders(xlEdgeRight).LineStyle = xlNone
      .Borders(xlInsideVertical).LineStyle = xlNone
      .Borders(xlInsideHorizontal).LineStyle = xlNone
    End With
    With Range("G5:G42,I5:I42,K5:K42,M5:M42,O5:O42,Q5:Q42")
      .Borders(xlDiagonalDown).LineStyle = xlNone
      .Borders(xlDiagonalUp).LineStyle = xlNone
      With .Borders(xlEdgeLeft)
          .LineStyle = xlContinuous
          .ColorIndex = 0
          .TintAndShade = 0
          .Weight = xlThin
      End With
      .Borders(xlEdgeTop).LineStyle = xlNone
      .Borders(xlEdgeBottom).LineStyle = xlNone
      .Borders(xlEdgeRight).LineStyle = xlNone
      .Borders(xlInsideVertical).LineStyle = xlNone
      .Borders(xlInsideHorizontal).LineStyle = xlNone
    End With
Click to expand...
Ok thanks!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Issue with Macros and renaming files
Replies
6
Views
1K
kevin9999
K
R
  • Question Question
Formatting using VBA
Replies
0
Views
558
RandyD123
R
M
  • Question Question
VBS Crash
Replies
1
Views
776
Alex Blakenburg
A
G
  • Question Question
How to apply multiple conditional formatting when using macro recorder
Replies
0
Views
687
gazmoz17
G
L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
614
RoryA
RoryA

Forum statistics

Threads
1,225,482
Messages
6,185,259
Members
453,283
Latest member
Shortm88

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