我想复制工作表"Prime Data"中A列的所有行，并粘贴到工作表"Pime-Tracking"的A列中用一个代码 [英] I want to copy all rows from column A on worksheet "Prime Data" and paste into column A on worksheet "Pime - Tracking" with a code

问题描述

我有一个excel工作表，其中包含大量具有各种信息的数据(例如，邮寄信息，最佳联系人，法定姓名，电话号码，保险要求....)一直以来都在添加新的联系人在主要数据"表上按字母顺序过滤.

I have an excel sheet that have a lot of data with various info (i.e...mailing info, best contact person, legal name, phone number, insurance requirements....) New contacts are added all the time and then are filtered in alphabetical order on the "Prime Data" sheet.

当我在"Prime Data"上添加一行以添加新联系人时，我使用的VBA代码会将数据复制到"Prime-Tracking"工作表中，并将单元格向下移动以说明插入的行.问题在于，它仅将单元格在该列中向下移动，而没有在所有列中插入新行.

When I insert a row, on "Prime Data" to add a new contact, the VBA code that I use will copy the data to "Prime - Tracking" sheet and move the cells down to account for the inserted line. The problem is that it only moves the cells down in that column and do not insert a new row across all columns.

主要-跟踪"每行大约有20列，用于说明要收集的项目的清单.当插入的行从"Prime Data"移出时，它的行为不像我在所有列中插入一行，而是在A列中添加了一行.然后，使得对所有项目的跟踪来自于下面/上方的供应商.插入的联系人.

The "Prime - Tracking" have about 20 columns per row to account for a checklist of items to collect. When the inserted line is brought over from the "Prime Data" it does not act like I inserted a row across all columns but adds a line to just column A. This then makes the tracking of all items be from the vendor below/above the inserted contact.

我目前正在使用以下代码来执行此操作.

I am currently using the below code to do this.

Private Sub CommandButton1_Click()

Dim SrcRng As Range
With Worksheets("Prime Data")
    Set SrcRng = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
End With
Worksheets("Prime - Tracking").Range("A2").Resize(SrcRng.Rows.Count, 1).Value = SrcRng.Value

End Sub

推荐答案

从一张纸到另一张纸的行的简单复制粘贴是:

A simple copy paste for a row from sheet to sheet is:

Dim i as long, lr as long
i = 'source data row
With Worksheets("Prime Data")
    .Rows(i).Copy
End with
With Worksheets("Prime - Tracking")
    lr = .cells(.rows.count,1).end(xlup).row
    .Rows(lr+1).Paste 'or .PasteSpecial xlValues
End With

如果要插入，可以:

Dim i as long
i = 'source data row
With Worksheets("Prime Data")
    .Rows(i).Copy
End with
With Worksheets("Prime - Tracking")
    .Rows(2).Insert xlShiftDown
End With

第三种方式，如果复制/粘贴花费的时间太长:

Third way, if copy/paste is taking too long:

Dim i as long
i = 'source data row
With Worksheets("Prime - Tracking")
    .Rows(2).Insert xlShiftDown
    .Rows(2).value = Worksheets("Prime Data").Rows(i).value
End With

这篇关于我想复制工作表"Prime Data"中A列的所有行，并粘贴到工作表"Pime-Tracking"的A列中用一个代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！