Using microsoft excel macros to merge and format worksheets

Most of the common repetitive tasks involved in an excel worksheet can be automated using macro functions. For example in my job every week i get an excel worksheets emailed to me, displaying the price changes of products. Since these worksheets are always in the same format, for me to update them into our live system, instead of doing the manual job of formatting the fields according to my systems field and deleting unwanted fields (columns) from the sheet, i use macros to do this. 

The first thing  before you do any is to change your settings in your excel by going to tools/options/macro settings

Here change the level to a comfortable one. Now open the excel sheet you like to format

1.Go to tools / macros / recrod new macro. While selecting this always select PERSONAL MACRO WORKBOOK. Doing so you will be reuse the same macro for any worksheet you open. If you don’t do this, once you close your worksheet this macro will not be available to you.

2. A small box will appear on your screen, this is is the stop button to click once you have finished recording the macro’s

3. In my case i want to first rename some of the fields in the worksheet, for example change item number to product no and so on

4. Then i want to delete some of the unwanted columns , also want to move a column from D to H column, by simply cut and paste function.

5. The last one i need to do is merge 3 columns and copy all that into another one and delete the merged columns. Now I cannot do this by common excel functions, i am going to write a VBA (Visual Basic for Applications) code.

6. Being said that, i have to stop my recording , hit on the small box that open on your screen, which stops the recording. Now close the excel , it will ask if you want to save the excel , say no to it since I havn’t completed my work yet. The next question is do you want to reopen your excel for the next time , say YES to this.

7. Now go back and open the  same excel and go to tools/macro, you will see your macro, if you havn’t renamed it will be usually macro1, click on that and either go to edit or step into. Step into mode the macro is already running. A new window opens with VBA codes. To write the function that i want, to merge columns 4,5,6 ie columns D, E, F into column C (2). Then delete columns D to F (D:F)

  Dim CellData As String

    Dim FirstRow As Long
    Dim LastRow As Long
    Dim CurrentRow As Long
FirstRow = 2
LastRow = [C65536].End(xlUp).Row
For CurrentRow = FirstRow To LastRow
CellData = Cells(CurrentRow, 3).Value & ” /” & Cells(CurrentRow, 4) & ” /” & Cells(CurrentRow, 5) & ” ” & Cells(CurrentRow, 6)
Cells(CurrentRow, 3) = CellData
 Next
 Columns(“D:F”).Select
Selection.Delete Shift:=xlToLeft

The over all code should look like this:

Sub Macro1()
” Macro1 Macro
‘ comments
  Dim CellData As String
 Dim FirstRow As Long
 Dim LastRow As Long
 Dim CurrentRow As Long
‘
  Range(“B1”).Select
 ActiveCell.FormulaR1C1 = “ProductNo”
 Range(“C1”).Select
 ActiveCell.FormulaR1C1 = “Description”
 Range(“D1”).Select
 ActiveCell.FormulaR1C1 = “UPC”
 Range(“J1”).Select
 ActiveCell.FormulaR1C1 = “Cost”
 Range(“H:I,K:K,L:L”).Select
 Range(“L1”).Activate
 Selection.Delete Shift:=xlToLeft
 Columns(“E:G”).Select
 Columns(“C:C”).EntireColumn.AutoFit
 Columns(“C:C”).ColumnWidth = 49.43
 Columns(“D:D”).Select
 Selection.Cut
 Columns(“H:H”).Select
 Selection.Insert Shift:=xlToRight
 FirstRow = 2
 LastRow = [C65536].End(xlUp).Row
 For CurrentRow = FirstRow To LastRow
 CellData = Cells(CurrentRow, 3).Value & ” /” & Cells(CurrentRow, 4) & ” /” &
Cells(CurrentRow, 5) & ” ” & Cells(CurrentRow, 6)
 Cells(CurrentRow, 3) = CellData
 Next
  Columns(“D:F”).Select
 Selection.Delete Shift:=xlToLeft
End Sub

Save the work and go back to the excel sheet, go to tools/macros and select your macro and run, see your macro do job for you…