Ide: Lòt lang yo se Google-tradui. Ou ka vizite a English vèsyon nan lyen sa a.
Ouvri yon sesyon
x
or
x
x
enskri
x

or

Ki jan yo fann done nan plizyè fichye pa ranje konte nan Excel?

Pou egzanp, mwen gen yon seri de lis done, kounye a mwen vle fann li nan plizyè fichye pa ranje konte, nan egzanp sa a, mwen pral fann li pa tout ranje 5 (al gade Ekran sa yo). Èske gen nenpòt fason pou ede nou rezoud pwoblèm sa a byen vit? Men mwen pral entwodui de ke trik nouvèl pou ou pou w jwenn li fè fasil.


doc-fann-done-pa-rows1


-2



doc-fann-done-pa-rows2
doc-fann-done-pa-rows3
doc-fann-done-pa-rows4
doc-fann-done-pa-rows5

Done done nan plizyè fich pa ranje konte ak kòd VBA

Done Split nan plizyè fich pa ranje konte ak Kutools pou Excel


flèch ble jarèt dwat Done done nan plizyè fich pa ranje konte ak kòd VBA


Kòd Vba sa a ka ede ou fann ranje yo nan plizyè fèy pa ranje konte, fè jan sa a:

1. Kenbe desann la ALT + F11 kle yo louvri a Microsoft Visual Basic pou aplikasyon pou fenèt.

2. Klike sou Antre > Modil, epi kole kòd sa a nan fenèt modil la.

VBA: Done done nan fèy pa ranje konte nan Excel.

Sub SplitData()
'Updateby20140617
Dim WorkRng As Range
Dim xRow As Range
Dim SplitRow As Integer
Dim xWs As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
SplitRow = Application.InputBox("Split Row Num", xTitleId, 5, Type:=1)
Set xWs = WorkRng.Parent
Set xRow = WorkRng.Rows(1)
Application.ScreenUpdating = False
For i = 1 To WorkRng.Rows.Count Step SplitRow
    resizeCount = SplitRow
    If (WorkRng.Rows.Count - xRow.Row + 1) < SplitRow Then resizeCount = WorkRng.Rows.Count - xRow.Row + 1
    xRow.Resize(resizeCount).Copy
    Application.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count)
    Application.ActiveSheet.Range("A1").PasteSpecial
    Set xRow = xRow.Offset(SplitRow)
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

3. Lè sa a, peze F5 kle nan kouri kòd la, ak yon dyalòg Pops soti pou chwazi yon ranje fann, ak Lè sa a, klike sou OK, ak yon lòt dyalòg pou ou presize konte yo ranje. Al gade nan Ekran:

4. Klike sou OK, ak ranje a yo separe nan fèy papye miltip pa konte a ranje.

nòt: Fèy yo fann yo mete nan do a nan Fèy travay la mèt.


flèch ble jarèt dwat Done Split nan plizyè fich pa ranje konte ak Kutools pou Excel

Kòd ki pi wo a ka sèlman fann done yo pa ranje yo konte espesifye, epi li pa pral ajoute tit la pou chak done Fichye si done ou gen tit la.

men, avèk Kutools pou Excel'S Done Split, ou pa sèlman ka insert Tèt yo pou chak done Fichye, men tou, ka presize non divize fichye yo nouvo.

Kutools pou Excel gen ladan plis pase 300 sou la meni Excel zouti. Gratis eseye pa gen okenn limit nan jou 30. Jwenn li Koulye a,

Si ou te enstale Kutools pou Excel, tanpri fè jan sa a:

1. Chwazi ranje a nan done ke ou vle fann.

2. Klike sou Enterprise > Fèy Worksheet > Done Split (oswa Enterprise > Done Split ), gade D:

doc-fann-done-pa-rows6

3. Nan Done Split nan Fèy Miltip bwat dyalòg:

  • chwazi Fiks ranje soti nan Split ki baze sou opsyon, ak antre nan kantite ranje konte ke ou vle fann pou chak fich. (Si done ou a gen Tèt epi ou vle mete yo nan chak Fèy travay fann nouvo, tanpri tcheke Done mwen gen tit opsyon.)
  • Lè sa a, ou ka presize non Fèy travay yo, anba a Nouvo non fich seksyon, chwazi non yo Fichye non règ ki soti nan la Règleman gout desann lis, ou ka ajoute a Prefiks or sifiks pou non yo fèy kòm byen. Al gade nan Ekran:

doc-fann-done-pa-rows7

4. Apre presize anviwònman yo, Lè sa a, klike sou OK, ak done yo chwazi yo te divize an fichye miltip nan yon nouvo workbook pa chak ranje 5. Ak tèt yo yo se eleman pou chak Fèy travay nouvo. Al gade nan Ekran:

doc-fann-done-pa-rows8

Klike sou yo konnen plis sou sa a Done Split done.

Atik ki gen rapò:

Ki jan yo fann done nan plizyè fich ki baze sou kolòn nan Excel?



Rekòmande Zouti Productivity

Biwo Tab

lò star1 Pote onglè sou lamen pou Excel ak lòt biwo lojisyèl, jis tankou Chrome, Firefox ak nouvo Internet Explorer.

Kutools pou Excel

lò star1 Etonan! Ogmante pwodiktivite ou nan minit 5. Pa bezwen okenn ladrès espesyal, sove de zè de tan chak jou!

lò star1 300 karakteristik nouvo pou Excel, fè Excel anpil fasil ak pwisan:

  • Rantre selilè / ranje / Kolòn san yo pa pèdi done.
  • Konbine ak Konsolide Pliye Plizyè ak Manyèl.
  • Konpare Range, Kopi plizyè chif, Konvèti tèks nan dat, inite ak konvèsyon lajan.
  • Konte pa Koulè, Paging subtotals, Avanse Triye ak Super Filter,
  • Plis Chwazi / Insert / Efase / Tèks / Fòma / Link / Kòmantè / Workbooks / Worksheets Zouti ...

Piki ekran nan Kutools pou Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Max · 10 months ago
    Omg thank you so very much dude!
  • To post as a guest, your comment is unpublished.
    prasanna · 1 years ago
    Hi Thanks for this great code, but the concern is, here we can only split fixed number of rows, can you help me with split is based on some name or code, because i have different set of rows for each worksheet.
  • To post as a guest, your comment is unpublished.
    aparna · 2 years ago
    hi this works great but want the output as string how can i modify the code or i can say example : 'output',
  • To post as a guest, your comment is unpublished.
    Ralph · 2 years ago
    Thanks a lot for the fantastic VBA-Script. But how to modify it to keep the header row on every split?
  • To post as a guest, your comment is unpublished.
    Ralph · 2 years ago
    Thanks, for this fantastic VBA-Script. But how to configure it to repeat the header row on every split?