david2005wang
New Member
- Joined
- Apr 8, 2022
- Messages
- 13
- Office Version
- 2021
- 2019
- 2013
- Platform
- Windows
Dear Gentleman,
I have one overal document register sheet (hse)-pic1, with sampe of three different registers of document reference number in three row from 1 to 3. (later the row number may increase to 100rows)
First, I wanna to use VBA (macro) to creat one command button in the sheet(HSE) the first pic to create one or more different newsheets with the sheetname as my selection the cell in F column such as SWM, and
then, fill the first row of the new create sheet (SWM) with the same contents and format in another sheet (model) pic2 from column A to column H; same kind of copy the first row from column A to column H with format in the sheet(model) to the the first row of sheet (SWM) .
last, copy the row with the same contents and format in the select row of the cell (SWM) from column A to column H in overall document register sheet (HSE) to the second row of new create sheet (SWM) from column A to column H as the final pic3;
I have one code example for creat the new sheet with the name in the selection cell (SWM) in the sheet (HSE), so how can I auto fill or copy the reqired contents into the new sheet, pls
help me!!!, thanking you so much.
david wang
Private Sub CommandButton2_Click()
'Name macro
'Sub CreateSheets()
'Dimension variables and declare data types
Dim rng As Range
Dim cell As Range
'Enable error handling
On Error GoTo Errorhandling
'Show inputbox to user and prompt for a cell range
Set rng = Application.InputBox(Prompt:="Select cell range:", _
Title:="Create sheets", _
Default:=Selection.Address, Type:=8)
'Iterate through cells in selected cell range
For Each cell In rng
'Check if cell is not empty
If cell <> "" Then
'Insert worksheet and name the worksheet based on cell value
Sheets.Add.Name = cell
End If
'Continue with next cell in cell range
Next cell
'Go here if an error occurs
Errorhandling:
'Stop macro
End Sub
I have one overal document register sheet (hse)-pic1, with sampe of three different registers of document reference number in three row from 1 to 3. (later the row number may increase to 100rows)
First, I wanna to use VBA (macro) to creat one command button in the sheet(HSE) the first pic to create one or more different newsheets with the sheetname as my selection the cell in F column such as SWM, and
then, fill the first row of the new create sheet (SWM) with the same contents and format in another sheet (model) pic2 from column A to column H; same kind of copy the first row from column A to column H with format in the sheet(model) to the the first row of sheet (SWM) .
last, copy the row with the same contents and format in the select row of the cell (SWM) from column A to column H in overall document register sheet (HSE) to the second row of new create sheet (SWM) from column A to column H as the final pic3;
I have one code example for creat the new sheet with the name in the selection cell (SWM) in the sheet (HSE), so how can I auto fill or copy the reqired contents into the new sheet, pls
help me!!!, thanking you so much.
david wang
Private Sub CommandButton2_Click()
'Name macro
'Sub CreateSheets()
'Dimension variables and declare data types
Dim rng As Range
Dim cell As Range
'Enable error handling
On Error GoTo Errorhandling
'Show inputbox to user and prompt for a cell range
Set rng = Application.InputBox(Prompt:="Select cell range:", _
Title:="Create sheets", _
Default:=Selection.Address, Type:=8)
'Iterate through cells in selected cell range
For Each cell In rng
'Check if cell is not empty
If cell <> "" Then
'Insert worksheet and name the worksheet based on cell value
Sheets.Add.Name = cell
End If
'Continue with next cell in cell range
Next cell
'Go here if an error occurs
Errorhandling:
'Stop macro
End Sub