+ Reply to Thread
Results 1 to 7 of 7

Copy data from a certain range and past multiple times.

Hybrid View

Butehawk Copy data from a certain... 04-29-2010, 06:22 PM
JBeaucaire Re: Copy data from a certain... 04-29-2010, 07:53 PM
Butehawk Re: Copy data from a certain... 04-29-2010, 09:23 PM
JBeaucaire Re: Copy data from a certain... 04-30-2010, 12:56 AM
royUK Re: Copy data from a certain... 04-30-2010, 01:12 AM
Butehawk Re: Copy data from a certain... 04-30-2010, 12:40 PM
JBeaucaire Re: Copy data from a certain... 04-30-2010, 09:15 PM
  1. #1
    Registered User
    Join Date
    01-13-2010
    Location
    Erskine
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    34

    Re: Copy data from a certain range and past multiple times.

    I had to make some changes to this to fit my data sheet. but it will not run the macro.What have I done wrong.
    The data I need to copy is in the range D3:V66.
    Any column of this data could have a reference to the row in the master data sheet. So I included these rows in the macro.
    When I try to run it it highlights the "option Explicit" line and says "Invalid inside Procedure".

    Sub pastemanyrowsinsequence()
    '
    ' pastemanyrowsinsequence Macro
    ' Macro recorded 30/04/2010 by Roger
    '
    
        Option Explicit
    
    Sub BruteCopyChange()
    Dim CopyRNG As Range
    Dim CopyCNT As Long
    Dim CopyNum As Long
    Dim NextRow As Long
    
    CopyCNT = Application.InputBox("Copy how many times?", "Copy Count", 10, Type:=1)
    If CopyCNT = 0 Then Exit Sub
    
    Application.ScreenUpdating = False
    Set CopyRNG = Range("D3:V66")
    NextRow = 67
    
    For CopyNum = 1 To CopyCNT
        CopyRNG.Copy Range("D" & NextRow)
        Range("D" & NextRow & ":D" & NextRow + 63 & _
              ",E" & NextRow & ":E" & NextRow + 63 & _
              ",F" & NextRow & ":F" & NextRow + 63 & _
              ",G" & NextRow & ":G" & NextRow + 63 & _
              ",H" & NextRow & ":H" & NextRow + 63 & _
              ",I" & NextRow & ":I" & NextRow + 63 & _
              ",J" & NextRow & ":J" & NextRow + 63 & _
              ",K" & NextRow & ":K" & NextRow + 63 & _
              ",L" & NextRow & ":L" & NextRow + 63 & _
              ",M" & NextRow & ":M" & NextRow + 63 & _
              ",N" & NextRow & ":N" & NextRow + 63 & _
              ",O" & NextRow & ":O" & NextRow + 63 & _
              ",P" & NextRow & ":P" & NextRow + 63 & _
              ",Q" & NextRow & ":Q" & NextRow + 63 & _
              ",R" & NextRow & ":R" & NextRow + 63 & _
              ",S" & NextRow & ":S" & NextRow + 63 & _
              ",T" & NextRow & ":T" & NextRow + 63 & _
              ",U" & NextRow & ":V" & NextRow + 63 & _
              ",V" & NextRow & ":V" & NextRow + 63).Replace What:="$3", _
            Replacement:="$" & 3 + CopyNum, LookAt:=xlPart, SearchOrder:=xlByRows, _
            MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        NextRow = NextRow + 64
    Next CopyNum
    
    CopyRNG.EntireColumn.AutoFit
    Application.ScreenUpdating = True
    End Sub






    I set the copy count to 10 to try it out,on the first ten data ranges.

    Roger
    Last edited by Butehawk; 04-30-2010 at 12:37 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1