+ Reply to Thread
Results 1 to 2 of 2

copy paste macro not working

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    06-30-2008
    Location
    london
    Posts
    108

    copy paste macro not working

    Hi there,

    I am trying to create a macro that will copy the contents in one sheet and paste into the sheets of four other files, as per below...

    Sub autoupdate()
         '
         ' Macro to update forecast files PEL/FSA/FSSI/FSW
         '
        Const path As String = _
        "Y:\Sales\National Accounts - Traditional and Grocery\Grocery\FINANCIAL\Forecasts\F11\1.0 Master F10AOP Full Year Forecast\"
        Dim vecWBs(1 To 4) As String
        Dim wb As Workbook
        Dim rng As Range
        Dim i As Long
         
        vecWBs(1) = "PEL F11AOP MASTER FILE.xls"
        vecWBs(2) = "FSA F11AOP MASTER FILE.xls"
        vecWBs(3) = "FSSI F11AOP MASTER FILE.xls"
        vecWBs(4) = "FSW F11AOP MASTER FILE.xls"
         
        Columns("B:K").Select
        Set rng = Selection
         
        For i = 1 To 4
             
            Set wb = Workbooks.Open(path & vecWBs(i))
            With wb
                 
                 
                rng.Copy.Worksheets(1).Range ("B1")
                .Save
                .Close
            End With
        Next i
         
        Set wb = Nothing
         
        Application.CutCopyMode = False
    End Sub
    Unfortunatelt it doesnt work. i get a "Run-time error '438'"

    and the below code is highlighted...

    HTML Code: 
    does anyone have any ideas why this is happening?

    Any ideas welcome.

    Many thanks,

    Nicko

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,327

    Re: copy paste macro not working

    Maybe try...

    rng.Copy Sheet1.Range("B1")
    HTH
    Regards, Jeff

+ 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