Hi, looking for some assistance with the below code. I have searched and read through many many threads and posts and tried many many solutions offered to no avail.
I had this working last Friday but it has now ceased working.MPM_CoverSummaryPG1>
I have 2 sheets involved.
The first sheet (MPMFilterAV) is a query based table which is filtered based on drop down selections on another sheet.
The second sheet (MPM_CoverSummaryPG1>) contains the count of rows of the filtered table in cell M1 (AVCount).
If the table has results then I need to insert entire rows below Row 2 on MPM_CoverSummaryPG1> equal to the number in cell M1/AVCount.MPM_CoverSummaryPG1>. At the moment it is inserting 2 rows above Row A. I believe the problem may be in my syntax here: Sheets("MPM-CoverSummaryPG1>").Range("2" & ":" & nRows).Insert Shift:=xlUp, CopyOrigin:=xlFormatFromRightOrBelow, but I am not 100% sure. I have also tried using Rows instead of Range and get the same result.
E.G. If AVCount = 5, then I want to insert 6 rows below row 2 in MPM_CoverSummaryPG1>
I had this working last Friday but it has now ceased working.MPM_CoverSummaryPG1>
I have 2 sheets involved.
The first sheet (MPMFilterAV) is a query based table which is filtered based on drop down selections on another sheet.
The second sheet (MPM_CoverSummaryPG1>) contains the count of rows of the filtered table in cell M1 (AVCount).
If the table has results then I need to insert entire rows below Row 2 on MPM_CoverSummaryPG1> equal to the number in cell M1/AVCount.MPM_CoverSummaryPG1>. At the moment it is inserting 2 rows above Row A. I believe the problem may be in my syntax here: Sheets("MPM-CoverSummaryPG1>").Range("2" & ":" & nRows).Insert Shift:=xlUp, CopyOrigin:=xlFormatFromRightOrBelow, but I am not 100% sure. I have also tried using Rows instead of Range and get the same result.
E.G. If AVCount = 5, then I want to insert 6 rows below row 2 in MPM_CoverSummaryPG1>
VBA Code:
Sub MPMPrev_Click()
Dim numRows As Long
nRows = AVCount + 1
If Sheets("PolicyExtensionsandEndsts").Range("E45") = "Covered" Or Sheets("PolicyExtensionsandEndsts").Range("E45") = "Specified Items" Then
Sheets("MPM-CoverSummaryPG1>").Activate
Sheets("MPM-CoverSummaryPG1>").Range("2" & ":" & nRows).Insert Shift:=xlUp, CopyOrigin:=xlFormatFromRightOrBelow
ElseIf Sheets("PolicyExtensionsandEndsts").Range("E45") = "Not Covered" Then
Sheets("MPM-Cover SummaryPG1>").Rows("1:1").Select
Selection.Delete
End If
End Sub