PDA

View Full Version : VBA code Problem



alihadi
08-12-2016, 04:00 AM
Hi everybody,
Does anyone know how to generate numbers in a number of sheets. These sheets are generated based on numbers that can be inserted. I am attaching a VBA code file that generates the sheets, but I do not know how to generate numbers in these sheets. For example: if the generated sheets are four sheets, so I want o generated in the first sheet numbers from 1 to 10, in the second sheet I want to generate numbers from 1 to 20, in the third sheet I want to generate numbers from 1 to 30 and t in the fourth sheet I want to generate numbers from 1 to 40. I will be grateful if you help me. Thanks in advance.

Kenneth Hobs
08-12-2016, 07:00 AM
Sub AddSheets3()
Dim i As Integer, j As Integer, ii() As Integer

'For i = Application.InputBox("Starting number?") To Application.InputBox("Ending number?")
For i = 1 To Application.InputBox("number?")
ReDim ii(1 To i * 10)
For j = 1 To i * 10
ii(j) = j
Next j

Sheets.Add.Name = "ContainerDeparture " & i
With ActiveSheet
.Range("A1").Value = "Numbers"
.Range("A2").Resize(UBound(ii)).Value = WorksheetFunction.Transpose(ii)
End With
Next i
End Sub

Paul_Hossler
08-12-2016, 07:22 AM
Option Explicit
Sub AddSheets3_ph()
Dim NumberOfSheets As Long, SheetNumber As Long, i As Long

NumberOfSheets = Application.InputBox("Number of Sheets, 0 to exit")
If NumberOfSheets < 1 Then Exit Sub

For SheetNumber = 1 To NumberOfSheets
Worksheets.Add(, Worksheets(Worksheets.Count)).Name = "ContainerDeparture " & SheetNumber
ActiveSheet.Cells(1, 1).Value = "Numbers"
For i = 1 To 10 * SheetNumber
ActiveSheet.Cells(i + 1, 1).Value = i
Next I
Next SheetNumber
End Sub