Thanks Paul!!!
it works perfectly fine.
This is the code that I have so far. first time working with a pivot table
macro. the final result should like the screen shot below.
the problem is
1) how do I copy the data highlighted in blue and yellow to the right table
in green? I tried using the R1C1 function but it is not working.
2) I will also need the grand total In the bottom.
3) How do I highlight the pivot table to look like below?
4) Just in case, if I might need to do so. how do I remove the or lock
it(don't know the right words) so that it gives the data. user will not be
able to drag and drop.
I have pasted my whole code below that got me to creating the pivot table
and not the green table
Sub testing2()
Dim rangeTemp As Range
Dim sheet As Worksheet
Dim colLast As Long
Dim Rng As Range, rCell As Range
ChDir "C:\Users\doe\Desktop"
Workbooks.Open Filename:= _
"C:\Users\doe\Desktop\Utility.xlsx"
Sheets("Data").Select
Range("A1").Select
On Error Resume Next
mylastrow = Cells.Find("*", [a1], , , xlByRows, xlPrevious).Row
mylastcol = Cells.Find("*", [a1], , , xlByColumns, xlPrevious).Column
mylastcell = Cells(mylastrow, mylastcol).Address
Data_range = "A1:" & mylastcell
Range(Data_range).Select
Selection.Copy
ActiveWindow.Close
Set sheet = Sheets.Add
sheet.Name = "DATA"
Range("A1").Select
ActiveSheet.Paste
'------------------------------------------------------------------------------------------
'trim Spaces from left and Right
For Each cl In Selection
If Len(cl) > Len(WorksheetFunction.Trim(cl)) Then
cl.Value = WorksheetFunction.Trim(cl)
End If
Next cl
'----------------------------------------------------------------------------------------
'Selection.AutoFill Destination:=Range("O2:O" & Cells(Rows.Count,
"B").End(xlUp).Row)
'For first row. To change rows, alter the Cells number
'find last column. search column g and I. Add the value to the two last
left.
colLast = Cells(1, Columns.Count).End(xlToLeft).Column
Cells(1, colLast + 1) = "TYPE"
Cells(1, colLast + 2) = "PMG"
Cells(2, colLast).Select
Set Rng = Range("I2:I" & Cells(Rows.Count, "B").End(xlUp).Row)
For Each rCell In Rng.Cells
'--------------------------------------------------------
If rCell.Value = "FUEL2" Then
Cells(rCell.Row, colLast + 1).Value = "FUEL"
ElseIf rCell.Value = "ELEC" Then
Cells(rCell.Row, colLast + 1).Value = "UTILITY"
ElseIf rCell.Value = "GAS" Then
Cells(rCell.Row, colLast + 1).Value = "UTILITY"
Else
Cells(rCell.Row, colLast + 1).Value = "VALUE NOT GAS,ELEC, OR
FUEL2"
Cells(rCell.Row, colLast + 1).Interior.ColorIndex = 45
Cells(rCell.Row, colLast + 1).Font.Bold = True
End If
'--------------------------------------------------------
If Left(Cells(rCell.Row, "G").Value, 4) = "6183" Then
Cells(rCell.Row, colLast + 2).Value = "AEP"
Else
Cells(rCell.Row, colLast + 2).Value = "ERP"
End If
'--------------------------------------------------------
Next rCell
'----------------------------------------------------------------------------------------------------------------
'Pivot Table
Sheets("DATA").Select
Sheets.Add
ActiveSheet.Name = "Summary"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase,
SourceData:= _
"DATA!R1C1:R534C15",
Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Summary!R3C1", TableName:="PivotTable1",
DefaultVersion _
:=xlPivotTableVersion14
Sheets("Summary").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("PGM")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("TYPE")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("BCOC")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("I_INVC_PAY_AMT"), "Sum of
I_INVC_PAY_AMT", xlSum
With ActiveSheet.PivotTables("PivotTable1")
.InGridDropZones = True
.RowAxisLayout xlTabularRow
End With
'=======================================================
'NEED A BETTER APPROACH
'Creating a mini table
'Headers
Cells(5, 6).Value = "PGM"
Cells(5, 7).Value = "TYPE"
Cells(5, 8).Value = "PIPELINE"
Cells(5, 9).Value = "PENDING ENC"
Cells(5, 10).Value = "BUDGET CODE"
'----------------------------------------
'AEP formatting
Cells(6, 6).Value = "AEP"
Cells(6, 7).Value = "Fuel"
Cells(7, 7).Value = "Util"
Cells(6, 10).FormulaR1C1 = _
"=CONCATENATE((LEFT(R[0]C[-7],4)),""-"",RIGHT(R[0]C[-7],3))"
Cells(7, 10).FormulaR1C1 = _
"=CONCATENATE((LEFT(R[-1]C[-7],4)),""-"",RIGHT(R[-1]C[-7],3))"
'Display message
MsgBox "The macro has finished running.", vbInformation
End Sub
On Thursday, February 25, 2016 at 7:56:03 AM UTC-5, Paul Schreiner wrote:
> Yes, this is much easier to understand.
> Did I miss where you mentioned your VBA code before?
> I TOTALLY didn't understand that you were working with VBA.
> I thought you were using Excel functions.
>
> So, now that we're on the same page, let's take a look at your code:
>
> When the macro first runs, colLast is set to 14 (column "N").
>
> You then set the Rng variable to the Range of data in column "I".
> You then loop through this Range (Rng) and process the values there.
> Now, the problem with:
> If rCell.Value = "ELEC" Then rCell.Offset(0, colLast + 1).Value = "utility"
>
> rCell.Offset means to move a specific distance from the cell rCell.
>
> rCell is column "I"
> You used (0, colLast +1)
> that means move 0 rows (use current row) but move 14+1 columns to the
> right from rCell!
> which puts it in column "AB" instead of column "O".
>
> You'd be better off using:
> Cells(rCell.Row, colLast + 1).Value = "utility"
>
> Now, if you have more tests than these, I'd suggest going with an
> If/ElseIf/Endif construct:
>
> For Each rCell In Rng.Cells
> If rCell.Value = "FUEL2" Then
> Cells(rCell.Row, colLast + 1).Value = "Fuel"
> ElseIf rCell.Value = "ELEC" Then
> Cells(rCell.Row, colLast + 1).Value = "utility"
> ElseIf rCell.Value = "GAS" Then
> Cells(rCell.Row, colLast + 1).Value = "TEST"
> End If
> Next rCell
>
> Because, if rCell.Value is "FUEL2", then there's no reason to test if it
> is "ELEC" or "GAS"!
>
> Now, for your last question, you want to test:
> If Left(rCell.Value, 4) = "6183" Then
>
> What column are you looking in?
> Currently rCell is column "I", and this column does not have numeric
> values.
> If you're looking in column "G", then I'd suggest changing your macro to:
> -------------------------
> Dim colLast As Long
> Dim Rng As Range, rCell As Range
>
> 'For first row. To change rows, alter the Cells number
> colLast = Cells(1, Columns.Count).End(xlToLeft).Column
>
> 'Now for some data:
> Cells(1, colLast + 1) = "TYPE"
> Cells(1, colLast + 2) = "PMG"
>
> Cells(2, colLast).Select
> Set Rng = Range("I2:I" & Cells(Rows.Count, "B").End(xlUp).Row)
>
> For Each rCell In Rng.Cells
> '--------------------------------------------------------
> If rCell.Value = "FUEL2" Then
> Cells(rCell.Row, colLast + 1).Value = "Fuel"
> ElseIf rCell.Value = "ELEC" Then
> Cells(rCell.Row, colLast + 1).Value = "utility"
> ElseIf rCell.Value = "GAS" Then
> Cells(rCell.Row, colLast + 1).Value = "TEST"
> End If
> '--------------------------------------------------------
> If Left(Cells(rCell.Row, "G").Value, 4) = "6183" Then
> Cells(rCell.Row, colLast + 2).Value = "AEP"
> Else
> Cells(rCell.Row, colLast + 2).Value = "ERP"
> End If
> '--------------------------------------------------------
> Next rCell
>
> *Paul*
> -----------------------------------------
>
>
>
>
>
>
>
> *“Do all the good you can,By all the means you can,In all the ways you
> can,In all the places you can,At all the times you can,To all the people
> you can,As long as ever you can.” - John Wesley*
> -----------------------------------------
>
>
> On Wednesday, February 24, 2016 3:50 PM, Maju <betm...@gmail.com
> <javascript:>> wrote:
>
>
>
> I am a bit confused with your question.
>
> This is what I am able to come up with so far.
>
> maybe you might be able to understand what I am trying to do from the
> code.
> So I was able to come up with this but it is not placing the value in same
> come as the word type
> Dim colLast As Long
> Dim Rng As Range, rCell As Range
> 'For first row. To change rows, alter the Cells number
> colLast = Cells(1, Columns.Count).End(xlToLeft).Column
>
> 'Now for some data:
> Cells(1, colLast + 1) = "TYPE"
> Cells(2, colLast).Select
> Set Rng = Range("I2:I" & Cells(Rows.Count, "B").End(xlUp).Row)
> For Each rCell In Rng.Cells
> If rCell.Value = "FUEL2" Then rCell.Offset(0, colLast + 1).Value = "Fuel"
> If rCell.Value = "ELEC" Then rCell.Offset(0, colLast + 1).Value =
> "utility"
> If rCell.Value = "GAS" Then rCell.Offset(0, colLast + 1).Value =
> "TEST"
>
> Next rCell
> How do I also incorporate this to include it in next column after TYPE.
> Cells(1, colLast + 1) = "PMG"
> If Left(rCell.Value, 4) = "6183" Then
> rCell.Offset(0, 1).Value = "AEP"
> Else
> rCell.Offset(0, 1).Value = "ERP"
>
> On Wednesday, February 24, 2016 at 1:35:02 PM UTC-5, Paul Schreiner wrote:
>
> I'm sorry, I STILL do not understand.
>
> How does the value in column "J" determine the value to display in column
> "O"?
>
> as for "empty" rows.
> How can a row be empty if it has a value or something in it?
> If it has something in it, it is not empty.
> If it is empty, it doesn't have anything in it!?
>
> I suspect I'm missing something in your explanation.
>
> *Paul*
> ------------------------------ -----------
>
>
>
>
>
>
>
> *“Do all the good you can,By all the means you can,In all the ways you
> can,In all the places you can,At all the times you can,To all the people
> you can,As long as ever you can.” - John
> Wesley*------------------------------
> -----------
>
>
> On Wednesday, February 24, 2016 11:19 AM, Maju <betm...@gmail.com> wrote:
>
>
>
> I am using 2010 excel.
>
> I have attached a file. The value in column J will determine what will go
> into o. So I don't have the value for column o stored any where.
>
> what I mean by delete row. Is to clean up the rows after the last row with
> data. Sometimes some rows might be empty but have in value or something
> that will make it thing that there is value.
>
> thanks!
>
> It would be helpful to provide a sample file.
> But:
> What version of Excel are you using?
>
> Where do you get the values for column "O"?
> Your example has entering "utility" when something other than "fuel" is in
> column J.
> Are there other values?
>
> And, what so you mean by "Delete any extra line after the last row with
> data."?
> Excel 2010 allows 1,048,576 rows.
> You cannot "delete" any of these rows.
> You can delete the CONTENTS, but the rows are still there.
> However, you can HIDE any or all of these rows.
>
> Can you please elaborate?
>
> *Paul*
> ------------------------------ -----------
>
>
>
>
>
>
>
> *“Do all the good you can,By all the means you can,In all the ways you
> can,In all the places you can,At all the times you can,To all the people
> you can,As long as ever you can.” - John
> Wesley*------------------------------
> -----------
>
>
> On Tuesday, February 23, 2016 8:48 AM, Maju <betm...@gmail.com> wrote:
>
>
>
> Hello All,
>
> Please I need help with the below macro.
> I have a worksheet with lots of data.
>
> 1. I want to go through each cell in column J.
> To find data and put the answer in column O.
> 2. Then left and Right trim spaces
> 3. Delete any extra line after the last row with data.
> 4. Save file as expense with a date attached to it.
>
>
> The below should be the final result.
>
>
> Column J Column
> O
> Fuel
> Fuel
> Gas
> Utility
> Electric
> Utility
> Fuel
> Fuel
>
> Your help will be greatly appreciated
>
> thanks!!
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros...@ googlegroups.com.
> To post to this group, send email to excel-...@googlegroups.com.
>
> Visit this group at https://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/d/optout.
>
>
>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros...@ googlegroups.com.
> To post to this group, send email to excel-...@googlegroups.com.
> Visit this group at https://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/d/optout.
>
>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros...@googlegroups.com <javascript:>.
> To post to this group, send email to excel-...@googlegroups.com
> <javascript:>.
> Visit this group at https://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/d/optout.
>
>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
