There is a script that generates warranty data for thousands of servers. The data is in log format and needs to be converted to Excel table format for analysis and pivots creation.

From this

1.png

To this

2.png

We shall record and NOT write the code that we shall modify later.

We start by recording a Quick and dirty code for Pivots that shall be used in our Final code.

Go to View – > Macros – > Record Macro

  1. We select the rows that need to be converted to tabular form. In this case Rows 2 to 10

3.png

2. Copy the data and paste it into a new sheet. New sheet can be added by shortcut Shift + F11

4.png

3. Rename the sheet to “Temp”

4. Start Cut Pasting the required fields to Row 20. We are creating a single row for entire Data

5.png5. Copy the entire Row 20 and insert it at the 2nd row of “Consolidated” Sheet.

6.png

6 . Delete the Temp sheet

7. Stop the Macro and go to the code, Alt + F11

It should like this; Good news is we don’t need to go through this entire code. We will copy and paste it entirely. Just read it and see how each of your steps was meticulously recorded by VBA.

Let’s have a close look at the code to understand

/////////////////////////////////////////////////////////////////////////////////////////

 

Sub Macro1()

‘ Macro1 Macro

 

Sheets(“HPC_Warranty_Delta”).Select ‘Selects the sheet

Rows(“2:10”).Select ‘Selects the Rows

Selection.Copy

Sheets.Add After:=Sheets(Sheets.Count) ‘Adds a Sheet

Sheets(“Sheet5”).Select

Sheets(“Sheet5”).Name = “Temp” ‘Renames  the sheet

Rows(“1:1”).Select

Selection.Insert Shift:=xlDown

Range(“B1”).Select ‘Cut Pasting the data to Row 20

Application.CutCopyMode = False

Selection.Cut

Range(“A20”).Select

ActiveSheet.Paste

Range(“B2”).Select

Selection.Cut

Range(“B20”).Select

ActiveSheet.Paste

Range(“B3”).Select

Selection.Cut

Range(“C20”).Select

ActiveSheet.Paste

Range(“B4”).Select

Selection.Cut

Range(“D20”).Select

ActiveSheet.Paste

Range(“B5”).Select

Selection.Cut

Range(“E20”).Select

ActiveSheet.Paste

Range(“B6”).Select

Selection.Cut

Range(“F20”).Select

ActiveSheet.Paste

Range(“A9:H9”).Select

Selection.Cut

Range(“G20”).Select

ActiveSheet.Paste

Rows(“20:20”).Select ‘Selects the Row 20 where the data is

Selection.Copy

Sheets(“Consolidated”).Select

Rows(“2:2”).Select

Selection.Insert Shift:=xlDown

Range(“F32”).Select

Sheets(“Temp”).Select

Application.CutCopyMode = False

ActiveWindow.SelectedSheets.Delete ‘Deletes the sheet

 

End Sub

/////////////////////////////////////////////////////////////////////////////////////////

The modified code is described below with the changes done and why. Remember in the starting of this chapter we defined MyMacro2 , Now is the time to copy edit the code from Macro1 and add it to MyMacro2

Sub MyMacro2()

With Application

.ScreenUpdating = False

.EnableEvents = False

.DisplayAlerts = False

End With

‘This code is somewhat of a tricky, this stops from Excel screen to be refreshed thus speeds up the computation and also stops from displaying events such as warning of deleting the sheet.

Dim datasheet As Worksheet

Set datasheet = ActiveWorkbook.Worksheets(“HPC_Warranty_Delta”)

For i = 1 To datasheet.UsedRange.Rows.Count ‘Running the code to

‘Running the code to the Last row of Raw Data

datasheet.Select

If datasheet.Cells(i, 1) = “” Then GoTo mylabel:

‘Check that row is not blank, If yes then skip the loop, Understand the Labels are not considered good practice in programing, but we are playing dirty.

Rows(i & “:” & i + 8).Select ‘Compare this with the code on the top, We have made it generic to I thus can loop.

Selection.Copy

i = i + 9 ‘Adding 9 Since in next loop I can move to next set

 

datasheet.Select

Selection.Copy

Sheets.Add After:=Sheets(Sheets.Count)

ActiveSheet.Name = “Temp” ‘See how we rename the sheet to Temp

Rows(“1:1”).Select

Selection.Insert Shift:=xlDown

Range(“B1”).Select

Application.CutCopyMode = False

Selection.Cut

Range(“A20”).Select

ActiveSheet.Paste

Range(“B2”).Select

Selection.Cut

Range(“B20”).Select

ActiveSheet.Paste

Range(“B3”).Select

Selection.Cut

Range(“C20”).Select

ActiveSheet.Paste

Range(“B4”).Select

Selection.Cut

Range(“D20”).Select

ActiveSheet.Paste

Range(“B5”).Select

Selection.Cut

Range(“E20”).Select

ActiveSheet.Paste

Range(“B6”).Select

Selection.Cut

Range(“F20”).Select

ActiveSheet.Paste

Range(“A9:H9”).Select

Selection.Cut

Range(“G20”).Select

ActiveSheet.Paste

Rows(“20:20”).Select

Selection.Copy

Sheets(“Consolidated”).Select

Rows(“2:2”).Select

Selection.Insert Shift:=xlDown

Range(“F32”).Select

Sheets(“Temp”).Select

Application.CutCopyMode = False

ActiveWindow.SelectedSheets.Delete

 

mylabel:

Next i

With Application

.ScreenUpdating = True

.EnableEvents = True

.DisplayAlerts = True

End With

‘It is very important to enable the application events to proceed further.

End Sub

/////////////////////////////////////////////////////////////////////////////////////////

Final Step

To run the macros simply select the sheet on which raw data is and run MyMacro2.

Whew: Hopefully this clears on how to dirty use the macro recorder to get the job done.

Attached is the file with code to test.

Unsymmetrical Data conolidate

Cheers

CuriousJatin

 

Advertisements