我有两张: 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 = 5So 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-14Can 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 SubScreenshots:
Set-up:
Result:
Let us know if this helps.
更多推荐
发布评论