03 Macro's to Format a Page in 03 ways

[Please_H]

Dear Experts of Mr.Excel,

Thanks again for all the Love and Support given in here...

Hope all of your Families and Friends are safe and sound from Covid-19.

This evening I am facing a new challenge having to do some formatting for countless number of Sheets and I am in a need of hand from the Experts of Macro.

Macro 1 - Once we Run, it :

01. Creates a Column Between 'Column (D)' and 'Column (E)'
02. Format all Cells to Calibri size 10
( Some Words and Numbers may be already Bold and some cells might have Formatting done with Borders and stuff. That Shouldn't Change )
03. Changes Column width to Column (A) : 20, Column (B) : 45, Column (C, D, E, F & G) : 17, Column (H) : 10
04. Copies Data from Sheet "IRFS16" Cell B3:H13 and Pastes ( Direct Copy Paste with Formatting and Equations and Stuff intact ) the Data to the Selected Cell at the time when the Macro was Run.

Macro 2 - Once we Run, it :

01. Changes Format for Selected Cells to "Accounting" with 2 Decimals.

Macro 3 - Once we Run, it :

01. Changes Alignment for Selected Cells to "Bottom Align".

Thanks a lot all of you...
Have a Great Day ahead...

 

[Joe4]

You should be able to get most of the code you need to do all of that by turning on the Macro Recorder, and record yourself performing those steps manually.
If you do that, and then turn off the Macro Recorder and view the code it recorded, you will have most of the code that you need.
I suggest that you try that first, and if there is anything that isn't dynamic or working correctly, post your code here along with an explanation of what isn't working right.

 

[Please_H]

You should be able to get most of the code you need to do all of that by turning on the Macro Recorder, and record yourself performing those steps manually.
If you do that, and then turn off the Macro Recorder and view the code it recorded, you will have most of the code that you need.
I suggest that you try that first, and if there is anything that isn't dynamic or working correctly, post your code here along with an explanation of what isn't working right.

Hey Joe4,

I had that in my mind first, but then thought due to the fact the Cell references change it may not work.
Sure, I'll do that...

 

[Please_H]

You should be able to get most of the code you need to do all of that by turning on the Macro Recorder, and record yourself performing those steps manually.
If you do that, and then turn off the Macro Recorder and view the code it recorded, you will have most of the code that you need.
I suggest that you try that first, and if there is anything that isn't dynamic or working correctly, post your code here along with an explanation of what isn't working right.

The below was the recorded,

- Macro 1

01. Request seem to be fine.
02. Request seem to be fine.
03. Request seem to be fine.

04. Gives 02 issues,

They are :

01. Pastes Not to the active Sheet but "Recovered_Sheet1 - test (2)".
02. Pastes to Cell A137 Only instead the Cell I am in,

Sub Macro1()
'
' Macro1 Macro
'
'
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Cells.Select
With Selection.Font
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection.Font
.Name = "Calibri"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Columns("A:A").Select
Selection.ColumnWidth = 20
Columns("B:B").Select
Selection.ColumnWidth = 45
Columns("C:G").Select
Selection.ColumnWidth = 17
Columns("H:H").Select
Selection.ColumnWidth = 10
ActiveWindow.SmallScroll Down:=123
Range("A137").Select
Sheets("IRFS16").Select
Range("B3:H13").Select
Selection.Copy
Sheets("Recovered_Sheet1 - test (2)").Select
ActiveSheet.Paste
Range("A149").Select
End Sub

 

[Please_H]

The below was the recorded,

- Macro 2

01. Gives 01 Definite issue,

That is :

01. Formats to Accounting only what I initially selected, the Macro isn't Dynamic.

Sub Macro2()
'
' Macro2 Macro
'
'
Range("C12:F19").Select
Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
End Sub

 

[Please_H]

The below was the recorded,

- Macro 3

01. Gives 01 Definite issue,

That is :

01. Alignment goes to Bottom Only what I initially selected, the Macro isn't Dynamic.

Sub Macro3()
'
' Macro3 Macro
'
'
Rows("10:49").Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = -1
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub

 

[Joe4]

04. Gives 02 issues,

They are :

01. Pastes Not to the active Sheet but "Recovered_Sheet1 - test (2)".
02. Pastes to Cell A137 Only instead the Cell I am in,

Regarding 01, I like to use worksheet objects. It makes it really easy to bounce back and forth.
So, if you want to capture the Active Sheet when you first start your macro, you can do so like this:

Dim ws1 as Worksheet
Set ws1=ActiveSheet

You can now go back to this sheet at any point like this:
ws1.Activate
You can also qualify your range, like if you wanted to set some cell equal to something, i.e.
ws1.Range("A1")= "Test


Regarding 02, very similar, you can capture the cell you are in when the macro first starts like this:

Dim rng1 as Range
set rng1=ActiveCell

and you can select this cell, or set it equal to values, i.e.
rng1.Select
or
rng1 = "Test"


Also note that the Macro Recorder is very literal and deliberate. So, where you are setting column widths, it first records you selecting the column, then it records you setting its width. You can combine those into one line (most every time you have one line end in "Select" and the next start with "Selection" or "ActiveCell", you can combine those.
For example, this:

Columns("A:A").Select
Selection.ColumnWidth = 20

can be simplified to this:

Columns("A:A").ColumnWidth = 20

Not only does it make your code shorter, it also makes it more efficient (selecting things slows the code down a little).
It is usually not necessary to actually select a range before working with it.

 

[Joe4]

The below was the recorded,

- Macro 2

01. Gives 01 Definite issue,

That is :

01. Formats to Accounting only what I initially selected, the Macro isn't Dynamic.

If you want some code to dynamically run against some range that you selected before starting the macro, there are two ways you can go about it:
1. Make your range selection BEFORE turning on the Macro Recorder.
or
2. Replace the range in your macro with the word "Selection", i.e.
this:

Range("C12:F19").Select
Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"

should just become:

Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"

Actually, whichever way you choose, you will end up with the same line of code posted above.
That is how you can make your macros dynamic, to run against any range you select before starting it.

Your last question above is the same thing. Just get rid of the range selection line, and just leave the "Selection" part, i.e.

Sub Macro3()
'
' Macro3 Macro
'
'
With Selection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = -1
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub