仅使用 VBA 宏将一个工作簿中可见行的值复制到新工作簿中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17122063/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Only copy values of visible rows from one workbook into a new workbook using VBA Macros
提问by Mike Barnes
I have some macros that copy Sheet 2 from my exsisting work book to a new work book. This code works as it should except that there are hidden rows that should not be shown on the new work book.
我有一些宏可以将 Sheet 2 从我现有的工作簿复制到新的工作簿。此代码按其应有的方式工作，但存在不应在新工作簿上显示的隐藏行。
Here is the code that I have written that copies the sheet over and pastes only its values:
这是我编写的代码，用于复制工作表并仅粘贴其值：
Dim Output As Workbook
Dim FileName As String
Set Output = Workbooks.Add
Application.DisplayAlerts = False
ThisWorkbook.Worksheets(sourceSheetName).Cells. _
SpecialCells(xlCellTypeVisible).Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=True, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats
FileName = ThisWorkbook.Path & "\" & ThisWorkbook.Worksheets("Quote Questions").Range("AK545").Value & ".xls"
Output.SaveAs FileName
So where would the code go that will only display the unhidden cells and not the hidden ones?
那么只显示未隐藏单元格而不显示隐藏单元格的代码会去哪里呢？
EDITThe code has changed slightly after an answer was submitted. Here is more info. Some of cells in the sheet that is being copied are merged and I get an error on the line of code:
编辑提交答案后，代码略有更改。这里有更多信息。正在复制的工作表中的一些单元格被合并，我在代码行上收到错误：
ThisWorkbook.Worksheets(sourceSheetName).Cells. _
SpecialCells(xlCellTypeVisible).Copy
Saying: Cannot change part of a merged cell, so im guessing there needs to another piece to add?
说：Cannot change part of a merged cell，所以我猜需要添加另一块？
I do not want to go to the sheet and have un-merge all the cells manually.
我不想转到工作表并手动取消合并所有单元格。
回答by Peter Albert
Replace the line
更换线路
ThisWorkbook.Worksheets("Quote & Proposal").Cells.Copy
with
和
ThisWorkbook.Worksheets("Quote & Proposal").Cells. _
SpecialCells(xlCellTypeVisible).Copy
and it should work.
它应该工作。
回答by surfmuggle
Copy only visible Rows (not hidden)
仅复制可见行（不隐藏）
You can check if the Row is hidden with this code
您可以使用此代码检查该行是否隐藏
Sub RowIsHidden()
For i = 1 To 7
MsgBox Cells(i, 1).EntireRow.Hidden
Next
End Sub
Copy Cells and Paste only Values
复制单元格并仅粘贴值
This is similar to your code above. Instead of index of the sheet you could also use the sheetname
这类似于您上面的代码。除了工作表的索引，您还可以使用工作表名称
Sub CopyOnlyValuesFromSheet()
' Copy all Cells from first Sheet (SheetIndex =1)
ThisWorkbook.Worksheets(1).Cells.Copy
' Select second Sheet (SheetIndex =2)
ThisWorkbook.Worksheets(2).Select
' Paste only values into Selection
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=True, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats
End Sub
Clear Values of hidden Rows
清除隐藏行的值
I have tried to use Cells(i, 1).EntireRow.Delete Shift:=xlUpbut since this has consequence over which rownumber i you have to iterate next it is easier to just clear the values
我曾尝试使用，Cells(i, 1).EntireRow.Delete Shift:=xlUp但由于这会影响我接下来必须迭代的行号，因此清除值更容易
Sub RowIsHiddenClearValue()
For i = 1 To 10
If Cells(i, 1).EntireRow.Hidden Then
Cells(i, 1).EntireRow.Value = ""
End If
Next
End Sub
Based of Peters answer
基于彼得斯的回答
Make sure that the cursor in the destination sheet is placed in the first cell.
确保目标工作表中的光标位于第一个单元格中。
Sub AnotherAnswer()
Call CopyValuesOfVisibleRows("Quote & Proposal", "Quote Questions")
End Sub
Sub CopyValuesOfVisibleRows(sourceSheetName, destinationSheetName)
ThisWorkbook.Worksheets(sourceSheetName).Cells. _
SpecialCells(xlCellTypeVisible).Copy
ThisWorkbook.Worksheets(destinationSheetName).Paste
End Sub
If you need more pointers to put the pieces together please provide more details with which parts you have problems.
如果您需要更多的指针来将各个部分放在一起，请提供您遇到问题的部分的更多详细信息。
