感谢您抽出宝贵的时间为我提供帮助,我设法找到了我认为应该分享的解决方案。这并没有沿数据透视表路线进行,而是给了我所需的最终结果。
Sub NormaliseTable()
Application.Calculation = xlManual
' start with the cursor in the table
Dim rTab As Range, C As Range, rNext As Range
Set rTab = ActiveCell.CurrentRegion
If rTab.Rows.Count = 1 Or rTab.Columns.Count = 1 Then
MsgBox "Not a well-formed table!"
Exit Sub
End If
Workbooks.Add ' the sheet for the results
Set rNext = Range("A1")
For Each C In rTab.Offset(2, 1).Resize(rTab.Rows.Count - 1, _
rTab.Columns.Count - 1).Cells
If Not (IsEmpty(C.Value) Or C.Value = "0") Then
rNext.Value = rTab.Cells(C.Row - rTab.Row + 1, 1)
rNext.Offset(0, 1).Value = rTab.Cells(1, C.Column - rTab.Column + 1)
rNext.Offset(0, 2).Value = C.Value
Set rNext = rNext.Offset(1, 0)
End If
Next
ChDir "\\Client\T$"
ActiveWorkbook.SaveAs Filename:="\\Client\T$\M3-FC-IMPORT.csv", FileFormat _
:=xlCSV, CreateBackup:=False
ActiveWorkbook.Save
ActiveWindow.Close
Application.Calculation = xlAutomatic
End Sub
如果没有将“应用程序计算”手动进行,则需要花费几个小时,因此我使用宏及其几乎瞬时的功能将其关闭并打开!
谢谢山姆
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句