• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

macro is not generating second report

narsing rao

narsing rao

Member
Hi Team,

I have created macro to generate report in my organisation it contains to reports from one data those are 1. Pivot report 2. today's calls report

the process i do is i pull the data and run macro from this data it has to create one pivot table and another today report wich contains all calls raised for today .
Now the problem is its generating pivot table but not creating Today report just creating headings
below is the code for your reference

Code: 
Sub smileyReport2()
'
' smileyReport2 Macro
' Testing2
'
' Keyboard Shortcut: Ctrl+h
'
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("Q1"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.Range("$A$1:$R$12713").AutoFilter Field:=17, Operator:= _
        xlFilterValues, Criteria2:=Array(2, "2/9/2017")
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Selection.Copy
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste
    Columns("K:K").EntireColumn.AutoFit
    Columns("L:L").EntireColumn.AutoFit
    Columns("N:N").EntireColumn.AutoFit
    Columns("Q:Q").EntireColumn.AutoFit
    Columns("R:R").EntireColumn.AutoFit
    Sheets("Sheet1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R1048576C18", Version:=6).CreatePivotTable TableDestination:= _
        "Sheet3!R3C1", TableName:="PivotTable1", DefaultVersion:=6
    Sheets("Sheet3").Select
    Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("RequestID"), "Count of RequestID", xlCount
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Feedback ")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("SubStatus")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").AutoGroup
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Years").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Quarters").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("PivotTable1").PivotFields("SubStatus").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("SubStatus").CurrentPage = _
        "Closed by Requestor"
    Range("A7").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Aug").Position = 1
    Range("A8").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Sep").Position = 4
    Range("A7").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Sep").Position = 3
    Range("A9").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Oct").Position = 5
    Range("A8").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Oct").Position = 4
    Range("A10").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Nov").Position = 6
    Range("A9").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Nov").Position = 5
    Range("A11").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Dec").Position = 7
    Range("A10").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Dec").Position = 6
    Sheets("Sheet3").Select
    Sheets("Sheet3").Name = "Pivot"
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "Today"
    Range("C3").Select
End Sub

please healp in sorting out this problem

Regards,
Narsing Rao
 
narsing rao said:
Hi Team,

I have created macro to generate report in my organisation it contains to reports from one data those are 1. Pivot report 2. today's calls report

the process i do is i pull the data and run macro from this data it has to create one pivot table and another today report wich contains all calls raised for today .
Now the problem is its generating pivot table but not creating Today report just creating headings
below is the code for your reference

Code: 
Sub smileyReport2()
'
' smileyReport2 Macro
' Testing2
'
' Keyboard Shortcut: Ctrl+h
'
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("Q1"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.Range("$A$1:$R$12713").AutoFilter Field:=17, Operator:= _
        xlFilterValues, Criteria2:=Array(2, "2/9/2017")
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Selection.Copy
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste
    Columns("K:K").EntireColumn.AutoFit
    Columns("L:L").EntireColumn.AutoFit
    Columns("N:N").EntireColumn.AutoFit
    Columns("Q:Q").EntireColumn.AutoFit
    Columns("R:R").EntireColumn.AutoFit
    Sheets("Sheet1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R1048576C18", Version:=6).CreatePivotTable TableDestination:= _
        "Sheet3!R3C1", TableName:="PivotTable1", DefaultVersion:=6
    Sheets("Sheet3").Select
    Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("RequestID"), "Count of RequestID", xlCount
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Feedback ")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("SubStatus")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").AutoGroup
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Years").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Quarters").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("PivotTable1").PivotFields("SubStatus").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("SubStatus").CurrentPage = _
        "Closed by Requestor"
    Range("A7").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Aug").Position = 1
    Range("A8").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Sep").Position = 4
    Range("A7").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Sep").Position = 3
    Range("A9").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Oct").Position = 5
    Range("A8").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Oct").Position = 4
    Range("A10").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Nov").Position = 6
    Range("A9").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Nov").Position = 5
    Range("A11").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Dec").Position = 7
    Range("A10").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Dec").Position = 6
    Sheets("Sheet3").Select
    Sheets("Sheet3").Name = "Pivot"
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "Today"
    Range("C3").Select
End Sub

please healp in sorting out this problem

Regards,
Narsing Rao
Click to expand...
Hi,

Is it possible to upload a sample excel file?
It is easier to give a targeted answer that way.
 
Hi,
Thanks for the response below the pic of the data which i download as i cant upload the data.
View attachment 38287

if you can see the q column i have applied filters from filter first i will select newest to oldest then from filters i will select today's calls i.e 2/9/2017 only , this keeps on changing as i have to select 2/10/2019 for tomorrow.. i have doubt if macro is failing in incriminating the date.. after selecting the today's date

upload_2017-2-9_15-27-34.png
upload_2017-2-9_15-27-34.png
upload_2017-2-9_16-54-16.pngupload_2017-2-9_16-54-16.png
i will copy those calls in todays sheet as shown in above ... now the problem is macro is creating the pivot table but not the Today sheet ...its only coping column heading but not the data (todays calls ) ..the entire sheet is showing blank
upload_2017-2-9_15-27-34.png

please help
 
Last edited:
Hi,

The following will add both "Today" and "Pivot" sheets, and will take care of the sorting, filtering and copying to Today's sheet. Remember to use "Pivot" as the sheet name in the rest of the code, instead of "sheet3", and to delete the "Sheets("Sheet3").Name = "Pivot"" as I have already added that in the beginning of following code:
Code: 
Sub Test()
  
    Sheets.Add After:=Sheets("Sheet1")
    ActiveSheet.Name = "Today"
    Sheets.Add After:=Sheets("Today")
    ActiveSheet.Name = "Pivot"
  
    With Sheets("Sheet1")
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range("Q2"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    End With
  
    With Sheets("Sheet1").Sort
        .SetRange Sheets("Sheet1").Range("A2:R" & Sheets("Sheet1").Cells(Rows.Count, "R").End(xlUp).Row)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
  
    Sheets("Sheet1").Columns("A:R").AutoFilter Field:=17, Operator:= _
            xlFilterValues, Criteria2:=Array(2, "02-09-2017")
  
    Sheets("Sheet1").UsedRange.Copy Sheets("Today").Cells(1, 1)
    Sheets("Today").Columns("A:R").EntireColumn.AutoFit
  
    Sheets("Today").Activate
  
End Sub

Since the Pivot Table part is working, add that part after the provided code (before "End Sub") and you should be good to go.

Let me know if you need anything else.
 
Hi,

Thanks for the quick reply ..but i am not able to understand in the code where i have to add your code...i am sorry to ask you this equation but i am not an expert in the programming.

Thanks in advance
 
narsing rao said:
Hi,

Thanks for the quick reply ..but i am not able to understand in the code where i have to add your code...i am sorry to ask you this equation but i am not an expert in the programming.

Thanks in advance
Click to expand...
Hi,

It should look something like this (untested, but should work):
Code: 
Sub Test()
   
    Sheets.Add After:=Sheets("Sheet1")
    ActiveSheet.Name = "Today"
    Sheets.Add After:=Sheets("Today")
    ActiveSheet.Name = "Pivot"
   
    With Sheets("Sheet1")
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range("Q2"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    End With
   
    With Sheets("Sheet1").Sort
        .SetRange Sheets("Sheet1").Range("A2:R" & Sheets("Sheet1").Cells(Rows.Count, "R").End(xlUp).Row)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   
    Sheets("Sheet1").Columns("A:R").AutoFilter Field:=17, Operator:= _
            xlFilterValues, Criteria2:=Array(2, "02-09-2017")
   
    Sheets("Sheet1").UsedRange.Copy Sheets("Today").Cells(1, 1)
    Sheets("Today").Columns("A:R").EntireColumn.AutoFit
   
   
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R1048576C18", Version:=6).CreatePivotTable TableDestination:= _
        "Pivot!R3C1", TableName:="PivotTable1", DefaultVersion:=6
   
    Sheets("Pivot").Select
    Cells(3, 1).Select
   
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("RequestID"), "Count of RequestID", xlCount
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Feedback ")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("SubStatus")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").AutoGroup
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Years").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Quarters").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("PivotTable1").PivotFields("SubStatus").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("SubStatus").CurrentPage = _
        "Closed by Requestor"
    Range("A7").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Aug").Position = 1
    Range("A8").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Sep").Position = 4
    Range("A7").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Sep").Position = 3
    Range("A9").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Oct").Position = 5
    Range("A8").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Oct").Position = 4
    Range("A10").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Nov").Position = 6
    Range("A9").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Nov").Position = 5
    Range("A11").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Dec").Position = 7
    Range("A10").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Modified Date").PivotItems( _
        "Dec").Position = 6
       
End Sub
If it stops at any point, hit debug and let me know where it stops.

Remember that, as it is, it will always filter for "02-09-2017"... if you want it to be dynamic, let's say use the today's date as criterion for the filter, you will need to change the code to use a variable instead.

Cheers
 
Hi Pcosta,

i think it seems to be working ...i have one doubt does it work for tomorrows date ...that is 2/10/2017 and incriminating for future dates like 2/10..2/11..2/12
 
narsing rao said:
Hi Pcosta,

i think it seems to be working ...i have one doubt does it work for tomorrows date ...that is 2/10/2017 and incriminating for future dates like 2/10..2/11..2/12
Click to expand...
It will not work...
At the moment you have:
Code: 
Sheets("Sheet1").Columns("A:R").AutoFilter Field:=17, Operator:= _
            xlFilterValues, Criteria2:=Array(2, "02-09-2017")

As you can see, the date is being provided as "02-09-2017"... you will need a variable here, something like:
Code: 
Sheets("Sheet1").Columns("A:R").AutoFilter Field:=17, Operator:= _
            xlFilterValues, Criteria2:=Array(2, Format(Date, "yyyy-mm-dd"))

Try it out (replace those lines of code with the latter) and let me know if it works.

Cheers
 
Hi Pcosta,

Today i pulled the data and used the macros updating the code as you said, it worked and i can generate the report perfectly with Todays calls populating ...thanks for your help it really helped me a lot...i will test this code few more days and roll out i my team,in future i am going to create few more macro to automate many reports in my team...hope you will help me in the save way...in order to contact you for any help shall port the errors directly to u or any other source to contact you ..please let me know..

Thanks once again for you valuable time and guidance..will be in touch with u :-)
 
Hi,

First of all, you are welcome ;), glad I could help!

If you have any further questions please update this thread... I will get a notification and will respond as soon as I can.

For something else not related to this topic (i.e. future projects or questions) please create a new thread and if you want to make sure you get my input, message me the link of the thread (new conversation).
 
Last edited:
Hi Pcosta,
I have one requirement..

I am trying to automate pulling the data from web site below are the step i am folowing
1.open excel sheet
2. select macros and recording
3. open data select from web
4. in that i will enter my web page from where i have to pull the data upto here i am able to do
next in my web page there are diffrent section where i have to select waht data need to pull
i will select that data and select ok to pull the data . after some time data will appear in that web site i have opetin to download the data in excel format . but as per the macro instructions there is arrow mark at the left top corner which will select the data after that we need to export bu in my case its not selecting the data instead its selecting the web page.

upload_2017-2-10_17-24-34.png
how to make the selection from rows to columns

Regards,
Narsing Rao
 
Hi Picasto,

Its intranet site not accessible from internet, as per the help documents i red its will select the rows and columns when clicking the arrow mark but in my case its selecting the web page....i need to hide web site address as i dont know if i can reveal the site address out the organisation .....
upload_2017-2-10_18-30-40.png

as you can see in the above picture instead of selecting the rows and column its selecting the eniter web page ..u can see the blue line border .

hope you got some idea seeing this picture.

Thanks in advance..
 
Hi Picasto,

now i am able to select the report area but still its not pulling the data , i am getting error.

"This web query returned no data. To change the query, click OK, click arrow on the name box in the formula bar, click the name of the external data range for the Web query, right-click the selection, and then click Edit Query".

please let me know if any have solution to this.
 
narsing rao said:
Hi Picasto,

now i am able to select the report area but still its not pulling the data , i am getting error.

"This web query returned no data. To change the query, click OK, click arrow on the name box in the formula bar, click the name of the external data range for the Web query, right-click the selection, and then click Edit Query".

please let me know if any have solution to this.
Click to expand...
Hi,

I've been following the other thread but unfortunately I'm afraid I can't be of much help in this case.
Sorry :(
 
You must log in or register to reply here.
Back
Top