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.
Bookmarks