+ Reply to Thread
Results 1 to 14 of 14

running a macro to copy multiple cells from a row by selecting a cell

  1. #1
    Registered User
    Join Date
    02-28-2007
    Posts
    55

    running a macro to copy multiple cells from a row by selecting a cell

    Can anyone help...

    I want to be able to select a cell, for example A10, this would then run a macro that would copy and paste several cells of information along that row and paste it on another worksheet. However, if i then choose a15, a3, a79 in turn (using the control key), it would run the macro using those rows in turn.

    i'm looking forward to hearing if and particuliar how this could be achieved.

    Thanks

    Healthwatch

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: running a macro to copy multiple cells from a row by selecting a cell

    Healthwatch, it would help somewhat if you could outline what you're copying and where exactly you're intending to paste/replicate it.

  3. #3
    Registered User
    Join Date
    02-28-2007
    Posts
    55

    Re: running a macro to copy multiple cells from a row by selecting a cell

    basically, i will be copying a name from cells in 'column B' and pasting it onto a standard form held on another worksheet. I then want to copy information (numbers) from alternate cells starting from 'column E' from the same row. Again this would be pasted onto the standard form ready for printing, following which the information would be deleted ready for use by next name selected.
    Hope this makes sense,
    healthwatch

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: running a macro to copy multiple cells from a row by selecting a cell

    Yes it makes sense but it's still too vague - we don't want to spend time coding something with "our best guess ranges" only to have to recode the routine at a later point to reflect your real setup -- you seemingly have the print form setup and the source data ready to be copied so if you can please be specific ... detail all cells that need to be copied & detail clearly where the data is to be replicated... better yet post a small sample file reflecting your layout with dummy data (remove anything confidential).

    I know I sound like a bore but it's either 1 or 2 posts now and then a quick resolution or lots of to-ing and fro-ing adapting code...

  5. #5
    Registered User
    Join Date
    02-28-2007
    Posts
    55

    Re: running a macro to copy multiple cells from a row by selecting a cell

    hi all,

    i've attached a demo file so hopefully this will make it clearer, it has a macro to demonstate what i'd like it to do, but i need to det it to do all rows...

    Basically what i would like to do is either click on the name (stored in column B) or click on something in 'column A' which would run a macro to do the following..

    First, to copy the name of the person to sheet4 B6. then to copy info in column 'C' (from the same row as name) into the formula stored in sheet4 I7.

    I then need to copy information from alternate cells starting from column 'D' same row and i was going to paste this information (transposed) onto sheet4 B60 so that i could then filter this list to remove blanks before pasting the information into the appropriate columns on sheet4 i.e a27 and c27.

    Most of the stuff above i can do, what i need help with is the following...

    Is it possible to assign a macro to run directly to a cell or can you, for instance, click on cell A19 (Sheet 1) and click on a button to run the macro to do the above for that row or select multiple rows and run the macro for each in turn. As at present i would have to code a macro for each line used and for additional lines added, which i'd prefer not to do as it will make the file very large

    I hope this makes sense,

    Healthwatch
    Attached Files Attached Files

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: running a macro to copy multiple cells from a row by selecting a cell

    I have to confess that I'm still a little confused given there's no formula in I7 (you mention there is one)... I'm also unsure as to what goes into Col B on Sheet4...

    Anyway here is some code that runs as a "Double Click" event... ie if you double click on a cell containing a name it will update Sheet4 with the data from Sheet1 -- copies Name to B6 (S4), WTE to I7 (S4), Dates to A27 onwards (S4) & values in D etc to C27 onwards (S4)

    To activate the code - right click on Sheet1 and select View Code... paste the below into the resulting window.... to invoke - double click on a name in Column B on Sheet1 ... Sheet4 will update. See REM Comments for commentary on what the code is doing.

    Please Login or Register  to view this content.
    I hope that points you in the right direction.

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: running a macro to copy multiple cells from a row by selecting a cell

    On an aside I was just looking at the formulae in column DI

    This:

    Please Login or Register  to view this content.
    Can be shortened using a SUMIF approach such that it becomes:

    Please Login or Register  to view this content.
    This works given the dates in header row are only actually in the even columns (D,F etc...) -- the cells are merged but E,G etc contain nothing - the value is always held in the first cell of the merged area... dates are integers and always exceed 0 hence the above SUMIF works.


    You can extend further such that you can apply across your entire range and have blanks returned when the value in B is not a valid staff member, eg:

    Please Login or Register  to view this content.

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: running a macro to copy multiple cells from a row by selecting a cell

    One last point... having looked at your VBA I believe I7 on Sheet4 is merely meant to represent 37.5 * ratio (sheet1 C) in which case change the following line:

    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.
    No need to insert a formula - just let the VBA do the calc and write back the result (rounded to 2 decimals)

  9. #9
    Registered User
    Join Date
    02-28-2007
    Posts
    55

    Re: running a macro to copy multiple cells from a row by selecting a cell

    Thanks for that, it works great...
    Could you explain to me how the VBA gets the information from sheet 1 i.e name and WTE as i would like it to get some more info off the sheet but not sure how it does it The new info would be located in columns DH:DM

    Thanks in advance,
    Healthwatch

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: running a macro to copy multiple cells from a row by selecting a cell

    Quote Originally Posted by healthwatch
    Could you explain to me how the VBA gets the information from sheet 1 i.e name and WTE
    The below is taken from the code provided

    Please Login or Register  to view this content.
    So to clarify further:

    Target:
    this is the cell on which you double clicked (ie Bx where x is row) ...
    (ie a Range)

    wsForm:
    this is declared in the code as a Sheet Object and has been assigned as Sheet4

    Offset:
    Will offset from a given range by specified rows / columns, eg:

    Please Login or Register  to view this content.
    So going back to the actual code:

    Please Login or Register  to view this content.
    Sets Cell B6 on Sheet4 to be the value of the actual Target cell
    (ie the contents of the cell you double clicked - name)

    The below

    Please Login or Register  to view this content.
    Sets Cell I7 on Sheet4 to be 37.5 * the value of the cell Offset from the Target by 1 Column (ie Cx where x = row)

  11. #11
    Registered User
    Join Date
    02-28-2007
    Posts
    55

    Re: running a macro to copy multiple cells from a row by selecting a cell

    hi,

    could you tell me how the 'Iterate week' section works...

    I get that the

    For IngCol = 4 (means starts from column 4) however the rest of the line i'm confused as i was trying to gather info from every cell instead of alternate ones - but not getting anywhere.

    Thanks again,

    Healthwatch

  12. #12
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: running a macro to copy multiple cells from a row by selecting a cell

    Please Login or Register  to view this content.
    Correct, starting position is Column 4 (D) ... the final column is technically determined by the last cell in row 11 containing a value... this:

    Please Login or Register  to view this content.
    goes to the last physical column on row 11 and then goes left to find the first non-blank (ie the last non-blank on the row) ... and establishes the number of that non-blank column.

    The "Step 2" tells the For Next Loop to iterate lngCol by 2 each time (as opposed to the traditional 1) ... ie step from 4 to 6, from 6 to 8 etc... change 2 to 1 if you want to iterate each column rather than every other.
    Last edited by DonkeyOte; 02-22-2009 at 01:28 PM.

  13. #13
    Registered User
    Join Date
    02-28-2007
    Posts
    55

    Re: running a macro to copy multiple cells from a row by selecting a cell

    I've tried changing the step 2 to (step 1) however this does not appear to work. So i tried to change the Offset(, 2) to Offset(, 1) this does not work either.

    Can you tell me what i'm doing wrong?

    What i want to do is, Each date is associated with 2 cells (as date is 2 cells merged) what i want to do is, if the cells are not blank and numeric add together (via the sum function, i think) and then paste this figure into sheet 4 c27 ( previously i only needed the alternate cells copying).

    Hope this makes sense,
    Thank you for the help

    Healthwatch

  14. #14
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: running a macro to copy multiple cells from a row by selecting a cell

    If I've interpreted you correctly you need to leave Step as 2 but use:

    Please Login or Register  to view this content.
    instead of

    Please Login or Register  to view this content.
    The Offset you've adjusted relates to the cell to which you are pasting the value not the source values... this is determined after the = operator... the Step should remain 2 so as to step the date headers correctly.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1