Number of rows to insert - Macro

[sveioen]

Hello,

Hope someone can help me with this little one;

I want to insert a number of rows. The number I get from subtracting B1 in the current sheet, with A1 in "Sheet1". I then insert as many rows as the number says. How can I do this using a macro?

 

[Lewiy]

Something like this perhaps:

Sub Macro1()
Dim sh1 As Worksheet
Dim sh As Worksheet
Dim NoOfLines As Long
Set sh1 = Sheets("Sheet1")
Set sh = ActiveSheet
NoOfLines = sh.Range("B1") - sh1.Range("A1")
sh.Rows("1:" & NoOfLines).Insert shift:=xlDown
End Sub

 

[sveioen]

Thanks Lewiy!

I tried your code and it works, but I get an error at the end:

Dim sh1 As Worksheet
Dim sh As Worksheet
Dim NoOfLines As Integer

Set sh1 = Sheets("Information")
' Set sh3 = Sheets("Sheet3")
Set sh = Sheets("Sheet6")
NoOfLines = sh.Range("B30") - sh1.Range("N1")
sh.Rows("1:" & NoOfLines).Insert shift:=xlDown

Error:

Run-time error '6':
Overflow

 

[Lewiy]

Which line does the error occur on?

 

[sveioen]

This one:

NoOfLines = sh.Range("B30") - sh1.Range("N1")

(Changed the range-values, but this is irrelevant I think)