Activating a number of cells based on single number

[Tanya7106]

Hello
I am trying to create a pricing document and need some help.
Basically i want to be able to put a number in a header sheet cell which then populates that number of lines on the next page. Not only that I want the first column on the next page to have one fixed word ("Plot") followed by incremental numbers up to the value in the header sheet cell.
Apologies if I have not described it correctly, but I'm sure you clever souls will get the gist of it.

TIA

 

[Sufiyan97]

Hi & Welcome to MrExcel.

Can you post the data along with some manually typed expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Bosquedeguate]

Here is some very basic code. It makes the following assumptions:

1. you always output to a know sheet (or should it create a new sheet)?
2. what if the target sheet has data?

This code covers the bare basics where in my case Sheet5 received the new values in Column 1.

Private Sub Worksheet_Change(ByVal Target As Range)
  With Target
    If .Row() = 1 And .Column() = 2 Then
      CreateNewRows "Sheet5", .Value
    End If
  End With
End Sub


Sub CreateNewRows(sheet As String, rows As Long)
  Dim i As Long
  Dim wks As Worksheet
  Set wks = Worksheets(sheet)
  For i = 1 To rows
    wks.Cells(i, 1) = "Plot" & i
  Next i
End Sub