Skip to main content

Conas bileoga nua a chruthú do gach ró in Excel?

Ag cur san áireamh go bhfuil tábla scórála agat le hainm an dalta ar fad i gcolún A. Anois, ba mhaith leat bileoga nua a chruthú bunaithe ar na hainmneacha seo i gcolún A, agus tá sonraí ar leith an mhic léinn i ndéanamh in aghaidh na bileoige. Nó cruthaigh bileog nua do gach ró amháin sa tábla gan na hainmneacha i gcolún A a mheas. Sa vedio seo, gheobhaidh tú modhanna chun é a bhaint amach.

Cruthaigh bileoga nua do gach ró le cód VBA
Cruthaigh bileoga nua do gach ró le fóntais Sonraí Scoilt Kutools le haghaidh Excel


Cruthaigh bileoga nua do gach ró le cód VBA

Leis na cóid seo a leanas, is féidir leat bileog nua a chruthú bunaithe ar luachanna na gcolún, nó díreach bileoga nua a chruthú do gach ró in Excel.

1. Brúigh Eile + F11 eochracha ag an am céanna chun an Microsoft Visual Basic d’Fheidhmchláir fhuinneog.

2. Sa Microsoft Visual Basic d’Fheidhmchláir fuinneog, cliceáil Ionsáigh > Modúil. Agus ansin greamaigh an cód seo a leanas isteach sa Modúil fhuinneog.

Cód VBA: cruthaigh bileog nua do gach ró bunaithe ar cholún

Sub parse_data()
'Update by Extendoffice 2018/3/2
    Dim xRCount As Long
    Dim xSht As Worksheet
    Dim xNSht As Worksheet
    Dim I As Long
    Dim xTRrow As Integer
    Dim xCol As New Collection
    Dim xTitle As String
    Dim xSUpdate As Boolean
    Set xSht = ActiveSheet
    On Error Resume Next
    xRCount = xSht.Cells(xSht.Rows.Count, 1).End(xlUp).Row
    xTitle = "A1:C1"
    xTRrow = xSht.Range(xTitle).Cells(1).Row
    For I = 2 To xRCount
        Call xCol.Add(xSht.Cells(I, 1).Text, xSht.Cells(I, 1).Text)
    Next
    xSUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    For I = 1 To xCol.Count
        Call xSht.Range(xTitle).AutoFilter(1, CStr(xCol.Item(I)))
        Set xNSht = Nothing
        Set xNSht = Worksheets(CStr(xCol.Item(I)))
        If xNSht Is Nothing Then
            Set xNSht = Worksheets.Add(, Sheets(Sheets.Count))
            xNSht.Name = CStr(xCol.Item(I))
        Else
            xNSht.Move , Sheets(Sheets.Count)
        End If
        xSht.Range("A" & xTRrow & ":A" & xRCount).EntireRow.Copy xNSht.Range("A1")
        xNSht.Columns.AutoFit
    Next
    xSht.AutoFilterMode = False
    xSht.Activate
    Application.ScreenUpdating = xSUpdate
End Sub

nótaí: A1: Is é C1 raon teidil do thábla. Is féidir leat é a athrú bunaithe ar do chuid riachtanas.

3. Brúigh F5 eochair chun an cód a rith, ansin cruthaítear bileoga oibre nua tar éis gach bileog oibre den leabhar oibre reatha mar atá thíos ar an scáileán:

Más mian leat bileoga nua a chruthú go díreach do gach ró gan smaoineamh ar luach an cholúin, is féidir leat an cód seo a leanas a úsáid.

Cód VBA: Cruthaigh bileog nua go díreach do gach ró

Sub RowToSheet()
	Dim xRow As Long
	Dim I As Long
	With ActiveSheet
		xRow = .Range("A" & Rows.Count).End(xlUp).Row
		For I = 1 To xRow
			Worksheets.Add(, Sheets(Sheets.Count)).Name = "Row " & I
			.Rows(I).Copy Sheets("Row " & I).Range("A1")
		Next I
	End With
End Sub

Tar éis an cód a rith, cuirfear gach ró i mbileog oibre gníomhach i mbileog oibre nua.

nótaí: Cuirfear an tsraith cheannteidil i mbileog nua leis an gcód VBA seo.


Cruthaigh bileoga nua do gach ró le fóntais Sonraí Scoilt Kutools le haghaidh Excel

I ndáiríre, tá an modh thuas casta agus deacair a thuiscint. Sa chuid seo, tugaimid an Scoilt Sonraí fóntais de Kutools le haghaidh Excel.

Roimh iarratas a dhéanamh Kutools le haghaidh Excel, Le do thoil é a íoslódáil agus a shuiteáil ar dtús.

1. Roghnaigh an tábla a chaithfidh tú a úsáid chun bileoga nua a chruthú, agus ansin cliceáil Kutools Plus> Sonraí Spit. Féach an pictiúr:

2. Sa Roinn Sonraí i mBileoga Oibre Il bosca dialóige, déan mar a leanas, le do thoil.

A. Chun bileoga nua a chruthú bunaithe ar luach an cholúin:

1). Roghnaigh an Colún sonrach rogha, agus sonraigh colún ar mhaith leat sonraí a roinnt air bunaithe ar an liosta anuas;
2). Más mian leat na bileoga oibre a ainmniú le luachanna colúin, roghnaigh le do thoil Luachanna an Cholúin sa Rialacha liosta anuas;
3). Cliceáil ar an OK cnaipe. Féach an pictiúr:

B. Chun bileoga nua a chruthú go díreach do gach ró:

1). Roghnaigh Sraitheanna seasta rogha, iontráil uimhir 1 isteach sa bhosca;
2). Roghnaigh Uimhreacha Rae ó na Rialacha liosta anuas;
3). Cliceáil ar an OK cnaipe. Féach an pictiúr:

cruthaítear leabhar oibre nua leis na bileoga nua go léir istigh ann. Féach na scáileáin scáileáin thíos.

Bileoga nua a chruthú do gach ró bunaithe ar luach an cholúin:

Bileog nua a chruthú do gach ró gan smaoineamh ar luach an cholúin:

  Más mian leat triail saor in aisce (30 lá) a bheith agat ar an bhfóntas seo, cliceáil le do thoil chun é a íoslódáil, agus ansin téigh chun an oibríocht a chur i bhfeidhm de réir na gcéimeanna thuas.

Cruthaigh bileoga nua do gach ró le fóntais Sonraí Scoilt Kutools le haghaidh Excel

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 (33)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
HI, Thanks for this wonder-full code, Can we get dynamic sheet, means if i update data in respective sheet it will get updated in main sheet.
This comment was minimized by the moderator on the site
Hi vikas chandra,
I can't fix this problem. Sorry about that.
This comment was minimized by the moderator on the site
Hi, I have a problem about title, the title range of my table is A1:AI2, when I changed the code like that it doesn't work.

***Note: A1:C1 is the title range of your table. You can change it based on your needs.***
This comment was minimized by the moderator on the site
Hello, thanks so much for this. I'm looking to modify the macro such that it will create a sheet for each row of a column and within each sheet have a function (average) that I can populate data into and in turn have the outcome linked back into the original sheet. Is this possible? I can try to clarify further if this doesn't make sense or is ambiguous.
This comment was minimized by the moderator on the site
Hi, is there a code which would add only 1 new sheet each time the macro is run, eg 1st time the new sheet would be named on the contents of cell A1, 2nd time the macro was run the new sheet would be named on the contents of A2 etc. thanks in anticipation
This comment was minimized by the moderator on the site
Hello, used this code and worked, but If I want select the more then one rows in header, what will be change in the code ? I have multiple lines in the sheet which I want in every sheet.
This comment was minimized by the moderator on the site
Hello, did you figured out how?
This comment was minimized by the moderator on the site
Hello! I just used this code and it worked! In addition to creating a new sheet for each entry, I want to transpose it to columns and can't figure it out. So for the above example, the output for Nana would look like this - Name NanaScore 86No. 2
This comment was minimized by the moderator on the site
<p> Nana
86
2</p>
This comment was minimized by the moderator on the site
How to reference the use of the code above (credit) ? Is it possible to modify the code ?
This comment was minimized by the moderator on the site
Hi, this is an open communication platform. The code is allowed to reference and modify.
This comment was minimized by the moderator on the site
Nevermind it was hidden trailing spaces. I used the TRIM feature and cleaned it up. Having a row count (line count really so rows -1 prepended to the sheet would be amazing)
This comment was minimized by the moderator on the site
Please can i get help on how to automatically name the sheets using a particular column. This is for the row to sheet VBA. See below

Sub RowToSheet()

Dim xRow As Long

Dim I As Long

With ActiveSheet

xRow = .Range("A" & Rows.Count).End(xlUp).Row

For I = 1 To xRow

Worksheets.Add(, Sheets(Sheets.Count)).Name = "Row " & I

.Rows(I).Copy Sheets("Row " & I).Range("A1")

Next I

End With

End Sub
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations