在Excel中自动复制和粘贴特定范围的最佳方式是什么？ [英] What is the best way to automate copy and paste specific ranges in excel?

问题描述

我对VBA很新，有一项我想自动化的任务，不知道从哪里开始。我有一个数据集，如下所示。

样本数据

我想要做的是循环通过列A，如果它有东西（将永远是一个电子邮件）选择所有行，直到A列中有一些。复制并粘贴到新标签页。所以第2-5行将复制并粘贴到新的选项卡中。然后第6-9行进入不同的新选项卡。第1行也将复制到每个选项卡。我没有找到帮助这个特定需求的代码，任何帮助将不胜感激。

我发现这个代码，开始修改它，但是没有地方接近我需要或为此事工作。

  Sub split（）
 
 Dim rng As范围
 Dim行As范围
 
设置rng =范围（A：A）
 
对于每行在rng 
'测试如果单元格空
如果row.Value<> 然后
'写入相邻单元格
 row.Select 
 row.Copy 
工作表（Sheet2）。激活
范围（A2）。 
 row.PasteSpecial 
工作表（Sheet1）。激活
结束如果
下一个
结束子
  

解决方案

此代码应提供您需要的：

  Sub Split（）
 
 Dim wb As Workbook 
设置wb = ThisWorkbook 
 
 Dim ws As Worksheet 
 Set ws = wb.Worksheets（1）'更改表索引或使用工作表（Sheet1）方法使用精确名称
 
 Dim rngBegin As Range 
 Dim rngEnd As Range 
 
用ws 
 
 Dim rngHeader As Range 
设置rngHeader = .Range（A1：H1）'每次复制标题
 
 Dim lRowFinal As Long 
 lRowFinal = .Range（C& .Rows.Count）.End（xlUp）.Row'假定最后一行需要的数据将有一个address1 
 
设置rngEnd = .Range（A1）'开始循环
设置rngBegin = rngEnd.End（xlDown）'开始循环
 
 
 
设置rngEnd = rngBegin.End（xlDown）.Offset（-1）
 
 Dim wsNew As Worksheet 
设置wsNew = Worksheets.Add（之后：= wb.Sheets（.Index））'始终在当前复制wsNew.Range（A2）
（b） wsNew.Range（A1：H1）Value = rngHeader.Value 
 
设置rngBegin = rngEnd.End（xlDown）
 
循环直到rngBegin.Row> = lRowFinal 
 
结束
 
结束子
  

I am very new to VBA and there is a task I would like to automate and don't know where to start. I have a data set that looks like below.

Sample Data

What I'm trying to do is loop through column A and if it has something in it (will always be an email) select all rows until there is something in column A again. Copy and paste into new tab. So row 2-5 would copy and paste into a new tab. Then row 6-9 into a different new tab. Also row 1 would copy to each tab as well. I haven't been able to find code to help with this specific need and any help would be greatly appreciated.

I found this code and started modifying it but, it's nowhere close to what I need or working for that matter.

Sub split()

Dim rng As Range
Dim row As Range

Set rng = Range("A:A")

For Each row In rng
    'test if cell is empty
    If row.Value <> "" Then
        'write to adjacent cell
        row.Select
        row.Copy
        Worksheets("Sheet2").Activate
        Range("A2").Select
        row.PasteSpecial
        Worksheets("Sheet1").Activate
    End If
Next
End Sub

解决方案

This code should provide what you need:

Sub Split()

Dim wb As Workbook
Set wb = ThisWorkbook

Dim ws As Worksheet
Set ws = wb.Worksheets(1) 'change sheet index or use Worksheets("Sheet1") method to use exact name

Dim rngBegin As Range
Dim rngEnd As Range

With ws

    Dim rngHeader As Range
    Set rngHeader = .Range("A1:H1") 'to copy headers over each time

    Dim lRowFinal As Long
    lRowFinal = .Range("C" & .Rows.Count).End(xlUp).Row 'assumes eventually last row of needed data will have an address1

    Set rngEnd = .Range("A1") ' to begin loop
    Set rngBegin = rngEnd.End(xlDown) 'to begin loop

    Do

        Set rngEnd = rngBegin.End(xlDown).Offset(-1)

        Dim wsNew As Worksheet
        Set wsNew = Worksheets.Add(After:=wb.Sheets(.Index))'always after current sheet, change as needed

        .Range(.Cells(rngBegin.Row, 1), .Cells(rngEnd.Row, 8)).Copy wsNew.Range("A2")
        wsNew.Range("A1:H1").Value = rngHeader.Value

        Set rngBegin = rngEnd.End(xlDown)

    Loop Until rngBegin.Row >= lRowFinal

End With

End Sub

这篇关于在Excel中自动复制和粘贴特定范围的最佳方式是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！