Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    I need a macro to copy data 2 columns to the right (I.e Column c) of where text "Net Profit" appears in column A and to Paste Value this one column to the right of where text
    "19. Purchases" appears

    Your assistance will be most appreciated
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try this one:
    Code:
    Sub moveNP()
    Dim lRow As Long, lPurch As Long, i As Long
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To lRow
    If Left(Cells(i, 1), 2) = "19" Then
        lPurch = i
    End If
    If Cells(i, 1) = "Net Profit" Then
        Cells(i, 3).Cut Destination:=Cells(lPurch, 2)
    End If
    Next
    
    End Sub

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for the help

    Regards

    Howard

  4. #4
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by mbarron View Post
    Try this one:
    Code:
    Sub moveNP()
    Dim lRow As Long, lPurch As Long, i As Long
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To lRow
    If Left(Cells(i, 1), 2) = "19" Then
        lPurch = i
    End If
    If Cells(i, 1) = "Net Profit" Then
        Cells(i, 3).Cut Destination:=Cells(lPurch, 2)
    End If
    Next
    
    End Sub

    It would be appreciated if you would amend the code to copy the data from 3 columns to the right of Net profit to paste this a a value (using PasteSpecial Value) 2 column to the right of where the text 19. Purchases appears

    Your assistance will be most appreciated

    Regards

    Howard

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Code:
    Sub moveNP()
    Dim lRow As Long, lPurch As Long, i As Long
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To lRow
    If Left(Cells(i, 1), 2) = "19" Then
        lPurch = i
    End If
    If Cells(i, 1) = "Net Profit" Then
        Cells(i, 4).Copy
        Cells(lPurch, 2).PasteSpecial Paste:=xlPasteValues
        'Cells(i, 4).ClearContents
    End If
    Next
    
    End Sub
    Clear the ' from in front of the 'Cells(i, 4).ClearContents if you want the value/formula in the Net Profits row to be deleted

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for all help, much appreciated

Posting Permissions

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