Results 1 to 6 of 6

Thread: VBA Copy paste special values based on cell reference
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Copy paste special values based on cell reference

    Hi All,

    I'm having some issues with VBA. I want the code to look up cells within a range, that when greater than zero, copies and pastes adjacent columns as values in the same location (to remove the formulas).

    The lookup range is J10:J371, and if greater than zero, I want adjacent cells in column J,K,L to copy and paste as values.

    For example, J52 = 2000, then J52:L52 copies and pastes as values in the same place.

    I saw something similar on the below thread but I can't quite get it to work on mine.

    https://www.mrexcel.com/forum/excel-...reference.html

    Thank you in advance.

  2. #2
    Board Regular gallen's Avatar
    Join Date
    Jun 2011
    Location
    Manchester UK
    Posts
    1,812
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Copy paste special values based on cell reference

    Hello and welcome

    This should do what you ask:

    Code:
    Sub HardWriteValue()
        Dim c As Range
        For Each c In Range("J10:J371")
            If c > 0 Then
                Application.EnableEvents = False
                c = c
                c.Offset(, 1) = c.Offset(, 1)
                c.Offset(, 2) = c.Offset(, 2)
                Application.EnableEvents = True
            End If
        Next c
    End Sub

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


  3. #3
    New Member
    Join Date
    Apr 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Copy paste special values based on cell reference

    Quote Originally Posted by gallen View Post
    Hello and welcome

    This should do what you ask:

    Code:
    Sub HardWriteValue()
        Dim c As Range
        For Each c In Range("J10:J371")
            If c > 0 Then
                Application.EnableEvents = False
                c = c
                c.Offset(, 1) = c.Offset(, 1)
                c.Offset(, 2) = c.Offset(, 2)
                Application.EnableEvents = True
            End If
        Next c
    End Sub
    Thank you so much for the quick response, it worked perfectly! One more thing, what would I need to add to ensure the macro does the same process across all sheets within my file simultaneously?

    Thanks again.

  4. #4
    Board Regular gallen's Avatar
    Join Date
    Jun 2011
    Location
    Manchester UK
    Posts
    1,812
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Copy paste special values based on cell reference

    Just loop through all worksheets:

    Code:
    Sub HardWriteValue()
        Dim c As Range
        Dim ws As Worksheet
        For Each ws In ThisWorkbook.Worksheets
            For Each c In ws.Range("J10:J371")
                If c > 0 Then
                    Application.EnableEvents = False
                    c = c
                    c.Offset(, 1) = c.Offset(, 1)
                    c.Offset(, 2) = c.Offset(, 2)
                    Application.EnableEvents = True
                End If
            Next c
        Next ws
    End Sub
    Last edited by gallen; Apr 4th, 2019 at 05:51 AM.

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


  5. #5
    New Member
    Join Date
    Apr 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Copy paste special values based on cell reference

    Thanks Gallen, I'm still a bit new to VBA so thanks for your help!

  6. #6
    Board Regular gallen's Avatar
    Join Date
    Jun 2011
    Location
    Manchester UK
    Posts
    1,812
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Copy paste special values based on cell reference

    No problem! Happy it works

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •