Posts by msn_manju

  • Data aggregation from different workbooks

    Hi All,


    I have a workbook which has sales data in one worksheet and inventory data in one worksheet. There around 50 workbooks I get each week one for each customer. The column headers are same for sales and inventory in all the sheets. All the 50 files are in the same folder.


    Is there a way to put the sales data of all customers in one worksheet and inventory in one worksheet. Currently I am doing it manually.




    Thanks,
    Manju

  • Inputbox options

    Hi All,


    I have an inputbox in my code. By default the inputbox is giving the options "OK" and "Cancel". Is there a way where I can have only "OK" option in my inputbox.


    Thanks
    Manju

  • Refresh pivot macro

    Hi,


    There are around fifteen pivot tables in my workbook. Is there a macro which would refresh all pivots in a workbook. Also is it possible to make the macro dynamic so that the macro would work even if the pivot table names are changed.



    Thanks

  • Data validation

    Hi,


    I have data validation in cells A1:A50 which allows to select values from a list. There are about 50 items in the list that can be selected. Is it possible to have the user select an item only once i.e., if an attempt is made to select an item thats already selected it show an error message.


    Thanks for the help

  • Removing non-english characters

    Re: Removing non-english characters


    Hi Dave,


    Thanks for your reply. If there is a more dynamic method it would be helpful, because Ž and Ÿ are only some of the examples and there are many characters like that and each day there might be new characters like that.


    Thanks again

  • Importing data

    Hi,


    I have the data in text format which I have to import it to Excel. The problem is that it has around 120k rows of data. The method I am doing it is by splitting the text file into two halves and importing it into two worksheets.


    Is there a way of importing the data without splitting the file (its okay if the output is in two worksheets).


    Thanks in advance

  • Using variable in vlookup

    Re: Using variable in vlookup


    I have two xls file. File one has different sheet tabs, i want to run a macro to put formula of vlookup in column B from the another xls file which has same # of tab as of First file. So i want a variable in vlookup i.e "sheet_name".


    So discription of vlookup is
    Selection.FormulaR1C1 = _
    "=IF(B2<>"""",IF(ISERROR(VLOOKUP(B2,[abc.xls]" & sheet_name & " '!$A:$C',2,FALSE)),0,VLOOKUP(B2," & sheet_name & " '!$A:$C',2,FALSE)))"


    thank u

  • Using variable in vlookup

    Re: Using variable in vlookup


    Code
    sheet_name = ActiveSheet.Name
Range("B:B").Select
Selection.FormulaR1C1 = _
        "=IF(RC[-1]<>"""",IF(ISERROR(VLOOKUP(RC[-1],[THQ.xls]" & sheet_name & " '!$A:$C',2,FALSE)),0,VLOOKUP(RC[-1]," & sheet_name & " '!$A:$C',2,FALSE)))"

  • Using variable in vlookup

    HI


    I am trying to pass a variable in vlookup. I am able to pass variable in same sheet, however if i want variable from different sheet its not working code as below Plz help.


    Code
    Selection.FormulaR1C1 = _
        "=VLOOKUP(RC[-1],[ABC.xls]" & sheet_name & "!R1C1:R207C3,2,FALSE)"


    Thank u