Copying specific Cell values fron multiple sheets to specified cell of another sheet

  • Hi Friends !!!
    Can anyone help me out in solving this problem where I have been paused? I have Work Book containing several sheets i.e. 80+ or more sheets. In that, there are 66 sheets records same pattern of data from a user form. In each of the 66 Sheets, a calculation row is set for the entered data for a number of columns(Auto Sum type), This calculated data from the 66 sheets is required to be duplicated in the summary sheet in a specific column. In the summary sheet, each row is set for each sheet starting from Sheet 1 to sheet 66 (already named). An example may help you in understanding better.
    (a) Let the Work Book be MyProject1.
    (b) Same pattern of Data is sent from a User Form ( say UserForm1) to 66 Sheets.
    (c) Row No. 64 in each sheet (of the 66 Sheets) calculates some value in time and numerical value.
    (d) I have a summary sheet in which I specified Columns to get the data from the 66 sheets.
    (e) Column ‘N’ will store the data of Z64 cells of the 66 sheets. Similarly, Column ‘O’ will have AB64 and Column ‘P’ will have the data of AD64 cells of the 66 sheets.
    It may be noted that, the sheets are not serially named or numbered. Secondly the work book also
    contains other sheets having formulas for different occasions. My need is that can a VBA code be placed
    for the same so that, the Summary Sheet can be updated as and when the data is entered through the user form simultaneously while updating the 66 work sheets. These 66sheets are defined for a heads used in a combo-box and against these 66 heads. The Userform holds the command button to transfer the user input data to the Summary sheet.

    I am very new to excel and do not know how to proceed. Also I do not want to copy and paste link from all 66 pages which is a tedious job. Request help from all to have a code in shorter length to cater for the need.

    Thanking you in advance.


  • Re: Copying specific Cell values fron multiple sheets to specified cell of another s

    Hi nm766,
    Nice to meet you.
    Write something like..

    Regards, junho

  • Re: Copying specific Cell values fron multiple sheets to specified cell of another s

    Thanks a lot Junho, for your lightning response.
    I think this will work with a little modification. But can you throw some light on the followings:

    (a) The cells (Z64, AB64 and AD64 from each sheet of the 66 sheets) I want to copy to the summary sheet, are holding the data that is the calculated values in the respective sheets. Will it change while getting transferred to the summary sheet?

    (b) Secondly, what I need is to display the cell values ofthe 66 sheets same to same in the summary sheet in their designated cells. What I mean to say is will it transfer the formula along with the cell values to the summary sheet?

    (c) The summary sheet starts recording the data from Row No. 2 onwards. The First row contains the Headings that is manually done. So data to be recorded in N2 to N67, O2 to O67 and P2 to P67 for the Z64, AB64 and AD 64 cells of the 66 sheets.

    Do you think any change to be made in the code you provided?

    Awaiting your response.

    Thanks a lot again



Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!