• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

VB Repeat Macro

I would like to repeat a body of text in a worksheet 340 times pasted...Below is the text and i have included my code.

{1:F01SBOSUS3UXIMS0001000001}{2:O5431610090427SUNDUS33XEPH00000000010904271610N}{4:
:16R:GENL
:20C::SEME//10227333
:23G:CANC
:16S:GENL
:16R:TRADDET
:94B::TRAD//EXCH/XASX
:98A::SETT//20090430
:98A::TRAD//20090427
:90B::DEAL//ACTU/AUD11,65
:35B:ISIN AU000000LNN6
LION NATHAN LIMITED
:16R:FIA
:12A::CLAS/ISIT/FCOM
:16S:FIA
:16S:TRADDET
:16R:FIAC
:36B::SETT//UNIT/45400,
:97A::SAFE//123456
:16S:FIAC
:16R:SETDET
:22F::SETR//TRAD
:16R:SETPRTY
:95P::PSET//CAVLARBA
:16S:SETPRTY
:16R:SETPRTY
:95R::REAG/CVSA/443
:16S:SETPRTY
:16R:SETPRTY
:95P::BUYR//MACQAU2EXXX
:16S:SETPRTY
:16R:AMT
:19A::DEAL//AUD528910,
:16S:AMT
:16R:AMT
:19A::EXEC//AUD1057,82
:16S:AMT
:16R:AMT
:19A::SETT//AUD527852,18
:16S:AMT
:16S:SETDET
-}

For x = 1 To 340
    Selection.Copy
    
    Selection.End(xlDown).Select
    ActiveSheet.Paste
    ActiveCell.Offset(1, 0).Select
 
Next x
End Sub

Open in new window

0
tomasdlv
Asked:
tomasdlv
1 Solution
 
MrBookinsCommented:
Is all of that text in one cell?

If so, assuming that that text is in Cell A1 you could use the following code to copy the cell to the next 339 rows:

Public Sub PasteFromA1()
    For i = 2 To 340
        Sheet1.Cells(i, 1) = Sheet1.Cells(1, 1)
    Next
End Sub

If each new line of the above text occupies a different cell, (vertically), use the following code:

Dim curRow As Integer
curRow = 1
While Sheet1.Cells(curRow, 1) <> ""
    For i = 2 To 340
        Sheet1.Cells(curRow, i) = Sheet1.Cells(curRow, 1)
    Next
    curRow = curRow + 1
Wend

That code will copy all non-empty cells from column A to the next 339 columns.

If I am misunderstanding the question, let me know.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now