+ Reply to Thread
Results 1 to 7 of 7

Macro to copy header as well as data where blank cells in Col J

  1. #1
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2016
    Posts
    1,265

    Macro to copy header as well as data where blank cells in Col J

    I have a sheet called "Data import"


    I have tried to write code to copy the headers as well as data from this sheet where there are blank cells in Col G and paste on sheet "items not reported on"


    I have manually extracted the data on sheet "items not reported on"


    Please Login or Register  to view this content.


    It would be appreciated if someone could assist me

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,548

    Re: Macro to copy header as well as data where blank cells in Col J

    See next code
    To be launched from sheet "Data Import"
    Please Login or Register  to view this content.
    - Battle without fear gives no glory - Just try

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    9,682

    Re: Macro to copy header as well as data where blank cells in Col J

    How about
    Please Login or Register  to view this content.

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,939

    Re: Macro to copy header as well as data where blank cells in Col J

    This question really should be a FAQ. I see it time and time again and looping just isn't the best way of doing this sort of thing.

    Whenever you find yourself creating looping code stop and think is this really necessary? Most of the time it isn't. Loops are inherently slow when they have to jump back and forth between VBA and the Excel application - there's a big time overhead at each pass through the loop.

    The fastest, most efficient and elegant way for this sort of stuff is to use an Autofilter to filter the rows you're interested in.

    In your case

    Please Login or Register  to view this content.
    This uses just one Copy / Paste instruction rather than many if you're looping through many cells.

    But why do even this? Why not just use an Autofilter on Sheet1 if you just want to see incomplete records. Or perhaps use a Pivot table with some Slicers.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2016
    Posts
    1,265

    Re: Macro to copy header as well as data where blank cells in Col J

    Hi Guys


    thanks for the help.

    Richard, thanks for your valuable explanation and advise

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    9,682

    Re: Macro to copy header as well as data where blank cells in Col J

    You're welcome & thanks for the feedback

  7. #7
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,548

    Re: Macro to copy header as well as data where blank cells in Col J

    Enjoy Excel and thank you for the reputation

+ 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. Need a Formula to copy data to different cells based on a column header
    By jpiquette in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-20-2019, 12:22 PM
  2. [SOLVED] Macro - Data copy from one sheet to other according to the header name
    By Chocobo in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-08-2018, 11:42 PM
  3. Need to copy header row in group to all rows below header; stop if blank and repeate
    By jmcaleer10 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-18-2013, 11:09 AM
  4. Replies: 4
    Last Post: 06-04-2013, 11:01 AM
  5. Macro to copy data into blank cells
    By santanuKD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-15-2010, 04:54 AM
  6. Replies: 0
    Last Post: 02-08-2010, 08:40 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