+ Reply to Thread
Results 1 to 12 of 12

Copy and paste range of cells depending on another cell

  1. #1
    Registered User
    Join Date
    03-12-2019
    Location
    Bristol
    MS-Off Ver
    10
    Posts
    10

    Copy and paste range of cells depending on another cell

    Hi all,

    I'm not entirely sure if this is possible but....

    Is it possible to programme excel to copy a range of cells into another range based on the value of a cell i.e.

    If A1 = 1 copy cells G3:G13 into A3:A13
    If A1 = 2 copy cells K3:K13 into A3:A13

    and so on....

    Thanks,

    joe

  2. #2
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Copy and paste range of cells depending on another cell

    Into A3:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and drag it down into 13.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  3. #3
    Registered User
    Join Date
    03-12-2019
    Location
    Bristol
    MS-Off Ver
    10
    Posts
    10

    Re: Copy and paste range of cells depending on another cell

    Thanks for the response.

    i had thought about IF statements but the spreadsheet will actually have 10 variations rather than just 2 in the example.

    I didn't want to use a nested If statement because it will be too long and i will almost certainly make a mistake somewhere in it.

    Is there a way to do it with VBA?

    Thanks,

  4. #4
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Copy and paste range of cells depending on another cell

    What do you mean?
    ex. A1=3 L3:L13, A1=4 M3:M13 etc.?

  5. #5
    Registered User
    Join Date
    03-12-2019
    Location
    Bristol
    MS-Off Ver
    10
    Posts
    10

    Re: Copy and paste range of cells depending on another cell

    Yeah exactly that.

    Thanks.

  6. #6
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Copy and paste range of cells depending on another cell

    Question is (in my opinion):
    is it possible to catch some pattern between value from A1 and column number?
    A1=1 values from column G(7)
    A1=2 values from column K(11)
    do you want column O for A1=3 and S for A1=5?

  7. #7
    Registered User
    Join Date
    03-12-2019
    Location
    Bristol
    MS-Off Ver
    10
    Posts
    10

    Re: Copy and paste range of cells depending on another cell

    It will be column o for A1 = 3 and S for A1 = 5.

    The column pattern is uniform in regards to every time A1 increase by 1 the column to pick up is 4 across.

    Thanks,

  8. #8
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Copy and paste range of cells depending on another cell

    Quote Originally Posted by JT100392 View Post
    It will be column o for A1 = 3 and S for A1 = 5.
    The column pattern is uniform in regards to every time A1 increase by 1 the column to pick up is 4 across.
    Thanks,
    So, we both made mistake because in this pattern col. S is for A1=4 not 5.
    Anyway... put into A3:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and drag it down to row 13.
    Should works.

  9. #9
    Registered User
    Join Date
    03-12-2019
    Location
    Bristol
    MS-Off Ver
    10
    Posts
    10

    Re: Copy and paste range of cells depending on another cell

    Thanks for your help it works perfectly. Very neat solution!

    I have another slightly more awkward issue now though....

    I need to do a similar thing further down the spreadsheet....

    Where A34 = 1.1 copy cells G37:G47 to A37:A47
    Where A34 = 1.2 copy cells K37:K47 to A37:A47

    And so on for the following values of A34 = 1.1, 1.2, 2.1, 2.2, 2.3, 3.1, 4.1, 4.2, 5.1, 5.2, 5.3, 5.4, 5.5

    The columns to copy are as follow: G, K, O, S, W, AA, AE, AI, AM, AQ, AU, AY, BC (same spacing as previously).

    My excel ability stops at IF statements and the above combination would require an unbelievably long nested IF statement to work.

    Can you help?

    Thanks,

    joe

  10. #10
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Copy and paste range of cells depending on another cell

    Put into A37:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and drag it down into A47

  11. #11
    Registered User
    Join Date
    03-12-2019
    Location
    Bristol
    MS-Off Ver
    10
    Posts
    10

    Re: Copy and paste range of cells depending on another cell

    Works perfectly!

    Thank you very much!

  12. #12
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Copy and paste range of cells depending on another cell

    You welcome.
    Last edited by KOKOSEK; 03-15-2019 at 06:31 AM.

+ 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. Copy, paste, and clear contents (i.e move) a range depending on combobox selection
    By ANDLU89 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-29-2018, 03:43 AM
  2. Copy and paste specific cells depending on adjacent cells HELP NEEDED
    By clacheson1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-20-2017, 04:06 PM
  3. [SOLVED] Copy paste depending on cells property
    By ced0802 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-26-2016, 03:40 PM
  4. copy & paste to other sheets depending on dates specified in cells
    By ufopilot in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-02-2015, 05:43 AM
  5. Copy range and paste depending on the selected checkbox
    By mackypogi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-30-2014, 10:38 PM
  6. Copy one cell and paste into a range of cells in same row
    By Caileanbw in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-26-2013, 12:33 PM
  7. Change range of cells within VBA macro and copy and paste to fixed cell range
    By Mannyny in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-20-2012, 11:51 AM

Tags for this Thread

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