Posts by Maqbool

Hi

The problems is below code:

Range("C6:AD6").AutoFill Destination:=Range("C6:AD" & lastrow)

This will copy down any thing you have on row 6 - eg. if on H6 - if you dont have a formula - and assume it is blank - so your h7 will blank, if you have 1 on H6 your H7 will be 1

Regards

Maqbool

Hi

Try this:

Set Rng = ActiveSheet.Range("E11:E20")


With Rng
    For Each c In Rng
        curid = c.Value
        strAddress = strAddress & ";" & curid
    Next


End With


 strAddress = Mid(strAddress, 2, Len(strAddress))


 
With oMail
    .To = strAddress


End With

Regards

Maqbool

Hi

Sorry - I am not very clear about your requirement.

Do you mean - Copy 16 rows ( ie 20% of 80 data points) to each sheet (say mar 01 to March 05). So each sheet will have 16 rows of data.

Otherwise post expected result in each sheet (Mar 01, Mar 02 etc..) - Also, if you can input some meaningful data on the spreadsheet that will be more helpful to those who willing to help you.

Regards

Maqbool

YouTube solution.

The spread has lot of other info and the user needs to select respective fields and load the corresponding data.

Regards

Maqbool

Hi nilojan

Post a sample workbook with expected results.

Regards

Maqbool

Hi Alan and Ali

Thank you very much [INDENT]
[USER="29237"]ali[/USER] - That is very helpful. Exactly what I was looking for.[/INDENT]

Best Regards

Maqbool

Hi Alan

Thank you very much.
The attachment has two queries - One created with PQ and MSQ.
On MSQ sheet I can do the filtering according to my selection of cell B2. My question is how can i do the same on sheet Power Query?

Regards

Hi

How do we create a parameter query with Power Query? I know how to create it with Microsoft Query. My source data is within the same workbook.
Is there any demos or samples available with a parameter query or some know any resource to see step by step how to do this?

Any help will be highly appreciated.

Regards

Maqbool

Hi

Try the below:

Private Sub Worksheet_Change(ByVal Target As Range)


    If Target.Column <> 1 Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
    
    If Target.Value > 10 Then Target.Offset(0, 1).Value = "OK"


End Sub

Regards

Maqbool

Hi
The only way i can think of is: you must leave any one item visible. Count items in the pivot field. Then loop through each item and if there is a blank then set the visible property to True other wise the first item or last items make visible - So any one item will be visible on the pivot.

Regards

Maqbool

Hi

Welcome to the forum.

It is not possible to hide all data items in a pivot table. At lease one item should be visible on pivot. You are trying to hide every thing.

Regards

Maqbool

Hi -

You need to define names for the ranges to be used on the chart. The names are created using Index and Offset function. So, according your start and end point the range will be adjusted dynamically.
Then that defined ranges can be used as x and y axis on the chart. See the attached work - It has screen shots of each step and a small description of each step.

Regards

Maqbool

Hi

Welcome to the Forum.

See the attached workbook. Hope this helps.

Regards

Maqbool

Here it is;

As I said earlier - Please goto the pivot options and change the option to retain per field to "none". Then refresh your pivot. I have done this in attached work book. The other problem was - your path declaration was missing "/" in the path

Regards

Maqbool

Hi
Can you post the same workbook that gives the error? Or goto the pivot table options and from the data tab, number of items to retain per field change to "None". and try.

Regards

Maqbool

try this formula
[f]
IF(OR(SUMPRODUCT(--ISNUMBER(SEARCH(error,$A2:$P2))),SUMPRODUCT(--ISNUMBER(SEARCH(error,$R2:$AF2)))),"Human Error","")
[/f]

Regards

Maqbool

Hi

Can you post a sample workbook pls,

With an expected result will be more helpful.

Regards

Maqbool

Hi

Try the following:

Sub Test()
    Dim wsTarget As Worksheet
    Dim wsSource As Worksheet
    Dim rngTarget As Range
    Dim rngSource As Range
    Dim c As Range, SourceCell As Range
    
    Application.ScreenUpdating = False
    'On Error Resume Next
    Set wsTarget = Sheet1
    Set wsSource = Sheet2
    
    With wsTarget
        x = .Rows.Count
        Set rngTarget = .Range("A1", .Range("A" & x).End(xlUp))
        
    End With


    With wsSource
        Set rngSource = .Range("A1", .Range("A" & x).End(xlUp))
    End With
       
    With rngSource
        For Each c In rngTarget
            Set SourceCell = .Find(c.Value, , xlValues, xlWhole)
            
            If Not SourceCell Is Nothing Then
                SourceCell.Copy
                c.PasteSpecial xlPasteFormats
            Else
               MsgBox c.Value & " Not Found in source.", vbInformation, "Copy Format"
            End If
        Next
    End With
    
    Application.ScreenUpdating = True
End Sub

Regards

Maqbool

Hi

I am not sure why you are getting this error. Use the code on a module. You don't need to select anything to process the code. Either post back the error file or take a screens shot and post.

Why do you select the range?

Regards

Maqbool

It will filter out based on K1, K2, (on your pivot - that is the row label) etc.. and create a new workbook in the folder - "C:\D-Drive\SET 1" - change this part according to your needs.
On a pivot you won't able to hide all fields. If the row label is blank then it won't create any workbook. You may need tweak this part according to source data. I based on your sample spreadsheet.

Regards

Maqbool