Results 1 to 1 of 1

How to Find Cell, Copy contents in adjacent cells, then paste to new item

Threaded View

wakaan808 How to Find Cell, Copy... 12-05-2019, 02:01 PM
  1. #1
    Registered User
    Join Date
    11-22-2019
    Location
    Arctic
    MS-Off Ver
    2010
    Posts
    2

    How to Find Cell, Copy contents in adjacent cells, then paste to new item

    Essentially I have a list of items in column A that will occasionally need to be duplicated by adding an integer at the end. e.g.: Item, Item1, Item2,...
    Within columns B:E there are formulas that reference columns I:L and the relative row they are contained in.

    e.g: If Item is in row 30, B:L reads
    B: =I30
    C: =J30
    D:=k30
    E:=L30

    Columns I:L reference a specific cell on a sheet named "Item"

    e.g.:
    I:=Item!A12
    J:=Item!B12
    K:=Item!C12
    L:=Item!D12

    When I run this macro, it should then create Item1 that will then read:

    B: =I31
    C: =J31
    D:=k31
    E:=L31

    I:=Item1!A12
    J:=Item1!B12
    K:=Item1!C12
    L:=Item1!D12

    This would normally be a simply copy from reference cell 30 and offset paste below. However, duplicate items will be created above/below in alphabetical order which throws off the cell reference of row 30. Essentially I need to find or lookup the contents that contain the name "Item" within column A and paste those into the newly found name "Item1". While simultaneously changing the formula names with an increment.

    The button called "New WD_CWC" is the macro I'm testing this with. It uses the Module called Mod_WD_CWC and the sub WD_CWC.

    This section properly increments the items within column A every time the macro is run.

    itm = "WD_CWC"
    nitm = itm
    
            Application.ScreenUpdating = False
            
    '// Finds nitm within column A, creates new item offset one row below within column A with an integer after. (WD_CWC1,WD_CWC2,...)
    
    Do
        Set fnd = Range("A:A").Find(nitm)
        If fnd Is Nothing Then
            Exit Do
          Else
            i = i + 1
            nitm = itm & i    '// keep adding one till the value is not found
        End If
    Loop
    
    Cells(Rows.count, 1).End(xlUp).Offset(1) = itm & (i)
    Set newrow = Cells(Rows.count, 1).End(xlUp).Offset(1)

    This section of the code is supposed to take care of the copy/paste aspect but the references are wrong, it doesn't increment, and it places the paste in a weird location the first time it is run.

    '//Finds itm within column A, copies contents from B:L, pastes 1 row down
    
    Set fnd = Range("a:a").Find(itm)
    If Not fnd Is Nothing Then
       newrow.Offset(, 1).Resize(, 11).Formula = fnd.Offset(, 1).Resize(, 11).Formula
    
    Else: MsgBox itm & " not found"
    End If

    I will also add I have an autosort sub contained within the sheet CABINETS every time an item is added within column A that might affect some things.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Find matches, copy and paste adjacent cell
    By amein in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-23-2016, 11:21 AM
  2. [SOLVED] split cell contents into rows, and copy adjacent cells down
    By FDibbins in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-15-2016, 12:02 AM
  3. [SOLVED] Help with VBA Code - Find cells with specific value, Copy/Paste value from adjacent colmns
    By Mikeyc85 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-09-2015, 12:32 PM
  4. [SOLVED] Macro to find specific word in cell and then copy and paste the adjacent cell upwards
    By raytej82 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-04-2014, 05:33 PM
  5. [SOLVED] find value, copy value of adjacent cell and paste it on another sheet
    By Ajgor in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-25-2012, 04:21 PM
  6. Replies: 2
    Last Post: 03-28-2012, 11:47 AM
  7. Replies: 1
    Last Post: 01-18-2008, 07:01 PM

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