+ Reply to Thread
Results 1 to 13 of 13

VBA to reference sheet range and populate with spacing between rows

  1. #1
    Registered User
    Join Date
    03-13-2014
    Location
    Melbourne Australia
    MS-Off Ver
    Excel 2010
    Posts
    22

    VBA to reference sheet range and populate with spacing between rows

    Hello,

    I am trying to use VBA to reference a cell range and display on a different sheet with 4 spaces between the displayed values starting at a specific cell.

    See the example clip below to see what I am trying to do. Please help?

    Capture.JPG
    Attached Images Attached Images
    Last edited by mrjackg; 03-10-2017 at 08:23 PM. Reason: Added detail of what I am trying to do

  2. #2
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: VBA to reference sheet range and populate with spacing between rows

    Here is one way to do it...

    Please Login or Register  to view this content.
    Regards,
    Stephen

    If you feel someone has helped you please thank them and click on the star on their post to add reputation.
    Please ensure that you use code tags where relevant, and mark solved threads as solved.
    Most of all please be respectful and understanding of others.

  3. #3
    Registered User
    Join Date
    03-13-2014
    Location
    Melbourne Australia
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: VBA to reference sheet range and populate with spacing between rows

    Thats great thanks. Is there a way to start the destination at a specific cell say C4?

    Thanks again!
    Jack

  4. #4
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: VBA to reference sheet range and populate with spacing between rows

    Sure, where it has i=1 change that to i=4

  5. #5
    Registered User
    Join Date
    03-13-2014
    Location
    Melbourne Australia
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: VBA to reference sheet range and populate with spacing between rows

    Right too easy. Thanks again

  6. #6
    Registered User
    Join Date
    03-13-2014
    Location
    Melbourne Australia
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: VBA to reference sheet range and populate with spacing between rows

    I dont suppose you could help me with one more thing? If I wanted to filter the source range to only select cells that Dont have some specific text in it, could you advice on the vba to be used here?

    See example I dont want the cells with the text showing _WorkPlan at the end the the name:
    Capture.JPG

  7. #7
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: VBA to reference sheet range and populate with spacing between rows

    Sure...

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    03-13-2014
    Location
    Melbourne Australia
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: VBA to reference sheet range and populate with spacing between rows

    Awesome so simple. Thanks again

  9. #9
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: VBA to reference sheet range and populate with spacing between rows

    This is another solution using arrays
    Please Login or Register  to view this content.
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  10. #10
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: VBA to reference sheet range and populate with spacing between rows

    Another option for your first post.
    Change the cell reference, here K7, to wherever you want to start your paste

    Please Login or Register  to view this content.

  11. #11
    Valued Forum Contributor
    Join Date
    11-28-2015
    Location
    indo
    MS-Off Ver
    2016 64 bitt
    Posts
    1,241

    Re: VBA to reference sheet range and populate with spacing between rows

    YaserKhalil : this is very good..copy paste use ing array but is posibel also copy format use array

  12. #12
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: VBA to reference sheet range and populate with spacing between rows

    Thanks a lot ..
    ASAIK using arrays doesn't keep format and if you need to use formats you would deal directly with cells

  13. #13
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: VBA to reference sheet range and populate with spacing between rows

    Another possibility for your Post #6
    No looping. Size of Range will determine speed I think.

    Please Login or Register  to view this content.

+ 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] Cell Reference with Irregular Spacing
    By ej4443 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-19-2016, 12:54 PM
  2. VBA - Search Sheet 1 for text, reference corresponding cell, populate Sheet 2?
    By rdr06001 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-18-2016, 01:30 PM
  3. [SOLVED] VBA to add up Total # or rows to populate Destination sheet and insert More Rows if needed
    By bdouglas1011 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-29-2014, 08:44 PM
  4. [SOLVED] Need to populate a table with aggregated reference data from other sheet
    By elnarho in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-25-2014, 10:21 AM
  5. [SOLVED] Locked formula changes reference when rows added to reference sheet in same workbook
    By macrorookie in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-19-2014, 04:08 PM
  6. Populate summary sheet (insert & index rows from info in columns on another sheet)
    By DerbysGal in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-03-2011, 01:54 PM
  7. Rows Spacing, Column Spacing
    By Pukka83 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-26-2008, 12:05 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