Results 1 to 6 of 6

Thread: VBA to copy cell value only into a range
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2017
    Location
    Derby. England
    Posts
    171
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to copy cell value only into a range

    Hi,

    I have this following code:

    Code:
    If Not Intersect(Target, Range("C8")) Is Nothing Then
    If Target.Value = 66 Then
        Target.Offset(, 1) = 66
        Target.Offset(, 1).AutoFill Range(Target.Offset(, 1), "Z" & Target.Row)
    I would like to alter it so it does the following:

    1. Copies anything entered in cell C8 to Z8.
    2. Only copies the value and not the formatting of the cell.
    3. If I delete the value in C8 it also deletes the rest of the values in the range as well.

    Any help would be appreciated.

    Kind Regards

    Dan

  2. #2
    Board Regular
    Join Date
    Nov 2015
    Location
    UK
    Posts
    263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to copy cell value only into a range

    Quote Originally Posted by danbates View Post
    Hi,

    I have this following code:

    Code:
    If Not Intersect(Target, Range("C8")) Is Nothing Then
    If Target.Value = 66 Then
        Target.Offset(, 1) = 66
        Target.Offset(, 1).AutoFill Range(Target.Offset(, 1), "Z" & Target.Row)
    I would like to alter it so it does the following:

    1. Copies anything entered in cell C8 to Z8.
    2. Only copies the value and not the formatting of the cell.
    3. If I delete the value in C8 it also deletes the rest of the values in the range as well.

    Any help would be appreciated.

    Kind Regards

    Dan

    Something like this, maybe? When it's run, this will check to see if there's a value in C8 - if there is, it's copied to Z8, else Z8 is left blank.

    Code:
    If Range("C8").Value <> "" then
        Range("Z8").Value = Range("C8").value
    Else
        Range("Z8").Value = ""
    End If
    Is that what you're after?

  3. #3
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,260
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA to copy cell value only into a range

    Im not sure exactly what point 3 means but maybe try:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("C8")) Is Nothing Then Range("Z8").Value = Target.Value
    
    End Sub

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,338
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    10 Thread(s)

    Default Re: VBA to copy cell value only into a range

    Try this:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    'Modified 6/5/18 4:20 AM EDT
    If Not Intersect(Target, Range("C8")) Is Nothing Then
    If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
    Target.Offset(, 23).Value = Target.Value
    End If
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  5. #5
    Board Regular
    Join Date
    Oct 2017
    Location
    Derby. England
    Posts
    171
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to copy cell value only into a range

    Hi,

    It's very close. I've just read my post again and I didn't exactly put it how I wanted.

    When I said "1. Copies anything entered in cell C8 to Z8." I meant as a range C8:Z8.

    Sorry my mistake.

    Thanks
    Dan

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,338
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    10 Thread(s)

    Default Re: VBA to copy cell value only into a range

    Try this:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    'Modified 6/5/18 4:40 AM EDT
    If Not Intersect(Target, Range("C8")) Is Nothing Then
    If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
    Target.Resize(, 24).Value = Target.Value
    End If
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

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
  •