#1
|
|||
|
|||
Macro not Performing Formula Update as Expected
On the attached file, the first item I am asking the "AddaShift" Macro to perform is copy row 17 and insert it directly above. While it does some other things properly, the only thing which I cannot get it to do is automatically update the formula in Cell J17. I would like for it to continue all the way to the last row which contains data but it keeps losing 1 row each time the macro runs. I assumed the formula would keep going to the last row with data but unfortunately not. Anybody have any suggestions? |
#2
|
|||
|
|||
Hi,
Change all of your pastspecials Code:
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Code:
Selection.PasteSpecial Paste:=xlPasteAll |
#3
|
|||
|
|||
Charlesdh,
I appreciate your reply but that portion of the code is supposed to paste the value only; not the formula. The portion of the code which I want to copy the formula is: Rows("17:17").Select Selection.Copy Rows("17:17").Select Selection.Insert Shift:=xlDown The formual in cell J17 currently goes to row 89. When the macro runs, it still only goes to row 89 but I would like it to go to row 90 since it has added a row. |
#4
|
|||
|
|||
Hi,
I see what you are talking about. I'll see if I can come up with a solution. It's possible another member may be able to also find s solution. |
#5
|
|||
|
|||
Hi,
I been playing around with this. But can nor find a solution in getting the code to change. Unless some one else has an answer you may need to use a "Macro" |
#6
|
|||
|
|||
Hi,
I have a bit of code that may work. However I have to go for now. Will try to have it tomorrow. |
#7
|
|||
|
|||
Hi,
I modified your code. I left most of it in. Copy and paste. Test on a copy. If it works then try it in your file. Be sure you back up before you run on the actual data. Code:
Sub AddADay() ' ' AddAShift Macro ' ' Dim ws As Worksheet Set ws = Sheets("PEUG") lrow = ws.Range("D65536").End(xlUp).Row If MsgBox("Has ALL Data for Previous Shift Been Entered? If not, click 'No'.", vbYesNo) = vbNo Then Exit Sub ActiveSheet.Unprotect Rows("17:17").Copy Rows("17:17").Insert Shift:=xlDown Application.CutCopyMode = False Cells(17, 10).Formula = "=SUMPRODUCT(I17:I" & lrow & ",--((G17:G" & lrow & ")<31))/SUMPRODUCT(H17:H" & lrow & ",--((G17:G" & lrow & ")<31))" Cells(17, 17).Formula = "=SUMPRODUCT(P17:P" & lrow & ",--((N17:N" & lrow & ")<31))/SUMPRODUCT(O17:O" & lrow & ",--((N17:N" & lrow & ")<31))" Cells(17, 24).Formula = "=SUMPRODUCT(W17:W" & lrow & ",--((U17:U" & lrow & ")<31))/SUMPRODUCT(V17:V" & lrow & ",--((U17:U" & lrow & ")<31))" Cells(17, 31).Formula = "=SUMPRODUCT(AD17:AD" & lrow & ",--((AB:AB" & lrow & ")<31))/SUMPRODUCT(AC17:AC" & lrow & ",--((AB17:AB" & lrow & ")<31))" Cells(17, 38).Formula = "=SUMPRODUCT(AK17:AK" & lrow & ",--((AI17:AI" & lrow & ")<31))/SUMPRODUCT(AJ17:AJ" & lrow & ",--((AI17:AI" & lrow & ")<31))" Range("h17:i17").ClearContents Range("o17:p17").ClearContents Range("v17:w17").ClearContents Range("ac17:ad17").ClearContents Range("aj17:ak17").ClearContents Range("J18:K18").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("q18:r18").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("x18:y18").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("ae18:af18").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("al18:am18").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("A1").Select ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ False End Sub |
#8
|
|||
|
|||
Thanks. Working well! Much obliged.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Performing filtering to tasks that occur during the next three months | jessy | Project | 1 | 05-30-2014 05:40 AM |
macro to update fields | PeaceDove | Word | 3 | 01-17-2012 02:45 PM |
Compare and Update Macro | AaronMoss | Excel Programming | 2 | 05-06-2011 04:54 AM |
Macro to update fields | rhatx | Word VBA | 0 | 03-02-2011 12:14 PM |
Excel 2011 not performing paste special correctly | mzimmers | Excel | 1 | 01-04-2011 06:17 AM |