Changing the layout of an Excel 2010 SAP report

A

Anita Kay

New Member
Joined
Jan 26, 2017
Messages
8
Hello all,

I have been coming the archives of many forums trying to find a good, effective and safe solution for changing the layout of an SAP report exported to Excel 2010. It takes months and months to get them to provide us with information and while we have asked for raw data they seem hesitant to give it to us. They always want to provide us with a report. I am not proficient with VBA but can get around. I do not understand a lot about looping.

What I need to do it to take the spreadsheet and insert twelve rows after each existing record, copy the information from columns A:L into the blanks beneath each populated row. I do that often with GoTo, Special, Find Blanks and a simple formula like =A2. The real problem lies with M:X. The reports is given with twelve months of data laid out in each column. Row 1 is the Date, row 2 has the actual values. I want to copy and transpose the dates into a new field in column Y for each populated row and name it Purchase Date beginning with the first populated record and then bring the corresponding values for each date into column Z naming it Purchase Amount. If you can help I would greatly appreciate it. Sample data is below.
VENDOR NUMBER
VENDOR NAME
MATERIAL
DESCRIPTION
VENDOR PART NUMBER
UOM
Lead Time
QTY ON ORDER
QTY FCST (12 MO)
TOTAL QTY
Buyer
MATERIAL CONTROLLER
Oct-16
Nov-16
Dec-16
Jan-17
Feb-17
Mar-17
Apr-17
May-17
Jun-17
Jul-17
Aug-17
Sep-17
103326
Palole Corporation DBA
6000060
RES MEP-HYPERCEL
12035
L
169
200
360
560
606
R10
0
0
130
0
0
70
0
0
150
0
0
210
103326
Palole Corporation DBA
6000124
FLT MEMBRANE CASSETTE 30K MWCO
OS030C26
EA
61
195
0
195
606
R06
0
0
0
95
40
0
0
0
0
60
0
0
103326
Palole Corporation DBA
6000165
FLT CART 5" 0.2UM HYDROPHILIC
AB05DFL2PH4
EA
50
0
24
24
606
R06
0
0
0
0
0
0
6
0
0
6
12
0
103326
Palole Corporation DBA
6000167
FLT CASSETE 30K MWCO 1FT2
OS030C12
EA
62
0
12
12
606
R06
0
0
0
0
0
12
0
0
0
0
0
0
103326
Palole Corporation DBA
6000196
FLT CAPSULE 0.2UM 500CM2 STERI
12991
EA
74
0
12
12
606
R06
0
0
0
12
0
0
0
0
0
0
0
0
103326
Palole Corporation DBA
6000414
FLT MEM CASETTE OMEGA T-SERIES
OS010T06
EA
64
61
14
75
606
R06
6
2
33
20
0
0
0
0
14
0
0
0
103326
Palole Corporation DBA
6001060
FLT VENT 0.2UM PTFE HYDROPHOBI
4251
EA
70
0
59
59
606
R06
0
0
0
0
0
17
0
0
15
0
27
0
103326
Palole Corporation DBA
6001345
FLT T-SERIES 30KD MWCO 2.5M2 M
OS030T26
EA
64
36
26
62
606
R06
0
0
36
0
0
8
0
0
0
0
0
18
103393
Dill Palmer Instrument Co
6001166
ADP Y CONNECTOR 3/8" BARB KYNA
WU-30703-93
EA
19
0
760
760
606
R04
0
0
0
0
230
0
0
220
0
0
310
0
104008
Signal-Abbot Inc.
6000055
CHM TRANS-4 5-DIHYDROXY-1 2-DI
D3511
G
51
14,000
24,876
38,876
606
R01
0
14,000
0
4,876
0
0
10,000
0
5,000
0
5,000
0
104008
Signal-Abbot Inc.
6000069
CHM DEXTRAN SULFATESODIUM SALT
D6924
G
51
4,000
8,450
12,450
606
R01
0
2,000
2,000
0
3,000
0
0
0
2,750
0
2,700
0
107575
Univarlist Usa Inc
6000087
CHM ANTIFOAM-C MED GRADE SILIC
803579
G
20
0
906
906
605
R01
0
0
0
0
0
0
906
0
0
0
0
0

<tbody>
</tbody>
 
Sort by date Sort by votes
Anita Kay,

Sample new results in worksheet New Layout:


Excel 2007
ABCDEFGHIJKL
1MATERIALDESCRIPTIONVENDOR PART NUMBERUOMLead TimeQTY ON ORDERQTY FCST (12 MO)TOTAL QTYBuyerMATERIAL CONTROLLERProject DateActual Quantity
26000060RES MEP-HYPERCEL12035L169200360560606R10Oct-160
36000060RES MEP-HYPERCEL12035L169200360560606R10Nov-160
46000060RES MEP-HYPERCEL12035L169200360560606R10Dec-16130
56000060RES MEP-HYPERCEL12035L169200360560606R10Jan-170
66000060RES MEP-HYPERCEL12035L169200360560606R10Feb-170
76000060RES MEP-HYPERCEL12035L169200360560606R10Mar-1770
86000060RES MEP-HYPERCEL12035L169200360560606R10Apr-170
96000060RES MEP-HYPERCEL12035L169200360560606R10May-170
106000060RES MEP-HYPERCEL12035L169200360560606R10Jun-17150
116000060RES MEP-HYPERCEL12035L169200360560606R10Jul-170
126000060RES MEP-HYPERCEL12035L169200360560606R10Aug-170
136000060RES MEP-HYPERCEL12035L169200360560606R10Sep-17210
146000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Oct-160
156000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Nov-160
166000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Dec-160
176000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Jan-1795
186000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Feb-1740
196000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Mar-170
206000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Apr-170
216000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06May-170
226000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Jun-170
236000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Jul-1760
246000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Aug-170
256000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Sep-170
New Layout





Code: 
Sub Reorg_SAP_Report_V2()
' hiker95, 01/27/2017, ME987801
Dim a As Variant, i As Long, c As Long, cc As Long, d As Long, lr As Long, lc As Long
Dim o As Variant, j As Long, n As Long
Application.ScreenUpdating = False
With Sheets("Sample Data")    '<-- you can change the sheet name here
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  ReDim o(1 To (lr + 1) * 12, 1 To 12)
End With
For i = 2 To UBound(a, 1) Step 1
  cc = 13
  For n = 1 To 12 Step 1
    j = j + 1
    d = 1
    For c = 3 To 12 Step 1
      o(j, d) = a(i, c)
      d = d + 1
    Next c
    o(j, 11) = a(1, cc)
    o(j, 12) = a(i, cc)
    cc = cc + 1
  Next n
Next i
With Sheets("New Layout")    '<-- you can change the sheet name here
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  If lr > 1 Then
    .Range("A2:L" & lr).ClearContents
  End If
  .Range("A2").Resize(UBound(o, 1), UBound(o, 2)) = o
  .Range("K2").Resize(UBound(o, 1)).NumberFormat = "mmm-yy"
  .Columns(1).Resize(, UBound(o, 2)).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Same instructions as my last reply.

Then run the Reorg_SAP_Report_V2 macro.
 
Upvote 0

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Thanks so much Hiker95!!!! I will try this out this weekend and give you a shout back!

Anita Kay

hiker95 said:
Anita Kay,

Sample new results in worksheet New Layout:

Excel 2007
ABCDEFGHIJKL
1MATERIALDESCRIPTIONVENDOR PART NUMBERUOMLead TimeQTY ON ORDERQTY FCST (12 MO)TOTAL QTYBuyerMATERIAL CONTROLLERProject DateActual Quantity
26000060RES MEP-HYPERCEL12035L169200360560606R10Oct-160
36000060RES MEP-HYPERCEL12035L169200360560606R10Nov-160
46000060RES MEP-HYPERCEL12035L169200360560606R10Dec-16130
56000060RES MEP-HYPERCEL12035L169200360560606R10Jan-170
66000060RES MEP-HYPERCEL12035L169200360560606R10Feb-170
76000060RES MEP-HYPERCEL12035L169200360560606R10Mar-1770
86000060RES MEP-HYPERCEL12035L169200360560606R10Apr-170
96000060RES MEP-HYPERCEL12035L169200360560606R10May-170
106000060RES MEP-HYPERCEL12035L169200360560606R10Jun-17150
116000060RES MEP-HYPERCEL12035L169200360560606R10Jul-170
126000060RES MEP-HYPERCEL12035L169200360560606R10Aug-170
136000060RES MEP-HYPERCEL12035L169200360560606R10Sep-17210
146000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Oct-160
156000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Nov-160
166000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Dec-160
176000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Jan-1795
186000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Feb-1740
196000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Mar-170
206000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Apr-170
216000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06May-170
226000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Jun-170
236000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Jul-1760
246000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Aug-170
256000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Sep-170

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
New Layout​






Code: 
Sub Reorg_SAP_Report_V2()
' hiker95, 01/27/2017, ME987801
Dim a As Variant, i As Long, c As Long, cc As Long, d As Long, lr As Long, lc As Long
Dim o As Variant, j As Long, n As Long
Application.ScreenUpdating = False
With Sheets("Sample Data")    '<-- you can change the sheet name here
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  ReDim o(1 To (lr + 1) * 12, 1 To 12)
End With
For i = 2 To UBound(a, 1) Step 1
  cc = 13
  For n = 1 To 12 Step 1
    j = j + 1
    d = 1
    For c = 3 To 12 Step 1
      o(j, d) = a(i, c)
      d = d + 1
    Next c
    o(j, 11) = a(1, cc)
    o(j, 12) = a(i, cc)
    cc = cc + 1
  Next n
Next i
With Sheets("New Layout")    '<-- you can change the sheet name here
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  If lr > 1 Then
    .Range("A2:L" & lr).ClearContents
  End If
  .Range("A2").Resize(UBound(o, 1), UBound(o, 2)) = o
  .Range("K2").Resize(UBound(o, 1)).NumberFormat = "mmm-yy"
  .Columns(1).Resize(, UBound(o, 2)).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Same instructions per my reply #8.

Then run the Reorg_SAP_Report_V2 macro.
Click to expand...
 
Upvote 0
Thanks so much Hiker95!!!! I will try this out this weekend and give you a shout back!
Click to expand...

Anita Kay,

You are very welcome.

I will we watching for your next reply.
 
Upvote 0
OK HIker95,

I really appreciate all of your hard work. However,I made a mistake. All the columns in the Sample Data worksheet should showup in the New Layout until you get to those columns that house the date as a field name and the values for each record. When I copied over the data into the New Layout sheet, I really messed that up. I apologize. Otherwise, the restructuring looks good for those date fields and of course, adding the extra records for each line item.

The other issue is that the field names in row 1 do not show in the New layout.

If you could advise on how to modify the code, I would appreciate it greatly.

AnitKay

hiker95 said:
Anita Kay,

Sample new results in worksheet New Layout:

Excel 2007
ABCDEFGHIJKL
1MATERIALDESCRIPTIONVENDOR PART NUMBERUOMLead TimeQTY ON ORDERQTY FCST (12 MO)TOTAL QTYBuyerMATERIAL CONTROLLERProject DateActual Quantity
26000060RES MEP-HYPERCEL12035L169200360560606R10Oct-160
36000060RES MEP-HYPERCEL12035L169200360560606R10Nov-160
46000060RES MEP-HYPERCEL12035L169200360560606R10Dec-16130
56000060RES MEP-HYPERCEL12035L169200360560606R10Jan-170
66000060RES MEP-HYPERCEL12035L169200360560606R10Feb-170
76000060RES MEP-HYPERCEL12035L169200360560606R10Mar-1770
86000060RES MEP-HYPERCEL12035L169200360560606R10Apr-170
96000060RES MEP-HYPERCEL12035L169200360560606R10May-170
106000060RES MEP-HYPERCEL12035L169200360560606R10Jun-17150
116000060RES MEP-HYPERCEL12035L169200360560606R10Jul-170
126000060RES MEP-HYPERCEL12035L169200360560606R10Aug-170
136000060RES MEP-HYPERCEL12035L169200360560606R10Sep-17210
146000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Oct-160
156000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Nov-160
166000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Dec-160
176000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Jan-1795
186000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Feb-1740
196000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Mar-170
206000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Apr-170
216000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06May-170
226000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Jun-170
236000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Jul-1760
246000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Aug-170
256000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Sep-170

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
New Layout​






Code: 
Sub Reorg_SAP_Report_V2()
' hiker95, 01/27/2017, ME987801
Dim a As Variant, i As Long, c As Long, cc As Long, d As Long, lr As Long, lc As Long
Dim o As Variant, j As Long, n As Long
Application.ScreenUpdating = False
With Sheets("Sample Data")    '<-- you can change the sheet name here
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  ReDim o(1 To (lr + 1) * 12, 1 To 12)
End With
For i = 2 To UBound(a, 1) Step 1
  cc = 13
  For n = 1 To 12 Step 1
    j = j + 1
    d = 1
    For c = 3 To 12 Step 1
      o(j, d) = a(i, c)
      d = d + 1
    Next c
    o(j, 11) = a(1, cc)
    o(j, 12) = a(i, cc)
    cc = cc + 1
  Next n
Next i
With Sheets("New Layout")    '<-- you can change the sheet name here
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  If lr > 1 Then
    .Range("A2:L" & lr).ClearContents
  End If
  .Range("A2").Resize(UBound(o, 1), UBound(o, 2)) = o
  .Range("K2").Resize(UBound(o, 1)).NumberFormat = "mmm-yy"
  .Columns(1).Resize(, UBound(o, 2)).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Same instructions per my reply #8.

Then run the Reorg_SAP_Report_V2 macro.
Click to expand...
 
Upvote 0
All the columns in the Sample Data worksheet should showup in the New Layout until you get to those columns that house the date as a field name and the values for each record. When I copied over the data into the New Layout sheet, I really messed that up. I apologize. Otherwise, the restructuring looks good for those date fields and of course, adding the extra records for each line item.

The other issue is that the field names in row 1 do not show in the New layout.
Click to expand...

Anita Kay,

Please supply another workbook, with the raw data in worksheet Sample Data with the title row, and, just 5 more of the sample rows, and, worksheet New Layout (manually formatted/completed by you) with the results that you are now looking for.
 
Upvote 0
Upvote 0
Anita Kay,

Thanks for the new workbook/worksheets.

Here is a new macro for you to consider.

Code: 
Sub Reorg_SAP_Report_V3()
' hiker95, 01/30/2017, ME987801
Dim a As Variant, i As Long, c As Long, cc As Long, d As Long, lr As Long, lc As Long
Dim o As Variant, j As Long, n As Long
Application.ScreenUpdating = False
With Sheets("Sample Data")    '<-- you can change the sheet name here
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  ReDim o(1 To (lr + 1) * 12, 1 To 14)
End With
For i = 2 To UBound(a, 1) Step 1
  cc = 13
  For n = 1 To 12 Step 1
    j = j + 1
    d = 1
    For c = 1 To 12 Step 1
      o(j, d) = a(i, c)
      d = d + 1
    Next c
    o(j, 13) = a(1, cc)
    o(j, 14) = a(i, cc)
    cc = cc + 1
  Next n
Next i
With Sheets("New Layout")    '<-- you can change the sheet name here
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  If lr > 1 Then
    .Range("A2:N" & lr).ClearContents
  End If
  .Range("A2").Resize(UBound(o, 1), UBound(o, 2)) = o
  .Range("M2").Resize(UBound(o, 1)).NumberFormat = "mmm-yy"
  .Columns(1).Resize(, UBound(o, 2)).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Same instructions per my reply #8.

Then run the Reorg_SAP_Report_V3 macro.
 
Upvote 0
Thank you so much Hiker95,

I just met with my colleagues and it is working as we wished. Thank you so much for your help in solving this problem. We will build off of this to complete future assignments.

AnitaKay:)

hiker95 said:
Anita Kay,

Thanks for the new workbook/worksheets.

Here is a new macro for you to consider.

Code: 
Sub Reorg_SAP_Report_V3()
' hiker95, 01/30/2017, ME987801
Dim a As Variant, i As Long, c As Long, cc As Long, d As Long, lr As Long, lc As Long
Dim o As Variant, j As Long, n As Long
Application.ScreenUpdating = False
With Sheets("Sample Data")    '<-- you can change the sheet name here
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  ReDim o(1 To (lr + 1) * 12, 1 To 14)
End With
For i = 2 To UBound(a, 1) Step 1
  cc = 13
  For n = 1 To 12 Step 1
    j = j + 1
    d = 1
    For c = 1 To 12 Step 1
      o(j, d) = a(i, c)
      d = d + 1
    Next c
    o(j, 13) = a(1, cc)
    o(j, 14) = a(i, cc)
    cc = cc + 1
  Next n
Next i
With Sheets("New Layout")    '<-- you can change the sheet name here
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  If lr > 1 Then
    .Range("A2:N" & lr).ClearContents
  End If
  .Range("A2").Resize(UBound(o, 1), UBound(o, 2)) = o
  .Range("M2").Resize(UBound(o, 1)).NumberFormat = "mmm-yy"
  .Columns(1).Resize(, UBound(o, 2)).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Same instructions per my reply #8.

Then run the Reorg_SAP_Report_V3 macro.
Click to expand...
 
Upvote 0
Anita Kay,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA Loop to clear contents of fiscal year rows in specific month columns
Replies
6
Views
504
kevin9999
K

Forum statistics

Threads
1,215,219
Messages
6,123,683
Members
449,116
Latest member
HypnoFant

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