Posts by MIG285

    Re: VBA Help On Pivot Report


    Hi all


    Mangaer to figure it out.


    this is the solution for all that are looking for the help



    Hi All



    Please could you help me with a little bit of an issue that has come up on the below code.


    I seem to get the debug error come up when one of the fields are missing or can not be found, which is frustrating. on the PivotTable2.



    I run two pivot tables on the same data, the Pivotable1 works great and does exactly what I need it to do, how ever its the second Pivot table where I am have an issue with.


    What I would like to know is if there is a simple way of making all the data in the .PivotItems visible with in the range of the Pivot tanle report without haveing to specify that ("Name ") on the Pivotable2


    The reason why I ask as these names can change anytime and there might be new names added to the list that I already have accumalated.


    Please could you help me on this small issue, it would be very much appriciated.


    Regards
    MIG285


    Re: Looping code not working help please?


    Hi norie, thank you the code works great and did what you suggested on the merge and that fixed it as we'll know the code is doing exactly what I need it to do


    Thanks for your help on this


    MIG285

    Re: Looping code not working help please?


    Hi Norie


    That is where i am stuck, its the Loop bit of the code that does not go onto next blank cell in column B, how do i get around that to i insert a "do until last used Range" how do i write that code so that it carries on until the last used row in the sheet.


    Sorry made ment to put loop and not Looping in the quest



    MIG285

    Hi All


    I have prepaired this code to find blank cell in column "B" after a subtotal is done on the sheet. and merge cell and the lookup values on another tab.


    This works great but, only does the first blank cell in column B and need it to find all the blank cells on the sheet and import the lookup.


    not sure if looping will be good or if there is anything that i can change on this code to complete the task.



    Your help would be very much appriciated.


    MIG285


    Re: Copy to last row from active cell


    Hi LocalPeople


    Thanks for that, Replaced my code with the one you suggested picked up an error on the destination Stateing the following "Veriable not Defined" error


    Not Sure what is diffrent the way you set out the code is so much easier to use for all templates and make more sense now to me just this small issue that has come up.


    thanks for you help

    Hi All


    I have the following code written for some templates that I have, however I seem to have a problem with the very last line in the code.


    What I am trying to do is copy the selected cell in Row (2:2), down to the last used row of data, the issue that I have is that the last column veries on diffrent templates and cannot have a fixed column Range based on that, need to find out how to get around this issue so it works on all templates in the same way irrespective of what the last column number it is on.


    Thanks in advance


    Mig285



    Re: Saving Format in VBA code


    Hi Rory


    Did not like that , FileFormat:=56, but changed the way it saves from ".xls" it to the following and seems to have corrected the issues.


    Code
    SSF = FName & " " & "FORECAST" & " " & Format$(Date, "DD.MM.YY") & ".xlsx"


    Mig285

    Re: Saving Format in VBA code


    Hi rory


    I use 2007, but I send it out to about 40 suppliers and about 25% come back top the the cant open the format but if I save the file with "xlsx" the problem seemd to go away.


    Would I change the code to suit or is there a difrent way to save the file?


    Mig 285

    Hi All


    I use the below code on all my Macro's to save the reports that I run. The problem is that the format that it saves it in, nobody in my company can open it yet I can open it from my PC?


    Not sure if there is something I can do to correct the issue. It has to save in an Excel Format and on the VBA code I use ".xls" is there something else I could use or do to correct the code.


    Cheers


    MIG285


    Code
    Dim SSF As String, FName, FNAME2, FNAME3 As StringFName = RANGE("D4").ValueFNAME2 = RANGE("A7").Value2FNAME3 = RANGE("O23").ValueSSF = FName & " " & Format$(Date, "DD.MM.YY") & " " & FNAME2 & " " & "-" & " " & FNAME3 & ".xls"ActiveWorkbook.SaveAs Filename:=SSF


    Hi


    I have come up with a error on the below code it work great when it finds the errors, however if the report has no errors it stops the macro at the point and does not continue to next step.


    Please could you have a look and see if I am missing a interger or step to force the next step if the results are nothing


    Please help


    MIG285




    Code
    Dim rng As RANGE
    i = ActiveSheet.Cells(ActiveSheet.Cells.Rows.Count, "A").End(xlUp).Row
    rng = RANGE(RANGE("L5"), RANGE("L5" & i))
    rng.SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents
    Set rng = Nothing
    RANGE("L5").Select

    Re: Speeding Up VBA Code


    Hi WinteE


    That worked like a charm, thanks.


    How could I repeat this with out selecting each tab and duplicating the code to work on each ws


    Looking forward to your reply


    MIG285

    Hi All


    Below I have put this VBA code to aid me in sorting out a number of verious reports.


    The issue is that the ranges of data in the four reports are all diffrent, but the format of the report does not change and there are 4 tabs with the same data but for diffrent divisions.


    So to make sure that the largest range is covered I had to imput the max range into the VBA code.


    As for the print ing of each worksheet need it to print the data that is left behind once VBA has completed its steps


    I am sure that I can change this to VBA code to only select the used ranges in each tab and file.


    Please could you help me



    MIG285


    Hi All


    Have started doing this VBA code.


    I have tried using steps that work on other VBA codes how ever I have this error come up on the " With ActiveWorkbook.Sheets(myarray)" line


    Where did I go wrong or how do I correct the error.


    Please could you help me with the issue


    MIG285




    Re: Copy Data from Active Workbook to Diffrent Workbook


    Hi PIke


    Thank you for the help it ,the first example has worked very well.


    You Mention Setting Objects as ranges.


    How would you do this would this be with a "DIM" & "as", for this I would really need help with undersatnding how to set that up and would be interesting on how to set that up expecially when I am trying to sort out the data into seperate tabs in the Array.



    Currently I use this code to move something from main sheet to archived sheet once the "Completed" flag is activated. I am sur that buy using something like this instead of the autofilter option.


    As always wait on your sound advice.