在关闭之前，将动态范围复制到新工作簿中，添加标题并将新工作簿保存到本地目录中 [英] Copying Dynamic Range Into New Workbooks, Adding Header, And Saving New Workbooks To Local Directory Before Closing

问题描述

我有一个包含一张纸的主工作簿，我需要分成许多工作簿，每个工作簿都有一个工作表.

I have a master workbook with one sheet that I need to be broken into many workbooks that each have a single worksheet.

当主"工作表中的行与B列中的内容相同时，将创建这些新创建的工作簿.

These newly created workbooks will be created when rows in the "Master" worksheet have the same content in column B.

我需要将这些工作簿保存到相同的特定本地目录中，文件名是B列和&列的内容.".csv"将文件设置为CSV文件而不是XLSX文件.

I need these workbooks to be saved to the same specific local directory with the file name being the content of column B & ".csv" to make the file a CSV file rather than an XLSX file.

这是我到目前为止的内容(很多来自该站点上另一个被推荐的线程，我对此进行了一些调整).

Here is what I have so far (a lot of this came from another upvoted thread on this site with a few tweaks by me).

Sub Splitter()
    Dim Master As Workbook
    Set Master = Workbooks("Master").Worksheets("Master") 'This declares the target worksheet.

    last = 1
    For i = 1 To 2000 'This defines the amount of rows
        If Range("B" & i) <> Range("B" & (i + 1)) Then
            Range("A" & last & ":F" & i).Copy 
            Set NewBook = Workbooks.Add
            NewBook.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValues

            Rows(1).EntireRow.Insert Shift:=xlDown
            Range("A1").Value = "Header1"
            Range("B1").Value = "Header2"
            Range("C1").Value = "Header3"
            Range("D1").Value = "Header4"
            Range("E1").Value = "Header5"
            Range("F1").Value = "Header6"

            last = i + 1
            Master.Activate
        End If
    Next i
End Sub

此代码将使用主"工作簿中的单个工作表创建数百个(甚至数千个)工作簿.

This code will create hundreds, if not thousands, of workbooks with single worksheets from the "Master" Workbook.

我在这里遇到了几个问题:

I'm having a couple issues here:

1. 此代码:

1. This code:

Rows(1).EntireRow.Insert Shift:=xlDown
Range("A1").Value = "Header1"
Range("B1").Value = "Header2"
Range("C1").Value = "Header3"
Range("D1").Value = "Header4"
Range("E1").Value = "Header5"
Range("F1").Value = "Header6"

似乎正确添加了标题行，但似乎正在复制电子表格的全部内容并将其粘贴到下一个可用行处.然后，它将覆盖第1行的内容.

appears to be adding the header row correctly but it seems to be copying the entire contents of the spreadsheet and pasting it at the next available row. It then overwrites the contents of row 1.

示例:宏将以下行拉到新工作簿中:

Example: The macro pulls the following rows to a new workbook:

Bat 
Bat
Bat

运行上述代码后，最终产品是:

When the above section of code runs, the final product is:

Header
Bat
Bat
Bat
Bat
Bat

它似乎是在复制内容，然后粘贴到第1行.

It appears to be replicating the content and then pasting over row 1.

第二个问题，如前面提到的，是新创建的工作簿/工作表将不会自动保存(CSV)并关闭，我需要亲自进入并关闭/保存它们.

The second issue, as alluded to earlier, is that the newly created workbooks/worksheets will not be automatically saved (CSV) and closed, and I will need to go in and close/save them each myself.

它们似乎是正确创建的(问题1中的问题除外).它们只是保持打开状态，我需要命名并保存所有它们.由于我确信会有很多新创建的工作簿，因此功能的缺乏将使我的生活非常困难...

They seem to be being created correctly (with the exception of the issue in problem #1). They are simply left open and I need to name and save all of them. Since I am sure there will be a great many of the newly created workbooks, this lack of functionality will make my life very difficult ...

---

任何对此的帮助将不胜感激.我对此很陌生，但是很快就可以使用它.

---

Any help with this would be greatly appreciated. I am fairly new to this, but am picking it up pretty quickly.

我很长的歉意，我想尽可能地清楚一点，以免浪费读者的时间.

My apologies for the long post, I wanted to be as clear as I could as to not waste the readers time.

推荐答案

1. 因为您仍处于 Range("A"& last&:F"& i)的CopyMode.请复制 .Insert 将再次插入复制的行.因此，将 Application.CutCopyMode = False 放在 Rows(1).EntireRow.Insert 之前，以停止再次插入复制的行.

1. Because you are still in CopyMode from Range("A" & last & ":F" & i).Copy the .Insert will insert the copied rows again. Therefore put a Application.CutCopyMode = False right before Rows(1).EntireRow.Insert to stop inserting copied rows again.

您需要 Workbook.SaveAs方法 和 Workbook.Close方法 保存并关闭工作簿.

You need Workbook.SaveAs Method and Workbook.Close Method to save and close the workbooks.

NewBook.SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)
NewBook.Close(SaveChanges, Filename, RouteWorkbook)

例如这应该起作用:

NewBook.SaveAs FileName:="C:\Temp\MyFileName.csv", FileFormat:=xlCSV
NewBook.Close SaveChanges:=False

您应使用工作表(例如 Master.Rows()或)指定任何 Rows()和 Range()> NewBook.Worksheets("Sheet1").Range()，这样可以清楚地知道范围/行位于哪个workbook \ worksheet中.然后，您不需要 Master.Activate

You should specify any Rows() and Range() with a worksheet like Master.Rows() or NewBook.Worksheets("Sheet1").Range() so that is clear in which workbook\worksheet the range/row is. Then you don't need Master.Activate

这篇关于在关闭之前，将动态范围复制到新工作簿中，添加标题并将新工作簿保存到本地目录中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！