+ Reply to Thread
Results 1 to 13 of 13

VBA to insert value into column rows before copying to new sheet

  1. 09-08-2016, 08:26 AM #1
    Sintek
    Sintek is offline
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,606

    VBA to insert value into column rows before copying to new sheet

    Hi All

    I have the below code which copies rows that meet a criteria to a new sheet. I need to add a line of code which places the word "COD" into Column F of each row that meets the criteria before copying over.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 09-08-2016, 10:07 AM #2
    Mumps1
    Mumps1 is offline
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,908

    Re: VBA to insert value into column rows before copying to new sheet

    Try: 
    Please Login or Register  to view this content.
    Last edited by Mumps1; 09-08-2016 at 10:10 AM.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.
    Reply With QuoteRegister To Reply
  3. 09-08-2016, 10:34 AM #3
    Sintek
    Sintek is offline
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,606

    Re: VBA to insert value into column rows before copying to new sheet

    Mumps1

    You are a star..Thanks so much. Very simplified code.
    Reply With QuoteRegister To Reply
  4. 09-08-2016, 10:38 AM #4
    Mumps1
    Mumps1 is offline
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,908

    Re: VBA to insert value into column rows before copying to new sheet

    My pleasure.
    Reply With QuoteRegister To Reply
  5. 09-08-2016, 10:57 AM #5
    Sintek
    Sintek is offline
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,606

    Re: VBA to insert value into column rows before copying to new sheet

    Mumps
    A quick question. I notice that it populates the second available row in my table and not the first. Any reason for this
    Reply With QuoteRegister To Reply
  6. 09-08-2016, 11:23 AM #6
    Mumps1
    Mumps1 is offline
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,908

    Re: VBA to insert value into column rows before copying to new sheet

    Try the attached file.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 09-08-2016, 11:29 AM #7
    Sintek
    Sintek is offline
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,606

    Re: VBA to insert value into column rows before copying to new sheet

    Hi Mumps1

    Thanks. It does work out if it is a Range......Not a Table though. I need Hist to be a table as I draw sales figures from it.
    Reply With QuoteRegister To Reply
  8. 09-08-2016, 11:37 AM #8
    Mumps1
    Mumps1 is offline
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,908

    Re: VBA to insert value into column rows before copying to new sheet

    Try this one.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 09-08-2016, 11:54 AM #9
    Sintek
    Sintek is offline
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,606

    Re: VBA to insert value into column rows before copying to new sheet

    Hi Mumps1

    Thanks for your time on this..... It works same as the first one.... It is fine if Sheets("Hist") is a range.......
    My Sheets("Hist") has to be a Table
    Reply With QuoteRegister To Reply
  10. 09-08-2016, 12:05 PM #10
    Mumps1
    Mumps1 is offline
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,908

    Re: VBA to insert value into column rows before copying to new sheet

    In the second file I posted (Post# 8), "Hist" should be a table.
    Reply With QuoteRegister To Reply
  11. 09-08-2016, 12:13 PM #11
    Sintek
    Sintek is offline
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,606

    Re: VBA to insert value into column rows before copying to new sheet

    Nope....It is a Range
    Reply With QuoteRegister To Reply
  12. 09-08-2016, 01:00 PM #12
    Mumps1
    Mumps1 is offline
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,908

    Re: VBA to insert value into column rows before copying to new sheet

    Try: 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  13. 09-08-2016, 01:21 PM #13
    Sintek
    Sintek is offline
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,606

    Re: VBA to insert value into column rows before copying to new sheet

    Hey there Mumps1

    Causes all kinds of trouble LOL.... Think I'm just going to leave as is..... First row can stay empty.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Linking 1 column in 2 sheets (if I insert rows in sheet #1 they appear in sheet #2)
    By isafloor in forum Excel General
    Replies: 4
    Last Post: 02-03-2015, 07:54 PM
  2. Code to insert rows in next sheet in order of Column B
    By kosherboy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-24-2014, 10:24 AM
  3. how to insert two rows of text in column b1:c1 from sheet 2 into sheet 1 automatically?
    By pejoi in forum Excel General
    Replies: 1
    Last Post: 10-22-2012, 03:26 AM
  4. Copying Rows to to another Sheet based on the column data?
    By BhuvanRam in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2012, 12:56 AM
  5. Excel 2007 : formulae for copying data from column in one sheet into rows in another sheet
    By mike.nkhata in forum Excel General
    Replies: 1
    Last Post: 06-17-2011, 04:18 PM
  6. Macro for copying rows from master sheet to other worksheet based on column content
    By ashncg in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-02-2011, 06:42 PM
  7. [SOLVED] Same formula in each cell of column; insert rows w/o copying down
    By tgdavis in forum Excel General
    Replies: 3
    Last Post: 09-14-2005, 05: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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1