+ Reply to Thread
Results 1 to 6 of 6

copy a range of cells N times and paste in separate sheet and also change a specific colum

  1. #1
    Registered User
    Join Date
    09-16-2013
    Location
    hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    3

    copy a range of cells N times and paste in separate sheet and also change a specific colum

    Hi,

    I am new to Excel Macros, Please give me a solution for this problem.

    I am having 2 blocks of cells in sheet2 (acc_cap and power_cap).acc_cap contains 3 sub blocks(B2 to F6, B7 to F10, B11 to F14) and power_cap contains 2 sub blocks(B15 to F20, B21 to F23). I like to have my final output in sheet3 which will be controlled from sheet1.

    In sheet1 column A indicates block name, column B indicates offset value, column C indicates how many times to repeat the block.

    1) In 2nd row of sheet1 I mentioned power_cap(in A2 cell), 40(in B2 cell), 4 (in C2 cell),
    then the macro has to search for "power_cap" in column A of sheet2, if it finds then that power_cap block range( A15 to F23 in sheet2) has to be pasted in sheet3 for 4 times means it contains 8 sub blocks. In the column C of sheet3, the offset values of sub blocks of power_cap has to be 40, 44, 48, 4c, 50, 54, 58, 5c.

    2) In 3rd row of sheet1 I mentioned acc_cap(in A3 cell), 84(in B3 cell), 1 (in c3 cell),
    then the macro has to search for "acc_cap" in column A of sheet2, if it finds then that acc_cap block range( A2 to F14 in sheet2) has to be pasted in sheet3 for 1 time means it contains only 3 sub blocks. In the column C of sheet3, the offset values of sub blocks of acc_cap has to be 84, 88, 8c.



    Please provide me a solution
    Attached Files Attached Files

  2. #2
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: copy a range of cells N times and paste in separate sheet and also change a specific c

    Try the following code (it is a generic solution supporting multiple copy instructions and multiple block types/templates)

    Please Login or Register  to view this content.
    If you like my contribution click the star icon!

  3. #3
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: copy a range of cells N times and paste in separate sheet and also change a specific c

    @ OllieB,

    Baie mooi! Very nice piece of Coding, well done!

    Groete
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  4. #4
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: copy a range of cells N times and paste in separate sheet and also change a specific c

    Winon,

    Dank je wel!

    Receiving a compliment from a forum guru makes my day

    Rgds

  5. #5
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: copy a range of cells N times and paste in separate sheet and also change a specific c

    @ OllieB,

    You are welcome, it is well deserved!

    Regards

  6. #6
    Registered User
    Join Date
    09-16-2013
    Location
    hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: copy a range of cells N times and paste in separate sheet and also change a specific c

    Thank You OlieB

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Macro to copy a cell range and paste to a new sheet 'n' times where 'n' defined by formula
    By staminaboy in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-26-2013, 11:39 AM
  2. Copy and paste specific range to date specific range in alternate sheet
    By alanalmarza in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-21-2013, 08:29 AM
  3. [SOLVED] Macro to copy/paste dynamic range x times into same sheet
    By BigAl_Qld in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-19-2013, 03:48 AM
  4. [SOLVED] Copy specific range from one sheet and paste to next available row of different sheet
    By koklok in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-28-2012, 07:27 PM
  5. copy and paste colum K to colom R and skip filled cells colum R
    By jdrmer in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-13-2012, 10:30 AM

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