+ Reply to Thread
Results 1 to 6 of 6

Copy cells in a defined range > 0, paste in a defined cell on other worksheet

  1. #1
    Registered User
    Join Date
    06-26-2018
    Location
    Perth
    MS-Off Ver
    2016
    Posts
    18

    Copy cells in a defined range > 0, paste in a defined cell on other worksheet

    Hi All,

    I am trying to copy cells in a range (sheet1) that are greater than zero, and paste to another worksheet (sheet2) in a defined cell. Currently the code I am using works, but it doesn't have the flexibility of choosing a defined range to copy from, and a defined cell to paste the values to.

    The copied range is starts in sheet 1, cell A1. The copied range pastes to Sheet2, cell A2 (by default). Wondering if there is a VBA wiz out there than can explain how to control where you copy and paste your ranges to between worksheets?

    Am new to this community and grateful for the responses I have had to my problems so far! Thanks in advance

    Here is my code:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Copy cells in a defined range > 0, paste in a defined cell on other worksheet

    Hi,

    You could for instance use inputbox with type = 8 to ask user where input and output ranges are.
    And as you will work with ranges, then looping by range collection members will be probably a natural choice.

    See such code (I assumed that you need to copy only the cells with positive values - as described in the post, not whole rows, as it is done in your code);

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Best Regards,

    Kaper

  3. #3
    Valued Forum Contributor mohan.r1980's Avatar
    Join Date
    09-18-2010
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2010 (windows7)
    Posts
    729

    Re: Copy cells in a defined range > 0, paste in a defined cell on other worksheet

    try below code
    Please Login or Register  to view this content.
    Regards,
    MohanS


    "Perfection is not attainable, but if we chase perfection we can catch excellence." - Vince Lombardi

    You can simply say thanks by clicking "*Add Reputation" icon

  4. #4
    Registered User
    Join Date
    06-26-2018
    Location
    Perth
    MS-Off Ver
    2016
    Posts
    18

    Re: Copy cells in a defined range > 0, paste in a defined cell on other worksheet

    Hi Kaper,

    Thank you for your post. I was able to modify this to exactly what I needed. Given this will be integrated into a very large spreadsheet which much more code before hand, I slightly changed it to paste in the specific cell on the next sheet I needed.

    Great work! This has made my job just a bit easier

    Included my edit to this:

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

  5. #5
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Copy cells in a defined range > 0, paste in a defined cell on other worksheet

    Glad to hear so,

    So I overestimated the need for flexibility in : "flexibility of choosing a defined range to copy from"
    And indeed added not needed flexibility to what you wanted to have defined: "a defined cell to paste the values to"

    Just one side note:

    If you run code once, and there were 5 positive values in A1:A15 they will be copied to E5:E9
    but then when you change your data in A1:A15 and only 2 values are positive, then they will be copied to E5 and E6, but content of E7:E9 will remain unchanged.
    So I'd suggest adding clearing contents of possible output cells. for instance in such way:

    Please Login or Register  to view this content.
    Could be also easier just by adding to code from previous post (anywhere above the For loop).
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    06-26-2018
    Location
    Perth
    MS-Off Ver
    2016
    Posts
    18

    Re: Copy cells in a defined range > 0, paste in a defined cell on other worksheet

    Hi Mohan,

    I ran your code this morning and it also is applicable to the task I am trying to achieve. Has given me a second perspective on the same problem. Works well!

    Thank you for taking the time to help me

    Regards,
    Jackson

+ 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. Range.Copy limitation issue (1004 application-defined or object-defined error)
    By macro_student in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-14-2016, 10:21 AM
  2. [SOLVED] If word entered not in defined name range, copy & paste it to bottom of defined name range
    By Butcher1 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-09-2014, 01:08 PM
  3. Copy, paste, replace- user defined range in all the defined sheets
    By aganesan99 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-13-2014, 12:28 PM
  4. [SOLVED] Macro to copy a cell range and paste to a new sheet 'n' times where 'n' defined by formula
    By staminaboy in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-26-2013, 11:39 AM
  5. EXCEL 2003 copy paste a defined range in a cell
    By EXCELNOOB123 in forum Excel General
    Replies: 1
    Last Post: 03-07-2011, 10:01 PM
  6. Finding a defined range of cells then Copy & Paste
    By bernborough in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-11-2010, 06:45 PM
  7. [SOLVED] Application-defined or object-defined error (worksheet, range, sel
    By darxoul in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-02-2006, 09: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