+ Reply to Thread
Results 1 to 8 of 8

Target.Adress not working when pasting a range over a column that is tracked for changes

Hybrid View

Beginer25 Target.Adress not working... 11-20-2019, 08:13 AM
davesexcel Re: Target.Adress not working... 11-20-2019, 08:20 AM
romperstomper Re: Target.Adress not working... 11-20-2019, 08:22 AM
Beginer25 Re: Target.Adress not working... 11-20-2019, 08:45 AM
romperstomper Re: Target.Adress not working... 11-20-2019, 08:53 AM
Beginer25 Re: Target.Adress not working... 11-20-2019, 10:02 AM
romperstomper Re: Target.Adress not working... 11-20-2019, 10:16 AM
Beginer25 Re: Target.Adress not working... 11-20-2019, 11:06 AM
  1. #1
    Registered User
    Join Date
    03-01-2013
    Location
    România
    MS-Off Ver
    Excel 2007
    Posts
    22

    Target.Adress not working when pasting a range over a column that is tracked for changes

    Hello,


    I am trying to copy at the end of a column the new values from a column in a different sheet. Simply put i am trying to synch the columns in both sheets.
    I found some code on the net but it only worked up to the point i was updating every single cell directly.

    What i want is the code to work when you paste an entire column over the column in sheet 2

    I have done some changes to this code but it's not working when i paste the entire column with some values instead of the blanks or extra values or a bigger range including this column.


    note: i have tried some variations of Intersect but only worked on individual cells not the full column or the full column or a bigger range including this column.

    EDIT:
    attached is an example. i want to synch by using the Fruit code.
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sourceSheet As Worksheet, targetSheet As Worksheet
    Dim syncRange As String
    Dim isInRange
    
    'Set the source and target sheets here
    Set sourceSheet = Sheet9
    Set targetSheet = Sheet3
    
    'This will be the column that needs to be synced
    syncRange = "C13:C500"
    
    'Check if the modified cell lies within the range to be synced
    Set isInRange = Application.Intersect(Target, Range(syncRange))
    
    If isInRange Is Nothing Then
    'Do nothing if the cell falls outside the range
    Else
    'Else sync the cell contents
    Sheet3.Range("d4").End(xlDown).Offset(1, 0) = sourceSheet.Range(Target.Address)
    
    End If
    
    End Sub
    Attached Files Attached Files
    Last edited by Beginer25; 11-20-2019 at 08:20 AM.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,529

    Re: Target.Adress not working when pasting a range over a column that is tracked for chang

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,112

    Re: Target.Adress not working when pasting a range over a column that is tracked for chang

    You can't assign the value of a multi-cell range to one cell. You need to copy and paste, or resize the destination. Also, if your Target is an entire column, the only place you could paste it is to row 1 of the destination worksheet.
    Everyone who confuses correlation and causation ends up dead.

  4. #4
    Registered User
    Join Date
    03-01-2013
    Location
    România
    MS-Off Ver
    Excel 2007
    Posts
    22

    Re: Target.Adress not working when pasting a range over a column that is tracked for chang

    Hi Rory,

    Not an entire column, sorry. up to 500 entries.
    Is there any way to do this with a For function for target impacted cells?

    Cheers,
    Beginer

  5. #5
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,112

    Re: Target.Adress not working when pasting a range over a column that is tracked for chang

    Try using:

    Sheet3.Range("d4").End(xlDown).Offset(1, 0).Resize(isinrange.rows.count).value = isinrange.value

  6. #6
    Registered User
    Join Date
    03-01-2013
    Location
    România
    MS-Off Ver
    Excel 2007
    Posts
    22

    Re: Target.Adress not working when pasting a range over a column that is tracked for chang

    Hi Rory,

    This is partially working good:
    - works as before for cell by cell editing
    - when pasting a few values these are seen as seen as cell update and the values get duplicated

    I will patch this with the bellow to remove the duplicates. Any thoughts on how not to pass the Blank values? they get caught between the values and give an blank cell.

    Sheet3.Range("D3:D500").RemoveDuplicates Columns:=1, Header:=xlYes

    Cheers,
    Beginer

  7. #7
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,112

    Re: Target.Adress not working when pasting a range over a column that is tracked for chang

    You can use a loop:

    If isInRange Is Nothing Then
    'Do nothing if the cell falls outside the range
    Else
    'Else sync the cell contents
    dim outRow as long
    outrow = Sheet3.Cells(Rows.count, "d").End(xlUp).Row + 1
    dim cell as range
    for each cell in isinrange.cells
    if len(cell.value) <> 0 then
    Sheet3.cells(outrow, "D").Value = cell.value
    outrow = outrow + 1
    end if
    next cell
    End If

  8. #8
    Registered User
    Join Date
    03-01-2013
    Location
    România
    MS-Off Ver
    Excel 2007
    Posts
    22

    Re: Target.Adress not working when pasting a range over a column that is tracked for chang

    Perfect!

    i have left the duplicate removal part at the end to get rid of them.
    i have also swapped .value from the code and replaced it with .text ... some ppl should burn for wanting to use 00 in front of numbers.....

+ 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. [SOLVED] Using Target Column in Range
    By lhargr2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-25-2019, 10:37 PM
  2. Count a range using serial #'s tracked
    By arumble in forum Excel General
    Replies: 7
    Last Post: 03-04-2019, 02:26 PM
  3. [SOLVED] Worksheet Change Event adding formulas to target range not working
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-08-2017, 05:46 AM
  4. Changing text of Target.Adress using replace in VBA
    By claudyio in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-14-2014, 09:17 AM
  5. Private Sub Worksheet_Change(ByVal Target As Range) not working
    By kaseyleigh in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-05-2013, 07:12 AM
  6. [SOLVED] Pasting range not working
    By reltub in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-20-2012, 10:19 PM
  7. Copy/Pasting Column Not Working
    By SPNesteby in forum Excel General
    Replies: 4
    Last Post: 01-22-2010, 11:57 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