• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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