Argh! just when you think you are done.
Sub CopyIt()
Dim NextRow As Long
Dim LastRow As Long
NextRow = Sheets("Paid").Range("C65536").End(xlUp).Row + 1
LastRow = Sheets("List").Range("C65536").End(xlUp).Row
Sheets("List").Range("B8:L" & LastRow).Copy
With Sheets("Paid").Range("B" & NextRow)
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
End Sub
In the above reference code how would i add Having the value present found in B2 in the 'LIST' page copied into the M column adjacent to all copied entries?
In other words after copying the appropriate data from the 'LIST' page to the 'PAID' page I need to have the 'LIST B2' value copied into the ' PAID M' cells that correspond to the newly pasted data.
Also I never got a reply on adding an auto date for the 'LIST B2' cell to the
Sub Workbook_Open()
Dim MyResponse As VbMsgBoxResult
MyResponse = MsgBox("Do you want to clear the cells?", vbYesNo + vbQuestion)
If MyResponse = vbYes Then
Sheets("List").Range("B8:E65536").ClearContents
Sheets("List").Range("H8:I65536").ClearContents
End If
End Sub
Any further help would be appriciated on those two matters.
New question same workbook.
My boss has asked me to Idiot prove the insertion of extra data onto these forms.
I currently have room for 25 seperate invoices from rows 8 - 32. Data is entered into those rows from Columns B - M. The previously attached Spreadsheet is a copy of the default form.
If there are more or less than 25 invoices, my boss would like to have a macro or VBA that can be run that will add or subtract rows. Basically i manually put them in and simply highlight and insert then copy down the cells. Since i have been promoted and they are not sure of the experience of the replacement coming in he doesn't want me running around showing them how to maintain the Spreadsheet.
SO .....
I need a macro/VBA code that will do the folloing:
Insert rows between the last current row (32) and the totals row (34) that will contain all formulas and also maintain an ongoing count of invoice (already present in Row A just needs to continue)
and a seperate one that :
Does the opposite. Deletes excess rows
Ummmm while typing this edit someone pointed out that my little auto change of the values when M brings back a value different from #N/A removes the formula. Soooooo, When I use the clear code to remove the info in B-E and H & I the formulas have changed is there anyway to Clear those cells and populate the cells K8:L with the formulas that were removed?
Just in case you need to know the formulas:
in K it is =sum(H#*G#) same row
in L it is =sum(L#*G#)
so any help on these matters is greatly appreciated.