Skip to main content

Conas iliomad bileoga oibre a chruthú ó liosta luachanna cille?

An bhfuil aon mhodhanna tapa nó éasca ann chun iliomad bileoga oibre a chruthú bunaithe ar liosta luachanna cille in Excel? San alt seo, labhróidh mé faoi roinnt cleasanna maithe chun an post seo a réiteach.

Cruthaigh iliomad bileoga oibre ó liosta luachanna cille le cód VBA

Cruthaigh iliomad bileoga oibre ó liosta luachanna cille le Kutools for Excel

mboilgeog cheart gorm saighead Cruthaigh iliomad bileoga oibre ó liosta luachanna cille le cód VBA

Chun iliomad bileoga oibre nua a chruthú go tapa a ainmníonn liosta de luachanna cille, is féidir leis an gcód VBA seo a leanas cabhrú leat.

1. Coinnigh síos an ALT + F11 eochracha a oscailt Microsoft Visual Basic d’Fheidhmchláir fhuinneog.

2. Cliceáil Ionsáigh > Modúil, agus greamaigh an cód seo a leanas sa Modúil Fuinneog.

Cód VBA: Cruthaigh iliomad bileoga oibre ó liosta cealla:

Sub AddSheets()
'Updateby Extendoffice
    Dim xRg As Excel.Range
    Dim wSh As Excel.Worksheet
    Dim wBk As Excel.Workbook
    Set wSh = ActiveSheet
    Set wBk = ActiveWorkbook
    Application.ScreenUpdating = False
    For Each xRg In wSh.Range("A1:A7")
        With wBk
            .Sheets.Add after:=.Sheets(.Sheets.Count)
            On Error Resume Next
            ActiveSheet.Name = xRg.Value
            If Err.Number = 1004 Then
              Debug.Print xRg.Value & " already used as a sheet name"
            End If
            On Error GoTo 0
        End With
    Next xRg
    Application.ScreenUpdating = True
End Sub

nótaí: Sa chód thuas, A1: A7 an bhfuil an raon cille ar mhaith leat bileoga a chruthú bunaithe air, athraigh é de réir do riachtanas.

3. Ansin brúigh F5 eochair chun an cód seo a rith, agus cruthaíodh na bileoga nua tar éis gach bileog sa leabhar oibre reatha, féach an scáileán:

cruthaigh doc bileoga iolracha 1

mboilgeog cheart gorm saighead Cruthaigh iliomad bileoga oibre ó liosta luachanna cille le Kutools for Excel

Mura bhfuil tú oilte leis an gcód thuas, seo uirlis áisiúil-Kutools le haghaidh Excel, Lena Cruthaigh Bileoga Oibre Seicheamh feidhm, cruthófar na bileoga oibre bunaithe ar na luachanna cille i leabhar oibre nua.

Kutools le haghaidh Excel : le níos mó ná 300 breiseán áisiúil Excel, saor in aisce le triail gan aon teorannú i 30 lá. 
Téigh go dtí Íoslódáil
Triail In Aisce 30 láCeannach
PayPal / MyCommerce

Tar éis a shuiteáil Kutools le haghaidh Excel, déan mar seo le do thoil:

1. Cliceáil Kutools Plus > Bileog Oibre > Cruthaigh Bileoga Oibre Seicheamh, féach ar an scáileán:

2. Sa an Cruthaigh Bileoga Oibre Seicheamh bosca dialóige:

(1.) Roghnaigh bileog oibre amháin ar mhaith leat bileoga oibre seicheamh a chruthú bunaithe uirthi;

(2.) Ansin roghnaigh Dáta i rogha raon ón Ainmneacha bileog bunaithe ar bosca liosta, agus cliceáil cruthaigh doc bileoga iolracha 4 cnaipe chun na luachanna cille is mian leat a úsáid a roghnú.

cruthaigh doc bileoga iolracha 3

3. Agus ansin cliceáil Ok cnaipe, cruthaíodh na bileoga oibre le hainmneacha na luachanna cille i leabhar oibre nua, féach an scáileán:

cruthaigh doc bileoga iolracha 5

Cliceáil Íoslódáil agus triail saor in aisce Kutools le haghaidh Excel Now!

Uirlisí Táirgiúlachta Oifige is Fearr

🤖 Kutools AI Aide: anailís sonraí a réabhlóidiú bunaithe ar: Forghníomhú Chliste   |  Gin Cód  |  Cruthaigh Foirmlí Saincheaptha  |  Anailís a dhéanamh ar Sonraí agus Cairteacha a Ghin  |  Feidhmeanna Kutools a agairt...
Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup  |   VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle   |  Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla   |  Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí:  12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

Tuairisc

Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
Comments (20)
No ratings yet. Be the first to rate!
Fabian
Fabian
This comment was minimized by the moderator on the site
Hi
I would like to copy my "Vorlage" spreadsheet as many times as my "Stände" spreadsheet specifies. At the same time, the new sheets are also to be named according to a list from the "Stände" spreadsheet (item A1:A85).
Thank you in advance!
Reply
0
0
Niks145
Niks145
This comment was minimized by the moderator on the site
hello skyyang
i have try this code but it is create blank sheet
i want copy of active sheets
any idea....
Reply
Report
0
0
Lucas
Lucas
This comment was minimized by the moderator on the site
Et si la liste est mouvante? car si j'ajoute des éléments dois-je tout le temps réadapter le code?
Merci
Reply
0
0
skyyang
skyyang     Lucas
This comment was minimized by the moderator on the site
Hello, Lucas
To solve your problem, please apply the below code:
Please right click the sheet tab, and select View Code, then copy and paste the code into the Sheet Code window.
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRg As Excel.Range
    Dim wSh As Excel.Worksheet
    Dim wBk As Excel.Workbook
    Dim xAddress As String
    Dim xWSH As Worksheet
    Dim xRgI As Range
    Set wSh = ActiveSheet
    Set wBk = ActiveWorkbook
    xAddress = "A2:A20"
    On Error Resume Next
    Set xRgI = Intersect(Range(xAddress), Target)
    If xRgI Is Nothing Then Exit Sub
    If Target.Value = "" Then Exit Sub
    Application.ScreenUpdating = False
    Set xWSH = wBk.Worksheets.Item(Target.Value)
    If xWSH Is Nothing Then
      Set xWSH = wBk.Worksheets.Add
        xWSH.Name = Target.Value
        If Err.Number = 1004 Then
            Debug.Print xRg.Value & " already used as a sheet name"
        End If
    End If
    wSh.Activate
    Application.ScreenUpdating = True
End Sub

https://www.extendoffice.com/images/stories/comments/comment-skyyang/2023-comment/doc-sheets-from-cells.png
After pasting the code, now, you can enter the content into the specified cells, and then press Enter key, the new sheet will be created automatically.
Please have a try, hope it can help you!
Reply
Report
0
0
Niks145
Niks145     skyyang
This comment was minimized by the moderator on the site
Thanks you for posting this.
but i have problem with this code it is add blank sheets i want to copy and add the sheets
any idea for this??
Reply
Report
0
0
skyyang
skyyang     Niks145
This comment was minimized by the moderator on the site
Hello, Niks,

To solve your problem, please apply the below code:
Please right click the sheet tab, and select View Code, then copy and paste the code into the Sheet Code window.
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRg As Range
    Dim wSh As Worksheet
    Dim wBk As Workbook
    Dim xAddress As String
    Dim xWSH As Worksheet
    Dim xRgI As Range
    
    Set wSh = ActiveSheet
    Set wBk = ActiveWorkbook
    xAddress = "A2:A20"
    
    On Error Resume Next
    Set xRgI = Intersect(Range(xAddress), Target)
    On Error GoTo 0
    
    If xRgI Is Nothing Then Exit Sub
    If Target.Value = "" Then Exit Sub
    
    Application.ScreenUpdating = False
    
    Set xWSH = Nothing
    On Error Resume Next
    Set xWSH = wBk.Worksheets(Target.Value)
    On Error GoTo 0
    
    If xWSH Is Nothing Then
        On Error Resume Next
        Set xWSH = wBk.Worksheets.Add(After:=wBk.Worksheets(wBk.Worksheets.Count))
        On Error GoTo 0
        
        If Not xWSH Is Nothing Then
            xWSH.Name = Target.Value
            wSh.Cells.Copy Destination:=xWSH.Cells(1, 1)
        End If
    End If
    
    wSh.Activate
    Application.ScreenUpdating = True
End Sub

After pasting the code, when a value is entered in the specified range, a new worksheet is created based on that value, and the entire content of the current worksheet is copied to the newly created worksheet.

Please try, hope it can help you!
Reply
Report
0
0
Guest User
Guest User
This comment was minimized by the moderator on the site
Thank you for posting this. I followed the directions and it worked perfectly.
Reply
0
0
WK
WK
This comment was minimized by the moderator on the site
I tried using the VBA code, it is creating "nameless" worksheets, so sheet1 , 2 , 3 and so on, rather than using the value in the cell as the sheet's name. I tried to fixed by changing the data type in the cell to text , same issue…


any ideas?
Reply
0
0
Theresa
Theresa     WK
This comment was minimized by the moderator on the site
I had this issue. to correct: 1. only 31 characters allowed for worksheet names2. no special characters + = ( ) [ ] \ / , : etc...find and replace with a space
Reply
0
0
Smitha
Smitha
This comment was minimized by the moderator on the site
This is of great help. I could save so much time. Thank you so much for your time and for helping us with your wonderful code.
Reply
0
0
Craig
Craig
This comment was minimized by the moderator on the site
This works great, how could you incorporate a template into each created tab? i.e. copy and paste from a template into each newly created sheet
Reply
0
0
Nathan
Nathan
This comment was minimized by the moderator on the site
First time using VBA code in Excel. Worked perfectly on the first try. Thanks for posting this.
Reply
0
0
amin
amin
This comment was minimized by the moderator on the site
and it creates a lot of sheets even if the list is empty... what if i want to create sheets based on cells that have value?
Reply
0
0
GUEST
GUEST
This comment was minimized by the moderator on the site
Better version. This will delete created sheet if exist another sheet with the same name. And added inputbox to avoid from manual code modification to select range.


Sub AddSheetsFromCells()

Dim xRg As Range, wBk As Workbook
Set wBk = ActiveWorkbook

On Error GoTo Quit
Set dbRange = Application.InputBox("Range: ", "Select Range", _
Application.Selection.Address, Type:=8)

Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each xRg In dbRange
With wBk
.Sheets.Add After:=.Sheets(.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = xRg.Value
If Err.Number = 1004 Then
Debug.Print Chr(34) & xRg.Value & Chr(34) & " already used as a sheet name"
.ActiveSheet.Delete
End If
On Error GoTo 0
End With
Next xRg

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Quit:

End Sub
Reply
0
0
Josh
Josh     GUEST
This comment was minimized by the moderator on the site
this is awesome...... thank-you very much .is there somewhere where there is a public repository for vba codes?
Reply
0
0
blackcatcivilcraigd@gmail.com
    GUEST
This comment was minimized by the moderator on the site
What if i wanted each newly created sheet to have a template pasted into it from a template sheet? The template would have formatting and formulas only

Thanks
Reply
Report
0
0
prachi
prachi    
This comment was minimized by the moderator on the site
i also need to know this. did u figure out ?
Reply
0
0
steph
steph     prachi
This comment was minimized by the moderator on the site
Sub UpdateMAPs()
Dim LR As Long, i As Long
Application.ScreenUpdating = False
With Sheets("Team List")
LR = .Range("E" & Rows.Count).End(xlUp).Row
For i = 2 To LR
Sheets("Blank MAP").Copy Before:=Sheets("Blank MAP")
ActiveSheet.Name = .Range("E" & i).Value
Next i
End With
Application.ScreenUpdating = True
End Sub

this worked for me from https://www.mrexcel.com/forum/excel-questions/553308-copy-worksheet-rename-cell-value.html
Reply
0
0
Fred
Fred     steph
This comment was minimized by the moderator on the site
This is amazing! Thank you so much!
Reply
0
0
Richard Pigford
Richard Pigford     GUEST
This comment was minimized by the moderator on the site
This appears to work great for what I am attempting to do with one exception... It is creating blank worksheets... I want to create a copy of an existing worksheet for each row in another worksheet. Is there anyway to do that?
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations