更改VBA脚本粘贴数据的方式 [英] Change the way a VBA script pastes data
本文介绍了更改VBA脚本粘贴数据的方式的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如果单元格值为3,那么它将粘贴第1行3次,第2行3次,等等。
当前的粘贴范围如下所示:
行1
第1行
第1行
第2行
第2行
第2行
第3行
第3行
第3行
我想知道VBA脚本是否可以粘贴数据,以便数据是以这种方式:
行1
行2
行3
第1行
第2行
第3行
第1行
第2行
第3行
驱动上述的VBA脚本如下:
Sub CopyJournalLines2 ()
Dim wsInv As Worksheet
Dim i As Integer
Dim j As Integer
Dim iStartRow As Integer
Dim iNumCopies As Integer
Dim iCopyRow As Integer
Dim CopyRange As Range
Dim PasteRange As Range
Set wsInv = ThisWorkbook.Sheets(发票上传)
带有wsInv $ b $ b .Rows(17:5000)。Cells.Clear
iStartRow = 17
iNumCopies =。范围(O12)值
对于i = 1至4
设置CopyRange = .Range(.Cells(i,1),.Cells(i,17))
iCopyRow = iStartRow +(i - 1)* iNumCopies
设置PasteRange = .Range(.Cells(iCopyRow,1),.Cells(iCopyRow,17))
PasteRange.Formula = CopyRange.Formula
对于j = 2到iNumCopies
iCopyRow = iStartRow + j - 1 +(i - 1)* iNumCopies
.Range(.Cells(iCopyRow,1),.Cells(iCopyRow,17 ))公式R1C1 = PasteRange.FormulaR1C1
下一步j
下一个i
结束
结束Sub
解决方案
实际上,问问你的代码要简单得多,因为你刚刚粘贴了同样的4行集 iNumCopies
的次数。
经过全面测试的代码:
Sub CopyJournalLines2()
Dim wsInv As Worksheet
Dim i As Integer,j As Integer
Dim iNumCopies As Integer,iCopyRow As Integer,iStartRow As Integer
Dim CopyRange As Range,PasteRange As Range
Set wsInv = ThisWorkbook.Sheets(发票上传)
与wsInv $ b
$ b .Rows(17:5000)。Cells.Clear
iStartRow = 17
iNumCopies = .Range(O12)。值
j = 0
对于i = 1 To iNumCopies
.Range(.Range (A& iStartRow).Offset(j),.Range(Q& iStartRow + j + 3))。FormulaR1C1 = .Range(A1:Q4)。FormulaR1C1
'粘贴格式和值使用以下代码
'.Range(A1:Q4)。复制
'.Range(.Range(A& iStartRow).Offset(j),.Range & iStartRow + j + 3))。PasteSpecial xlPasteValues
'.Range(.Range(A& iStartRow).Offset(j),.Range(Q& iStartRow + j + 3 ))PasteSpecial xlPasteFormats
j = j + 4
下一个i
结束
结束Sub
I have a VBA script which loops in the following way:
If cell value is 3, then it will paste line 1 3 times, line 2 3 times,etc.
Current paste range looks like this:
Line 1
Line 1
Line 1
Line 2
Line 2
Line 2
Line 3
Line 3
Line 3
I was wondering whether the VBA script can paste the data so the data is in this manner:
Line 1
Line 2
Line 3
Line 1
Line 2
Line 3
Line 1
Line 2
Line 3
VBA script driving the above is as follows:
Sub CopyJournalLines2()
Dim wsInv As Worksheet
Dim i As Integer
Dim j As Integer
Dim iStartRow As Integer
Dim iNumCopies As Integer
Dim iCopyRow As Integer
Dim CopyRange As Range
Dim PasteRange As Range
Set wsInv = ThisWorkbook.Sheets("Invoice Upload")
With wsInv
.Rows("17:5000").Cells.Clear
iStartRow = 17
iNumCopies = .Range("O12").Value
For i = 1 To 4
Set CopyRange = .Range(.Cells(i, 1), .Cells(i, 17))
iCopyRow = iStartRow + (i - 1) * iNumCopies
Set PasteRange = .Range(.Cells(iCopyRow, 1), .Cells(iCopyRow, 17))
PasteRange.Formula = CopyRange.Formula
For j = 2 To iNumCopies
iCopyRow = iStartRow + j - 1 + (i - 1) * iNumCopies
.Range(.Cells(iCopyRow, 1), .Cells(iCopyRow, 17)).FormulaR1C1 = PasteRange.FormulaR1C1
Next j
Next i
End With
End Sub
解决方案
The code for ask your is much simpler, actually, since you are just pasting the same 4 line set iNumCopies
of times.
Fully tested code:
Sub CopyJournalLines2()
Dim wsInv As Worksheet
Dim i As Integer, j As Integer
Dim iNumCopies As Integer, iCopyRow As Integer, iStartRow As Integer
Dim CopyRange As Range, PasteRange As Range
Set wsInv = ThisWorkbook.Sheets("Invoice Upload")
With wsInv
.Rows("17:5000").Cells.Clear
iStartRow = 17
iNumCopies = .Range("O12").Value
j = 0
For i = 1 To iNumCopies
.Range(.Range("A" & iStartRow).Offset(j), .Range("Q" & iStartRow + j + 3)).FormulaR1C1 = .Range("A1:Q4").FormulaR1C1
'to paste formats and values use the following code
'.Range("A1:Q4").Copy
'.Range(.Range("A" & iStartRow).Offset(j), .Range("Q" & iStartRow + j + 3)).PasteSpecial xlPasteValues
'.Range(.Range("A" & iStartRow).Offset(j), .Range("Q" & iStartRow + j + 3)).PasteSpecial xlPasteFormats
j = j + 4
Next i
End With
End Sub
这篇关于更改VBA脚本粘贴数据的方式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文