+ Reply to Thread
Results 1 to 10 of 10

VBA to copy sells

  1. 03-25-2013, 01:20 PM #1
    Skorpionkz is offline
    Registered User
    Join Date
    03-05-2013
    Location
    Dublin
    MS-Off Ver
    Excel 2010
    Posts
    18

    VBA to copy sells

    Hi guys,

    I trying to create script:

    Please Login or Register  to view this content.
    Obviously this not working, and I don't know where I am wrong.

    This should check if range I8:I31 have "Ok" in it and if yes to copy value from columns A and B to new added row in row A and B.

    So if it found 3*Ok it should insert 3 blank rows and paste value there.

    Now instead of 3 it adding 72 rows:/ (my range 24*3)

    Attached is the sample file.

    Thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-25-2013, 01:44 PM #2
    Mayda89 is offline
    Forum Contributor Mayda89's Avatar
    Join Date
    09-13-2012
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    192

    Re: VBA to copy sells

    This should work.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 03-25-2013, 01:51 PM #3
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to copy sells

    I do not exactly where you want to post the copied values in sheet conditions, but assuming it is in next empty column A,

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 03-25-2013, 03:35 PM #4
    Skorpionkz is offline
    Registered User
    Join Date
    03-05-2013
    Location
    Dublin
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: VBA to copy sells

    @ Mayda89
    this does almost same thing like mine it paste correct value, but instead of insert only 3 lines with value from the rows where was "ok" it added each of them 24 times

    @ AB33

    It work almost how I wanted but...
    1. it should add first empty row, starting in row 27 with count of how many time it found "Ok"
    2. and then paste value how your script did in those inserted rows

    I cannot understand how this script work can you add comment about this line:
    .Cells(iRow, 1).Resize(, 2).Copy ms.Range("A" & Rows.Count).End(xlUp).Offset(1)

    In case I will need to change something

    thank you
    Last edited by Skorpionkz; 03-25-2013 at 03:39 PM.
    Reply With QuoteRegister To Reply
  5. 03-25-2013, 03:45 PM #5
    Mayda89 is offline
    Forum Contributor Mayda89's Avatar
    Join Date
    09-13-2012
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    192

    Re: VBA to copy sells

    You are right.

    Now it should work.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 03-25-2013, 03:59 PM #6
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to copy sells 

    Please Login or Register  to view this content.
    When it finds "ok", instead of copying cells 1 and 2, you combine them by resize. That is Cells(iRow, 1). is cell1 and go 2 to the right which takes you to cell2. It is similar to offset, but resize starts at 1 while offset at 0.

    Please Login or Register  to view this content.
    This is simply copying in to next empty column A, so if A1 is not empty and the next empty would be A2.
    Reply With QuoteRegister To Reply
  7. 03-25-2013, 04:30 PM #7
    Skorpionkz is offline
    Registered User
    Join Date
    03-05-2013
    Location
    Dublin
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: VBA to copy sells

    Quote Originally Posted by AB33 View Post 
    Please Login or Register  to view this content.
    This is simply copying in to next empty column A, so if A1 is not empty and the next empty would be A2.

    Can you modify this part to insert empty rows starting from row 27 (number depends on how many "Ok" it will found) and paste data overthere?

    Thank you
    Reply With QuoteRegister To Reply
  8. 03-25-2013, 05:00 PM #8
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to copy sells 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 03-26-2013, 08:40 AM #9
    Skorpionkz is offline
    Registered User
    Join Date
    03-05-2013
    Location
    Dublin
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: VBA to copy sells

    Thank you!!!

    This is what I need
    Reply With QuoteRegister To Reply
  10. 03-26-2013, 10:47 AM #10
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to copy sells

    You are welcome!
    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)

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