VBA Macro Format Excel Sheet

[Kelly Warden]

Hello-

I am new to this group. On a new tab I would like to create a button to run a VBA Macro to re format the data in the worksheet. On tab 1, i have the original data file and on tab 2, is what i would like the result to be.

Basically, I need to do the following:
- add a column to the spreadsheet called "Type of Savings"
- if in column C the "type of savings" is savings
- if in column D the "type of savings" is cost avoidance
- move columns C & D to be in the same column

 

[chirayu]

Here u go - try it out

 

[Kelly Warden]

Could you do something like this example? I tried to modify your code, but couldn't figure it out.

 

[chirayu]

Hi Kelly,

Tab 2 has more fields than Tab 1 so I'm not sure as to how the Macro would cover all fields as all the data from Tab 2 is not present in Tab 1

Also here's a breakup of my code

Sub test()
'select sheet 2 and delete old data
Worksheets("Tab2").Select
ActiveSheet.Cells.Delete
 
'select sheet 1 and copy new data to sheet 2
Worksheets("Tab 1").Select
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Worksheets("Tab2").Select
Range("A1").Select
ActiveSheet.Paste
 
'add a new column for type of savings
Worksheets("Tab2").Columns("C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.Value = "Type of Savings"
 
'write savings in front of the savings data
Range("C2").Select
ActiveCell.Value = "Savings"
ActiveCell.Offset(0, 1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
 
'paste cost avoidance under savings data
Range("E2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Range("D2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
 
'write cost avoidance in front of the cost avoidance data
ActiveCell.Offset(0, -1).Select
ActiveCell.Value = "Cost Avoidance"
ActiveCell.Offset(0, 1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
 
'delete the empty cost avoidance column
ActiveSheet.Columns("E").Delete
 
'copy year and category in front of the cost avoidance data
Range("A2:B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
 
'close selection of range and go to A1
Application.CutCopyMode = False
Range("A1").Select
 
End Sub

 

[Kelly Warden]

There won't be data in some columns, however, I still need these columns so they line up when I consolidate all the workbooks.

 

[chirayu]

Hi Kelly,
Here you go - check it to see if its ok - file attached

Sub test()
 
'clear old data from tab 2
Worksheets("Tab2").Select
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents
 
'copy year from tab 1
Worksheets("Tab 1").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
 
'paste year in year field in tab 2
Worksheets("Tab2").Select
Range("C2").Select
ActiveSheet.Paste
Selection.Copy
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
 
'copy region from tab 1
Worksheets("Tab 1").Select
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
 
'paste region in category field in tab 2
Worksheets("Tab2").Select
Range("E2").Select
ActiveSheet.Paste
Selection.Copy
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
 
'copy tracking ID from tab 1
Worksheets("Tab 1").Select
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
 
'paste tracking ID in project name field in tab 2
Worksheets("Tab2").Select
Range("D2").Select
ActiveSheet.Paste
Selection.Copy
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
 
'copy sub category from tab 1
Worksheets("Tab 1").Select
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
 
'paste sub category in global sub category field in tab 2
Worksheets("Tab2").Select
Range("N2").Select
ActiveSheet.Paste
Selection.Copy
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
 
'copy savings data from tab 1
Worksheets("Tab 1").Select
Range("F2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
 
'paste savings data in approved savings field in tab 2
Worksheets("Tab2").Select
Range("K2").Select
ActiveSheet.Paste
 
'write savings in front of savings data
Range("J2").Select
ActiveCell.Value = "Savings"
ActiveCell.Offset(0, 1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
 
'copy cost avoidance data from tab 1
Worksheets("Tab 1").Select
Range("G2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
 
'paste cost avoidance data in approved savings field in tab 2 below savings data
Worksheets("Tab2").Select
Range("K2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
 
'write cost avoidance in front of cost avoidance data
Range("J2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "Cost Avoidance"
ActiveCell.Offset(0, 1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
 
'copy approved savings field to approved savings in USD field
Range("K2:L2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillRight
 
End Sub