If statement for pasting entries into another sheet

  • Hi,

    Im creating a new sheet in a spreadsheet that has teh code Im basing my new macro off of.

    The file is attached. The question i have pertains to sheet 4 and 5, the two wishlist sheets.

    The code I have is copied from the code for the Add Data macro in the add request sheet. You can run it to understand what Im saying.

    Basically the existing code was used to match the broker name in column A in sheet "add request" and paste the information in the top (B1:B5) to the matched column B in the sorted by broker sheet. Try it for yourself

    Now what I need help with:

    in the wishlist sheets its a similar idea except the broker might not exist yet in the "Wishlight - Brokers" sheet column B. So its first assessing whether the broker listed in sheet "Add Wislist" Column A exists or not and if it doesnt then paste the broker name and the info from B3:B5. If it already exists and can be matched in Column B in "wishlist - brokers" then do what the macros is already doing and duplicate the information in B3:b5 in "Add Wislist" for a row beneath the broker name in "Wishlight - Brokers"

    Here is the code i have. The code assumes that the Broker is already listed and that is what I am trying to fix, adding the additional if statement for when it is not listed

    [Blocked Image: http://www.excelforum.com/clientscript/select_all_icon.jpg] [Blocked Image: http://static.beckelman.net/v1/img/page_white_copy.png]

    Sub AddToWishlist()
    Const WishlistWs As String = "Add Wishlist"
    Const SortedListWs As String = "Wishlist - Brokers"
    Const FR1 As Integer = 8
    Const FR2 As Integer = 2
    Const DataStg As String = "B3:B5"
    Dim WkRg As Range
    Dim Rg As Range
    Dim LR As Long
    Dim DataRg As Range
    Dim I As Integer
    With Sheets(WishlistWs)
    Set WkRg = Range(.Cells(FR1, "A"), .Cells(Rows.Count, "A").End(3))
    Set DataRg = .Range(DataStg)
    End With
    With Sheets(SortedListWs)
    For I = .Cells(Rows.Count, "B").End(3).Row To FR2 Step -1
    If (Application.IsError(Application.Match(.Cells(I, "B"), WkRg, 0))) Then
    .Cells(I + 1, "B").EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
    .Cells(I + 1, "B").Offset(0, 1).PasteSpecial _
    Paste:=xlPasteValues, Transpose:=True
    End If
    End With
    End Sub

    Im just not too sure what that coding is or if other things need to be changed within the code first

    Any ideas?



    • Test.xlsm

      (39.07 kB, downloaded 32 times, last: )
  • Re: If statement for pasting entries into another sheet

    looks like my code didnt work properly for the vba,
    it wont let me edit hte post for some reason so im posting it in proper context here