将工作表集复制到另一个工作簿 [英] copy set of worksheets to another workbook

问题描述

我想将工作簿中的某些工作表复制到另一个工作簿中.我不希望从源工作簿中复制任何公式或链接.我只需要数据.我想使用vbscript来实现这一点.谁能指导我做到这一点.

I want to copy some worksheets in my workbook to another workbook. I dont want any of the formulas or links to be copied from my source workbook.I need just the data. I would like to use vbscript to achieve this. Can anyone guide me to do this.

这是我通过制作工作表副本使其起作用的尝试，但不幸的是，它也复制了公式.

This is my attempt at making it work by doing a worksheet copy, but unfortunately it also copies the formula.

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts=False
Set objWorkbook1= objExcel.Workbooks.Open("SourceFile.xlsx")
set objWorkbook2=objExcel.Workbooks.Add
set sheetsToCopy=objWorkbook1.Sheets(Array("Sheet1","Sheet2","Sheet3"))
sheetsToCopy.Copy objWorkbook2.Sheets(1)
objWorkbook1.save
objWorkbook2.saveAs("TargetFile.xlsx")
objWorkbook1.close
objWorkbook2.close
objExcel.DisplayAlerts=True
set objExcel=nothing

推荐答案

尝试替换

sheetsToCopy.Copy objWorkbook2.Sheets(1)

具有类似这样的内容:

i = 1
For each wks in sheetsToCopy
    If objWorkbook2.Sheets.Count < i Then objWorkbook2.Sheets.Add , objWorkbook2.WorkSheets(objWorkbook2.WorkSheets.Count)
    'adds new worksheet to end of file to keep sheet count in tact
    wks.Cells.Copy 
    With objWorkbook2.Sheets(i)
        .Range("A1").PasteSpecial -4163 '--> numeric constant for PasteValues
        .Name = wks.Name
    End With
    i = i + 1
Next

唯一的问题是确保新工作簿中有足够的工作表.您可以写一张支票以查看有多少张纸，如果我是>，则在复印前先添加一张纸.

the only issue is to make sure there are enough sheets in your new workbook. You can write a check to see how many sheets there are and if i is > then add a sheet before copying.

这篇关于将工作表集复制到另一个工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！