+ Reply to Thread
Results 1 to 21 of 21

Sorting, Paste values VBA code

  1. #1
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    308

    Sorting, Paste values VBA code

    Hi,

    I got stuck while recording a macro where cell ranges are recorded which are fixed. But while working, data range changes which macro to be taken care.

    An excel file attached with necessary details.

    Pl. help.

    Thanks,
    Nagesh.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,433

    Re: Sorting, Paste values VBA code

    Please Login or Register  to view this content.
    Last edited by mehmetcik; 11-25-2021 at 07:31 AM. Reason: Code to Copy Paste Values Columns D to K added.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    308

    Re: Sorting, Paste values VBA code

    Hi mehmetcik,

    While executing below error msg displayed.

    Run-time error '9':
    Subscript out of range

    When i click Debug, below message highlighted in yellow:

    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=11, Criteria1:="<>"

    Pl. help.

    Nagesh.

  4. #4
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    308

    Re: Sorting, Paste values VBA code

    Hi mehmetcik,

    Pl. ignore the above post.

    Will check thoroughly and will come back.

    Thanks for your time and efforts.

    Nagesh.

  5. #5
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    308

    Re: Sorting, Paste values VBA code

    Hi mehmetcik,

    Run-time error '438':

    Object doesn't support this property or method

    'Select visible cells
    Range("K2:K" & lr).Selection.SpecialCells(xlCellTypeVisible).Select

    Pl. look in to it.

    Thanks,
    Nagesh.

  6. #6
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,433

    Re: Sorting, Paste values VBA code

    Sorry.

    Change that line to:

    Please Login or Register  to view this content.

  7. #7
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    9,995

    Re: Sorting, Paste values VBA code

    It is almost never needed to make use of Activate or Select...

    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  8. #8
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    308

    Re: Sorting, Paste values VBA code

    First of all Thank you mehmetcik & sintek for your time and efforts.

    Sintek, your code looks small & simple.

    Hope the numbers in the brackets are column numbers (My actual file have 100+ columns and i have to understand the concept first to incorporate in my file)


    Request you to pl. include code as per below:

    1. Sorting sequence should be Column 11 first and then Column 1,2 & 3 together (In the sample file only 1 column shown but in my original file three columns i.e. Col. 1,2 & 3 together to be sorted. Pl. provide code for sorting three level sorting)
    2. Col. B Formulas to be replaced with Paste values (only to the extent of "CLOSED" rows) (Excluding the Header Row)
    3. Col. E:J Data to be deleted (only to the extent of "CLOSED" rows) (Excluding the Header Row)
    4. After the executing your code, filters are vanished, but filters are to be kept in tact.

    Pl. help.

    Thanks,
    Nagesh.

  9. #9
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    9,995

    Re: Sorting, Paste values VBA code

    Perhaps you should upload samples file representing actual data and explain step by step what it actually is what you are wanting to achieve...Goal posts have been moved...

    sample file to have before | after scenario - showing expected result...

  10. #10
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    308

    Re: Sorting, Paste values VBA code

    Hi Sintek,

    Thanks for the support and interest.

    An excel file attached with necessary details.

    Pl. help.

    Thanks,
    Nagesh.
    Attached Files Attached Files

  11. #11
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    9,995

    Re: Sorting, Paste values VBA code

    This produces required result...
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by sintek; 11-29-2021 at 04:06 AM. Reason: Added sample file

  12. #12
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    308

    Re: Sorting, Paste values VBA code

    Hi Sintek,

    It is working wonderfully. But noticed a small issue. When adding data in new row, all the formulas are copied to newly added row except where the data is deleted (Red colour columns).

    Pl. look in to it.

    Thanks,
    Nagesh.

  13. #13
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    9,995

    Re: Sorting, Paste values VBA code

    You are working with listobjects...When adding new data, the formulas are automatically populated as per first rows...Your previous code cleared all these formulas so hence nothing to automatically add...
    As you are manually entering anyway, first copy last row and paste to below and then change column data...That way, all formulas will be intact...
    Last edited by sintek; 11-29-2021 at 06:08 AM.

  14. #14
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    308

    Re: Sorting, Paste values VBA code

    Since it is Table, when i put data in to new row (in first column), all the formulas are automatically getting copied as per the last row. Even yellow highlighted columns' formulas are copied, which as per the code, become paste values but formulas are copied to new row. Only Red columns (Where the contents are cleared) for which formulas are not copied.

    Pl. do something.

  15. #15
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    9,995

    Re: Sorting, Paste values VBA code

    Pl. do something.
    How bout ... You do something...
    It is working wonderfully
    As far as I'm concerned your initial requirement and the extra requirements have been fulfilled...
    But noticed a small issue. When adding data in new row
    This is a manual entry and a new requirement and has nothing to do with this thread...
    I don't even understand what it is you are wanting as you are not being transparent...
    Last edited by sintek; 11-29-2021 at 08:02 AM.

  16. #16
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    308

    Re: Sorting, Paste values VBA code

    Sorry if am not able to explain my requirement.

    I checked each and every step manually and noticed that if the code exclude last row while deleting the contents (Clear contents), the issue is resolved (the formulas are copied to new row).

    Pl. incorporate the change in the code. (since i have zero knowledge in the coding).

    Pl. help.

    Thanks,
    Nagesh.

  17. #17
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    9,995

    Re: Sorting, Paste values VBA code

    So now you don't want the last row of if "CLOSED" to be cleared...or values...
    Change 1 to 2
    Please Login or Register  to view this content.

  18. #18
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    308

    Re: Sorting, Paste values VBA code

    OK but This logic should be applicable only for "CLEAR CONTENTS".
    For "PASTE VALUES", it should be as it is (-1).

    Will the code work if I Copy and paste [With .Offset(1).Resize(.Rows.Count - 1)] after [Next Area]?

    If not pl. advise how to incorporate the change.

  19. #19
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    9,995

    Re: Sorting, Paste values VBA code

    Please Login or Register  to view this content.

  20. #20
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    308

    Re: Sorting, Paste values VBA code

    This Gem Sintek.

    Thank you very much for your time and effort.

    Nagesh.

  21. #21
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    9,995

    Re: Sorting, Paste values VBA code

    .........................
    Thanks.png

+ 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. Code to prevent copy & pasting values from one tab to another
    By MARC_IN_CT in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-13-2021, 08:12 AM
  2. [SOLVED] Sorting the data with Exchange code for different months and paste it in sequence in next
    By boddulus in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-05-2014, 07:37 PM
  3. [SOLVED] Using code to paste values
    By kosherboy in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 06-12-2014, 11:36 AM
  4. Help with a VBA code to paste only values from a Web Query
    By adtamez in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-06-2014, 02:16 PM
  5. [SOLVED] Changing Current Code from Paste Formulas to Paste Values
    By freybe06 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-24-2013, 12:33 PM
  6. Copy and paste code pasting won't paste just values
    By thehotbreadguy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-09-2013, 11:12 PM
  7. Modify copy & paste code with sorting from A-Z
    By tantcu in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-23-2013, 10:49 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