Copy & Insert Multiple rows on a different worksheet (with defined formulas & conditional formatting) based on a cell value

Donna216

New Member
Joined
Aug 20, 2019
Messages
3
I'm very new to VBA so would appreciate some assistance with the following:
Cell located on 'Summary' sheet B2 - displays the value (e.g. 350) which is the number of rows I need inserted on to 2 different Worksheets 'Testing Records' & 'Raw Data'. But ......'Testing Records' worksheet contains conditional formatting & 'Raw Data' contains formulas which need to be included in the new rows. How can I do this? Should I copy the 1st row on each worksheet & then insert them the required number of times as based on the cell on 'Summary' sheet??? Any assistance would be greatly appreciated....
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

What we do is to have the top row on the destination sheets hidden but with all the formula, conditional formatting etc in there. Then use this row to copy.

Code:
Sub ADD_NUMBER_OF_ROWS()
    MY_NEW_ROWS = Sheets("Summary").Range("B2").Value
    With Sheets("Testing Records")
        .Rows("1:1").Copy
        .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).Resize(MY_NEW_ROWS).PasteSpecial (xlPasteAll)
        .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).Resize(MY_NEW_ROWS).EntireRow.Hidden = False
    End With
    With Sheets("Raw Data")
        .Rows("1:1").Copy
        .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).Resize(MY_NEW_ROWS).PasteSpecial (xlPasteAll)
        .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).Resize(MY_NEW_ROWS).EntireRow.Hidden = False
    End With
End Sub

This code can be made simpler if you only have the three tabs in the workbook. Is this at all close to your requirement?
 

Forum statistics

Threads
1,137,154
Messages
5,679,911
Members
419,862
Latest member
Bluewings666

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top