基于单元格值多次复制粘贴数据(Copy

基于单元格值多次复制粘贴数据(Copy-pasting data a number of times based on cell value)

我有两张： DataEntry和Datasheet 。 在C4上的DataEntry上写入Number (Quantity of Data)在E4上写入数据Number (Quantity of Data) 。 我想根据DataEntry E4上提到的次数将数据粘贴到Datasheet上。

对于Eg。 DataEntry上提到的数据是

C4 = Markers E4 = 5

因此，我希望在相应行的Datasheet粘贴此标记5次，并在下一列中添加日期，以及在最后数据下面添加的其他项目：

它在DataSheet样子如何：

A2 B2 Markers 01-Jan-14 Markers 01-Jan-14 Markers 01-Jan-14 Markers 01-Jan-14 Markers 01-Jan-14

有人可以帮助我使用上面的VBA代码

I have two sheets: DataEntry and Datasheet. There is data written on DataEntry on C4 and Number (Quantity of Data) on E4. I want data to be pasted on Datasheet based on number of times mentioned on DataEntry E4.

For Eg. Data Mentioned on DataEntry is

C4 = Markers E4 = 5

So I want entry of this Markers 5 times pasted in Datasheet on respective rows with Date in next column and so on other items to be added below last data:

How it would look like in DataSheet:

A2 B2 Markers 01-Jan-14 Markers 01-Jan-14 Markers 01-Jan-14 Markers 01-Jan-14 Markers 01-Jan-14

Can somebody help me with VBA codes for above

最满意答案

试试这个：

Sub CopyBasedOnQuantity() Dim DataEntry As Worksheet, DataSht As Worksheet Dim ItemName As Range, ItemCount As Range Dim NRow As Long, TargetCell As Range With ThisWorkbook Set DataEntry = .Sheets("DataEntry") Set DataSht = .Sheets("Datasheet") End With With DataEntry Set ItemName = .Range("C4") Set ItemCount = .Range("E4") End With With DataSht NRow = .Range("A" & Rows.Count).End(xlUp).Row + 1 Set TargetCell = .Range("A" & NRow) TargetCell.Resize(ItemCount.Value, 1).Value = ItemName.Value TargetCell.Offset(0, 1).Resize(ItemCount.Value, 1).Value = Date End With End Sub

截图：

建立：

结果：

如果这有帮助，请告诉我们。

Try this:

Sub CopyBasedOnQuantity() Dim DataEntry As Worksheet, DataSht As Worksheet Dim ItemName As Range, ItemCount As Range Dim NRow As Long, TargetCell As Range With ThisWorkbook Set DataEntry = .Sheets("DataEntry") Set DataSht = .Sheets("Datasheet") End With With DataEntry Set ItemName = .Range("C4") Set ItemCount = .Range("E4") End With With DataSht NRow = .Range("A" & Rows.Count).End(xlUp).Row + 1 Set TargetCell = .Range("A" & NRow) TargetCell.Resize(ItemCount.Value, 1).Value = ItemName.Value TargetCell.Offset(0, 1).Resize(ItemCount.Value, 1).Value = Date End With End Sub

Screenshots:

Set-up:

Result:

Let us know if this helps.