none
how to pull data from other sheet RRS feed

  • Question

  • Hi,

    Any function or vba to pull data from particular cell of other worksheet to same cell, if that worksheet is named as just previous month's name than the active worksheet. Say ctive sheet name is julyMPR then pull data from juneMPR and only from A1,D1,A10,D10 of juneMPR to A1,D1,A10,D10 of julyMPR.

    regards

    Wednesday, July 20, 2016 2:28 PM

Answers

  • Hi drsantoshsinghrathore,

    on the base of your requirement you can try to refer the example below.

    it is just an example demo. so you need to modify it to get desired result.

    Sub demo()
    Dim premon, curmon As String
    premon = MonthName(Month(Date) - 1) & "MPR"
    curmon = MonthName(Month(Date)) & "MPR"
    Sheets(premon).Range("A1:D10").Copy Destination:=Sheets(curmon).Range("A1:D10")
    
    End Sub

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 25, 2016 6:25 AM
    Moderator

All replies

  • Google is your friend. You don't say if this is something you need to do one time, i.e. you have a july sheet and a june sheet and you need to connect these two sheets, or if this is a more general problem, i.e. you need to connect SheetMonthNow to SheetMonthPrevious (*).

    Start here and see if this helps, then provide more information if you have a specific question:

    http://officetricks.com/different-methods-to-import-data-from-external-excel-file/

    (*) I reiterate the concept I posted in another of your threads: Create a clean data set, then build a reporting system. Why are you creating a file for June, another for July, and probably one for August? Put all the data in ONE file and then figure out how to report on it. I deal with this specific issue a lot and separate files turn into a nightmare especially if users can modify the file structure because then you can't know if some specific data element lives in cell A3 in every sheet or if some user moved A3 to DD87 either on purpose or by mistake.

    Structured and controlled data source = good

    Wednesday, July 20, 2016 2:56 PM
  • Hi,

    Actually main data will be in hard copies and large one. I need to prepare monthly report. A simplified example is like this,

    and

    I need previous months data to add present month.

    Regards

    Wednesday, July 20, 2016 5:16 PM
  • Short answer: I believe by putting data into the kind of human friendly structure you display, you're setting yourself up for long term problems.

    Longer answer: Without a good understanding of what data you're collecting and what your actual reporting needs are, I can only recommend you think about rebuilding the system to store raw data in a structure of rows and columns (as opposed to one table per month) and build out to create reports from there. Your question related to pulling data from one Excel file into another so I have to assume that your 'June' data is actually in one Excel file and July in another. So for instance, in your July table, you want the formula:

    =Sum(H9 + B10) to be =Sum(<JuneFile:H9> + B10)

    The link I sent you includes directions for doing just that.

    Wednesday, July 20, 2016 5:34 PM
  • Also, if twas me, I'd put the data into a relational database like Access, normalize your structure, and use that to create your reports. Access allows a lot of reporting flexibility.

    Newer versions of Excel allow relational structures so you may have success doing that also. I have not played with those features so I can't offer much assistance but it's probably worth your time to investigate.

    Be a lazy developer! Make the data work for you.

    Wednesday, July 20, 2016 5:38 PM
  • Hi drsantoshsinghrathore,

    You had mentioned that you want VBA or Formula. Well if you want VBA this is the correct forum and if you want Formula the “Excel it pro discussion forum” is the correct forum.

    In the original post you had mentioned that ,” pull data from juneMPR and only from A1,D1,A10,D10 of juneMPR to A1,D1,A10,D10 of julyMPR.

    Where as In your last reply you had mentioned that ,” Actually main data will be in hard copies and large one”.

    So first here I want to ask you that you want to display all the data or you want to just pull the data that you had mentioned in the original post?

    So that on the base of your requirement we can try to give you suggestion.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, July 21, 2016 2:19 AM
    Moderator
  • Hi,

    Thank you,

    >>In the original post you had mentioned that ,” pull data from juneMPR and only from A1,D1,A10,D10 of juneMPR to A1,D1,A10,D10 of julyMPR.

    Yes I want this. True.

    >>Where as In your last reply you had mentioned that ,” Actually main data will be in hard copies and large one”.

    Yes, right. Main data in on registers.

    Regards

    Friday, July 22, 2016 5:38 PM
  • Hi drsantoshsinghrathore,

    on the base of your requirement you can try to refer the example below.

    it is just an example demo. so you need to modify it to get desired result.

    Sub demo()
    Dim premon, curmon As String
    premon = MonthName(Month(Date) - 1) & "MPR"
    curmon = MonthName(Month(Date)) & "MPR"
    Sheets(premon).Range("A1:D10").Copy Destination:=Sheets(curmon).Range("A1:D10")
    
    End Sub

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 25, 2016 6:25 AM
    Moderator
  • Thank you all,

    It seems formulla better option in cell.

    regards

    Monday, July 25, 2016 6:42 AM
  • Hi drsantoshsinghrathore,

    you had mentioned,"It seems formulla better option in cell."

    did you solved your issue using formula or you want solution using formula.

    if you got the solution using formula and the issue is solved then please update the status of thread.

    if the problem exist and you want solution using formula let me know so that I move this thread to Excel it pro discussions forum from where you can get a formula to solve your issue.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 25, 2016 8:00 AM
    Moderator
  • Hi,

    Actually I know use of a formula. It is a long work. I thought there may be shorter way to do this. But no reply for precisely doing this. Adding april data to may and may data to june and so on. That's why I posted last time. If any thing as proper splution comes, welcome.

    regards

    Monday, July 25, 2016 8:19 AM
  • Hi drsantoshsinghrathore,

    did you try to run the example I mentioned above.

    it will precisely calculate the current month and last month by itself.

    and you not need to do anything.

    you had mentioned,"Adding april data to may and may data to june and so on"

    yes it can do that , if till now you did not test it then I would recommend you to test it.

    I think that it is very simple way to achieve  this instead of using Formula.

    just you need to pass the range of source and destination sheets and its done.

    if you have any issue regarding that example then let us know so that we can try to give you further suggestions.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 25, 2016 8:29 AM
    Moderator
  • Hi Deepak Panchal10 ,

    Thank you.

    This error

    Please help,

    regards
    Wednesday, July 27, 2016 1:01 AM
  • Hi drsantoshsinghrathore,

    First Goto the "Tools" Tab in VBE. then select "References". then check for MISSING references and try to clear it.

    other work around is to add VBA before Date Function. like VBA.Date.

    please visit the link below to get more information regarding this issue.

    You receive a "Can't find project or library" error message when you run a VBA project

    "Can't Find Project or Library" When You Run a Macro

    let us know it worked for you or not.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 27, 2016 7:57 AM
    Moderator
  • Hi Deepak Panchal10,

    Thank you for your excellent code.

    Same OS and same version on PC and laptop. It works fine on laptop but above error on PC. Though I didn't change anything in library. Can you tell me where this change will be made in that library. Please keep in mind I am a layman in excel. I never learned it in classes but myself only.

    Regards



    Wednesday, July 27, 2016 8:50 AM
  • Hi drsantoshsinghrathore,

    please check my previous suggestion in which I suggests you 2 different things.

    first in the references you need to find for Missing References. if you find it then clear it.

    if it not work then try to implement suggestion 2.

    if you have any trouble to implement the suggestion then let me know so that I can again try to give you some detailed suggestion.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, July 28, 2016 7:04 AM
    Moderator