Ini Fantastik. Terima kasih banyak. Saya telah menjalankan tes cepat untuk ini dan tampaknya melakukan apa yang saya cari dalam sekejap mata. Apakah ada cara untuk menyimpan ini sehingga setiap kali saya membuka excel saya tidak perlu menyalin/menempel ke modul? . Saya tidak pernah tahu excel sekuat inikata MoshiM
Jadikan lembar kerja dengan data sebagai lembar aktif dan jalankan ini. Ini harus disimpan ke jalur yang sama dengan csv
Kode VBA
Sub flyguy[] Dim ACS As Range, Z As Long, New_WB As Workbook, _ Total_Columns As Long, Start_Row As Long, Stop_Row As Long, Copied_Range As Range Dim Headers[] As Variant Set ACS = ActiveSheet.UsedRange With ACS Headers = .Rows[1].Value Total_Columns = .Columns.Count End With Start_Row = 2 Do While Stop_Row 1 Then Start_Row = Stop_Row + 1 Stop_Row = Start_Row + 499 With ACS.Rows If Stop_Row > .Count Then Stop_Row = .Count End With With ACS Set Copied_Range = .Range[.Cells[Start_Row, 1], .Cells[Stop_Row, Total_Columns]] End With Set New_WB = Workbooks.Add With New_WB With .Worksheets[1] .Cells[1, 1].Resize[1, Total_Columns] = Headers .Cells[2, 1].Resize[Copied_Range.Rows.Count, Total_Columns] = Copied_Range.Value End With .SaveAs ACS.Parent.Parent.Path & Application.PathSeparator & "file-" & Z & ".xls", FileFormat:=-4143 .Close End With If Stop_Row = ACS.Rows.Count Then Exit Do Loop End Sub
Klik untuk meluaskan
kata flyguy
Menempatkannya di sana berfungsi seperti yang diharapkan, terima kasih
Satu pertanyaan tambahan. Setelah saya menjalankan makro, pertama kali file dibuat seperti yang diharapkan, File-1, File-2, File-3 dll. Jika saya mendapatkan file lain dan mencoba menjalankan makro, itu tidak membuat file baru dengan menambah nama file seperti File-4, File-5 katanya --Nama file sudah ada di lokasi ini yang ingin Anda timpa
Apakah ada solusi untuk ini?
Jika saya menekan no Ketika saya men-debug, di bawah ini disorot
Simpan Sebagai ACS. Induk. Induk. Jalur & Aplikasi. PathSeparator & "file-" & Z & ". xls", FileFormat. =-4143Klik untuk meluaskan
Kode VBA
Sub flyguy[]
Dim ACS As Range, Z As Long, New_WB As Workbook, B As Long, _
Total_Columns As Long, Start_Row As Long, Stop_Row As Long, Copied_Range As Range, File_Name As String
Dim Headers[] As Variant
Set ACS = ActiveSheet.UsedRange
With ACS
Headers = .Rows[1].Value
Total_Columns = .Columns.Count
End With
File_Name = Dir[ThisWorkbook.Path & Application.PathSeparator & "*.xls"]
On Error GoTo Next_File
Do While Len[File_Name] > 0
If File_Name Like "*file-*" Then
B = CLng[Split[Split[File_Name, "file-"][1], ".xls"][0]]
If B > Z Then Z = B
End If
Next_File: On Error GoTo -1
File_Name = Dir
Loop
On Error GoTo 0
Start_Row = 2
Do While Stop_Row 1 Then Start_Row = Stop_Row + 1
Stop_Row = Start_Row + 499
With ACS.Rows
If Stop_Row > .Count Then Stop_Row = .Count
End With
With ACS
Set Copied_Range = .Range[.Cells[Start_Row, 1], .Cells[Stop_Row, Total_Columns]]
End With
Set New_WB = Workbooks.Add
With New_WB
With .Worksheets[1]
.Cells[1, 1].Resize[1, Total_Columns] = Headers
.Cells[2, 1].Resize[Copied_Range.Rows.Count, Total_Columns] = Copied_Range.Value
End With
.SaveAs ACS.Parent.Parent.Path & Application.PathSeparator & "file-" & Z & ".xls", FileFormat:=-4143
.Close
End With
If Stop_Row = ACS.Rows.Count Then Exit Do
Loop
End Sub
_
Kode barukata MoshiM
Kode VBA
Sub flyguy[] Dim ACS As Range, Z As Long, New_WB As Workbook, B As Long, _ Total_Columns As Long, Start_Row As Long, Stop_Row As Long, Copied_Range As Range, File_Name As String Dim Headers[] As Variant Set ACS = ActiveSheet.UsedRange With ACS Headers = .Rows[1].Value Total_Columns = .Columns.Count End With File_Name = Dir[ThisWorkbook.Path & Application.PathSeparator & "*.xls"] On Error GoTo Next_File Do While Len[File_Name] > 0 If File_Name Like "*file-*" Then B = CLng[Split[Split[File_Name, "file-"][1], ".xls"][0]] If B > Z Then Z = B End If Next_File: On Error GoTo -1 File_Name = Dir Loop On Error GoTo 0 Start_Row = 2 Do While Stop_Row 1 Then Start_Row = Stop_Row + 1 Stop_Row = Start_Row + 499 With ACS.Rows If Stop_Row > .Count Then Stop_Row = .Count End With With ACS Set Copied_Range = .Range[.Cells[Start_Row, 1], .Cells[Stop_Row, Total_Columns]] End With Set New_WB = Workbooks.Add With New_WB With .Worksheets[1] .Cells[1, 1].Resize[1, Total_Columns] = Headers .Cells[2, 1].Resize[Copied_Range.Rows.Count, Total_Columns] = Copied_Range.Value End With .SaveAs ACS.Parent.Parent.Path & Application.PathSeparator & "file-" & Z & ".xls", FileFormat:=-4143 .Close End With If Stop_Row = ACS.Rows.Count Then Exit Do Loop End Sub
Klik untuk meluaskan