只能将可见行的值从一个工作簿复制到使用VBA宏的新工作簿中 [英] Only copy values of visible rows from one workbook into a new workbook using VBA Macros

问题描述

我有一些将Sheet2从我现有的工作簿复制到一本新的工作簿的宏。这个代码的工作原理是除了有隐藏的行不应该在新的工作簿上显示。

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.

这是我写的复制表的代码超过和只有它的值：

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?

编辑
提交答案后，代码稍有变化。这里有更多的信息。
正在复制的工作表中的某些单元格被合并，我在代码行上收到错误：

EDIT The 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?

我不想去表单

推荐答案

替换行

ThisWorkbook.Worksheets("Quote & Proposal").Cells.Copy

与

ThisWorkbook.Worksheets("Quote & Proposal").Cells. _
    SpecialCells(xlCellTypeVisible).Copy

它应该可以工作。

这篇关于只能将可见行的值从一个工作簿复制到使用VBA宏的新工作簿中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！