Formula Not Copying Down Correctly After VBA Copy/Insert Procedure

[Boots6]

I have a Form for my excel with Text Boxes BelowRow and NumberOfRows that I use to get values for which cell the user wants to insert below and how many rows they would like to insert. The formulas must be included in the new rows so I copy the "BelowRow" and insert it above the row beneath it (i.e. if the user selects 10 for "BelowRow", the code will copy row 10 and insert it above row 9). This works fine except one formula is not copying down on the 'PUMP SUMMARY' sheet like it does on the 'CONCRETE SUMMARY' sheet. The formula in cell B10 of the PUMP SUMMARY sheet is =If('CONCRETE SUMMARY'!B10="","",'CONCRETE SUMMARY'!B10). The 2 sheets need to show the same information across rows (row B10 on the PUMP SUMMARY sheet needs to =B10 on the CONCRETE SUMMARY Sheet). The CONCRETE SUMMARY sheet has a similar formula that copies down just fine. On my form, if I put 2 as NumberOfRows and 10 as BelowRow, the PUMP SUMMARY sheet shows the following:

Before:
Cell B9's formula = =IF('CONCRETE SUMMARY'!B9="","",'CONCRETE SUMMARY'!B9)
Cell B10's formula = =IF('CONCRETE SUMMARY'!B10="","",'CONCRETE SUMMARY'!B10)

After:
Cell B9's formula = =IF('CONCRETE SUMMARY'!B9="","",'CONCRETE SUMMARY'!B9)
Cell B10's formula = =IF('CONCRETE SUMMARY'!B11="","",'CONCRETE SUMMARY'!B11)
Cell B11's formula = =IF('CONCRETE SUMMARY'!B12="","",'CONCRETE SUMMARY'!B12)
Cell B12's formula = =IF('CONCRETE SUMMARY'!B13="","",'CONCRETE SUMMARY'!B13)

I've tried using Offset a million different ways to get thing to work correctly, but I don't understand why it doesn't work like it does on the CONCRETE SUMMARY sheet where the code begins. That sheet's cells have formulas that reference the row they're in and it works fine. Please help! Code below:

Private Sub Add_Click()

If BelowRow <= 8 Then
If MsgBox("You Must Insert Below The Header", vbOKOnly) Then
AddAreaForm.BelowRow.Value = ""
AddAreaForm.NumberOfRows.Value = ""

Exit Sub

End If
End If

Dim intNumberOfRows As Integer
Dim intBelowRow As Integer
Dim RowIns As Integer
Dim CopyRow As Integer

Application.ScreenUpdating = True

If IsNumeric(BelowRow) And IsNumeric(NumberOfRows) Then

intNumberOfRows = NumberOfRows.Value
intBelowRow = BelowRow.Value
RowIns = 2

'Section for CONCRETE SUMMARY sheet that works fine'
Cells(intBelowRow, 1).Select
ActiveCell.EntireRow.Select
Selection.Copy
Cells(intBelowRow, 1).Select
Selection.EntireRow.Insert
Cells(intBelowRow, 2).Offset(1, 0).Select
Selection.ClearContents
Range("CopyCells").Copy
Cells(intBelowRow, 4).Offset(1, 0).Select
ActiveSheet.Paste

Application.CutCopyMode = False

For RowIns = 2 To intNumberOfRows
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Copy
Selection.EntireRow.Insert
Application.CutCopyMode = False

Next RowIns

'Section for PUMP SUMMARY'
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets(Array("CONCRETE SUMMARY", "POUR ENTRY", "PUMP SUMMARY", "PUMP ENTRY", "PRICING")).Select
Sheets("PUMP SUMMARY").Activate

Cells(intBelowRow, 1).Select
ActiveCell.EntireRow.Select
Selection.Copy
Cells(intBelowRow, 1).Select
Selection.EntireRow.Insert
Range("CopyCells2").Copy
Cells(intBelowRow, 2).Offset(1, 0).Select
ActiveSheet.Paste

For RowIns = 2 To intNumberOfRows
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Copy
Selection.Offset(1, 0).EntireRow.Insert
Application.CutCopyMode = False

Next RowIns

Application.CutCopyMode = False


AddAreaForm.BelowRow.Value = ""
AddAreaForm.NumberOfRows.Value = ""
AddAreaForm.Hide

End If


End Sub


Thank you for any help!

 

[SkipVought]

hi,

I see several things about your workbook that give me pause.

Using the INSERT method to add data is fraught with pitfalls. I almost never use this method. Rather I add below the table and SORT into the proper order.

Using this, =IF('CONCRETE SUMMARY'!B9="","",'CONCRETE SUMMARY'!B9) ASSUMES an order and correlation between the cirrent sheet and some other sheet and is NOT a best and accepted pratice with spreadsheets.

Question is, WHAT is it that you're trying to do. Please don't tell me HOW you think you ought to do it.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Just out of curiosity, are you prefilling a table with formulas in anticipation of data that will be entered at some later time in some other sheet?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Boots6]

I am doing this because it's what my boss wants - he set up the formulas and data validation. The workbook is to track Concrete and Pump on a project. The information will only be input when a project first starts. There can be up to 30 Areas and both Pump and Concrete sheets are identical as far as how many Areas will be in each. The problem is, he has Data Validation in each of the Areas so if I add it at the bottom and sort, one it won't keep the format he wants, two the data validation won't work. Area 1 could have anywhere from 1 to 10 rows. Area 2 below it could have anywhere from 1 to 10 rows. If Area 1 is made of 10 rows, those 10 rows under column B need to be titled "a1list" so that formulas on different tabs work properly. Insert allows you to choose the number of rows and keep the data validation without manually doing it (the people using it will not manually do it). The number of areas and number of rows in each area will be identical in the Pump Summary and Concrete Summary sheets.

He wants me to make it perfect and "idiot proof" for the people that will be using it. I think it should be in a database, but he wants it done immediately and said not to put it in one if it was going to take too long. I would attach images of the layout if I could in this forum, I think it would help. I appreciate any input...

Thanks,

Boots

 

[SkipVought]

If a table is properly structured, it can be sorted without damaging ANY data validation or other formatting.

In the event that a table contains multiple formatting in any column that prevents a table sort, that table has not been properly designed and implemented, resulting in ongoing maintenenance issues, like the one you are facing.

However, I'll check out your code to see if I can make a more helpful suggestion.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

here's a side by side of your concrete & pump code...

[b]
cement                                     pump[/b]
Cells(intBelowRow, 1).Select               Cells(intBelowRow, 1).Select
ActiveCell.EntireRow.Select                ActiveCell.EntireRow.Select
Selection.Copy                             Selection.Copy
Cells(intBelowRow, 1).Select               Cells(intBelowRow, 1).Select
Selection.EntireRow.Insert                 Selection.EntireRow.Insert
Cells(intBelowRow, 2).Offset(1, 0).Select  Range("CopyCells2").Copy
Selection.ClearContents                    Cells(intBelowRow, 2).Offset(1, 0).Select
Range("CopyCells").Copy                    ActiveSheet.Paste
Cells(intBelowRow, 4).Offset(1, 0).Select	
ActiveSheet.Paste

do you see the problem?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Boots6]

I know that the ClearContents Part is not there. I did that on purpose - it doesn't matter either way. There is still a skip the formula if I add it back in. I've tried every combination of Offset I could think of to fix the problem too. Even if you make the 2 identical, the Pump Sheet does this after the procedure:

Cell B9's formula = =IF('CONCRETE SUMMARY'!B9="","",'CONCRETE SUMMARY'!B9)
Cell B10's formula = =IF('CONCRETE SUMMARY'!B11="","",'CONCRETE SUMMARY'!B11)
Cell B11's formula = =IF('CONCRETE SUMMARY'!B12="","",'CONCRETE SUMMARY'!B12)
Cell B12's formula = =IF('CONCRETE SUMMARY'!B13="","",'CONCRETE SUMMARY'!B13)

I can't get that skip out. I'm going to put this into a database as soon as I'm not on a time crunch, but if you think of anything that would quickly easily correct it, I would greatly appreciate it.

Thanks

 

[SkipVought]

What is the difference between CopyCells and CopyCells2?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Boots6]

They are 2 different sections of cells I have set aside to copy and paste into the new rows. That part works fine.

 

[SkipVought]

Can't you COPY B9 and PASTE into B10 and following?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Boots6]

Yes, I have to figure in the NumberOfRows and then copy down each row that was added. I just hoped there was an easier way - I don't understand why it works on the Concrete Sheet and not on the Pump Sheet.

Thanks for your help.

 

[SkipVought]

its awfully convoluted design!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]