+ Reply to Thread
Results 1 to 17 of 17

Copy Range of Formulas to a Variable Range of Cells

  1. #1
    Registered User
    Join Date
    02-08-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    10

    Copy Range of Formulas to a Variable Range of Cells

    Hi everyone,

    I have difficulties trying to writing some VBA codes. As what I have attached, I am trying to copy a range of formulas, and paste them into another range of cells.

    For example, when excel detects column A with "Apple", I want to copy formulas from M2:Z2 and copy them to M24:M34. The thing is that the range of M24:M34 is more of variable and always changing, it could be M14:M64 the next day. How should I amend the codes to paste the formulas from M2:Z2 to the M columns between "Apple" and "Pear". Sorry if this is confusing.. but please let me know if there is any uncertainty. Thanks

    Sub copyFormula()

    myrow = 21
    Lastrow = Range("A65000").End(xlUp).Row
    For i = 21 To Lastrow
    Range("A" & myrow).Select

    If ActiveCell.Value = "Apple" Then

    Range("M2:Z2").Select
    Selection.Copy
    Range("M24:M34").Select
    ActiveSheet.Paste

    End If
    myrow = myrow + 1
    Next I
    End sub
    Attached Files Attached Files

  2. #2
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Copy Range of Formulas to a Variable Range of Cells

    Hi xace

    I can write Code that'll work on your Sample File but it'll be based on the Contents of Column A. I'm fairly confident that your Actual Data does not look like your Sample Data where Apple is in each Cell under Apple, Pear is in each Cell under Pear, etc.

    What does your actual Data look like?

    Row\Col
    A
    23
    Apple
    24
    Apple1
    25
    Apple2
    26
    Apple3
    27
    Apple4
    28
    Apple5
    29
    Apple6
    30
    Apple7
    31
    Apple8
    32
    Apple9
    33
    Apple10
    34
    Apple11
    35
    Pear
    36
    Pear1
    37
    Pear2
    38
    Pear3
    39
    Pear4
    40
    Pear5
    41
    Pear6
    42
    Pear7
    43
    Pear8
    44
    Pear9
    45
    Pear10
    46
    Pear11
    47
    Pear12
    48
    Pear13
    49
    Pear14
    50
    Pear15
    51
    Pear16
    52
    Pear17
    53
    Pear18
    54
    Orange
    55
    Orange1
    56
    Orange2
    57
    Orange3
    58
    Orange4
    59
    Orange5
    60
    Orange6
    61
    Orange7
    62
    Orange8
    63
    Orange9
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Registered User
    Join Date
    02-08-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    10
    Hi jaslake, appreciate the response.. Apple, pear and orange are just headers. Actual data are security types like bonds,c equities etc. So the cells under bonds would be the identifiers such as the ISIN or sedol.

    I'm just wondering if it's possible to set the range to paste between the different headers.

  4. #4
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Copy Range of Formulas to a Variable Range of Cells

    Hi xace

    Yes, it's possible.
    I'm just wondering if it's possible to set the range to paste between the different headers.
    What are the ACTUAL Header Titles? Are they ALWAYS present in the Worksheet? Are they ALWAYS in the same sequence in the Worksheet?

  5. #5
    Registered User
    Join Date
    02-08-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Copy Range of Formulas to a Variable Range of Cells

    Hi jaslake,

    The actual header titles are Bond, CFD,Equity, Fund certificate, Future, GDR/ADR, Option and so on.

    The tricky part is that they are not always in the same sequence in the worksheet. The only constant is that it will always appear under column A, after line 20. For example, there are days where there might not be equities in the report, so it will just be Bond, CFD, Fund certificate and so on etc.
    Last edited by xace; 09-02-2015 at 10:36 PM.

  6. #6
    Registered User
    Join Date
    02-08-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    10
    Bump for help

  7. #7
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Copy Range of Formulas to a Variable Range of Cells

    Hi xace

    Nothing further comes to mind based on your Sample Data. I'd suggest you post a Sample File with your Actual Data with it's Actual Structure (desensitized) so we can see what we're dealing with.

  8. #8
    Registered User
    Join Date
    02-08-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Copy Range of Formulas to a Variable Range of Cells

    before.xlsmafter.xlsm

    Attached a before and after excel. Hopefully it gets clearer?

  9. #9
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Copy Range of Formulas to a Variable Range of Cells

    Hi xace

    I may be able to work with this. Just looking for ways...looking for "hooks".

    A small hook:
    Does Bond ALWAYS have "XX" as the leading two Characters?
    Does Equity ALWAYS have "B" as the leading Character?
    Does Fund Certificate ALWAYS have "XQ" as the leading two Characters?
    Does Options ALWAYS have "FX" as the leading two Characters?

    A bigger hook (might be a solution):
    Is Column B for the Headers (Bond, Equity, Fund Certificate and Options) ALWAYS empty?

    Perhaps another hook:
    Are Headers (Bond, Equity, Fund Certificate and Options) ALWAYS Bold? Are any other Cells in Column A in Bold?

    Most important...are these the ONLY possible Headers we have?
    • Bond
    • Equity
    • Fund Certificate
    • Options
    Last edited by jaslake; 09-03-2015 at 11:07 PM.

  10. #10
    Registered User
    Join Date
    02-08-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Copy Range of Formulas to a Variable Range of Cells

    Hi jaslake, the bigger hook hits! Only column "A" of the header is filled. Column "B" onwards have nothing.

  11. #11
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Copy Range of Formulas to a Variable Range of Cells

    Hi xace

    It's a hook...I'll look at it tomorrow...after midnight here....

  12. #12
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Copy Range of Formulas to a Variable Range of Cells

    Hi xace

    Went to bed...had a thought.

    Under each Header Row you have Data Rows. Is Column B of each of these Data Rows ALWAYS populated?
    Last edited by jaslake; 09-04-2015 at 01:06 AM.

  13. #13
    Registered User
    Join Date
    09-04-2015
    Location
    India
    MS-Off Ver
    Karnataka
    Posts
    1

    Re: Copy Range of Formulas to a Variable Range of Cells

    Hi All,

    I have a workbook that includes 1 Sheet which is named as TT & CT, used to view the time tracking information. (Excel file name is TT & CT Final)
    I need the data of E9:K9 from the above sheet i.e "TT & CT" to A2:G2 to new excel workbook.
    The cell that will be copied is fixed but the place where it will be pasted in new workbook will need to come by order wise. Below is the example how it should be pasted to new excel workbook.
    A2:G2
    A3:G3
    A4:G4
    A5:G5.

    Please help me out to get macro code for the same. As i am not very much good in macros.. hoping t get as simple and easy way to get it done,

    Thanks in Advance..!!

  14. #14
    Registered User
    Join Date
    02-08-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Copy Range of Formulas to a Variable Range of Cells

    thanks jaslake.

    Under each Header Row you have Data Rows. Is Column B of each of these Data Rows ALWAYS populated? - yes

    Just to inform you as well, there are actually more headers such as Right, Index bond, Covered warrant etc, but I believe these can be manually added easily into the coding template you'll be writing?
    Last edited by xace; 09-04-2015 at 06:29 AM.

  15. #15
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Copy Range of Formulas to a Variable Range of Cells

    Hi xace

    This Code in the attached looks for Blank Cells in Column B and fills Columns M through Z with the Formulas in M2 to Z2. Looking at your after.xlsm Workbook, this is obviously NOT what you're looking for. But I have no idea what your looking for as you've not explained except for this from your Initial Post.
    How should I amend the codes to paste the formulas from M2:Z2 to the M columns between "Apple" and "Pear"
    So, explain how one determines which Formulas to paste where and when.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  16. #16
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Copy Range of Formulas to a Variable Range of Cells

    Building off of jaslake's good work, this uses the .SpecialCells(xlCellTypeConstants).Areas properties to define each section and copy the formulas en masse.

    Edit the Select Case code block to define the range of formulas to copy for a given Header.

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  17. #17
    Registered User
    Join Date
    02-08-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Copy Range of Formulas to a Variable Range of Cells

    Appreciate the help jaslake and AlphaFrog.

    AlphaFrog's codes are good. Will be doing some testing on my end. Thanks for bearing with my lousy explanation

+ 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 range of cells, AND their formulas?
    By horsefish01 in forum Excel General
    Replies: 2
    Last Post: 05-28-2015, 06:49 PM
  2. Copy only cells that contain values from a range cells that contain formulas to next row
    By gjwilson1216 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-07-2015, 11:12 AM
  3. Copy range of cells using variable criteria and named ranges
    By Cpetersoc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-12-2014, 01:07 PM
  4. [SOLVED] Copy Variable to a Range of Cells
    By Metrazal in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-26-2014, 10:52 AM
  5. [SOLVED] Copy range from multiple sheets, into a master sheet and moving over by variable col range
    By g1eagle in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-23-2012, 01:36 PM
  6. [SOLVED] Copy/Paste Values from Variable Range to Corresponding Variable Range
    By Cpetersoc in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-14-2012, 08:20 AM
  7. [SOLVED] Copy only Visible Cells of a Variable Range...
    By Damian Carrillo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-20-2005, 10:05 PM

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