+ Reply to Thread
Results 1 to 3 of 3

getting run time error 424 or 1004 when using Saveas method

  1. #1
    Registered User
    Join Date
    03-23-2013
    Location
    slough
    MS-Off Ver
    excel 2007
    Posts
    20

    getting run time error 424 or 1004 when using Saveas method

    Hello,

    I have a macro, which basically updates all the country details, respectively, by dynamically creating country folders and country sheets from the original all the country updated excel file. It runs perfectly fine without errors on my desktop , but gives me run time error when used on a network drive.. why is it?? the macro is copied below....

    Sub DistributeRowsToNewWBS()
    Dim wbNew As Workbook
    Dim wsData As Worksheet
    Dim wsCrit As Worksheet
    Dim wsNew As Worksheet
    Dim rngCrit As Range
    Dim LastRow As Long
    Dim myCountry As String
    Dim myNewPath As String
    Dim mySourcePath As String
    Dim myFinalFolder As String
    Dim dt As String

    Set wsData = Worksheets("Combined Decision Logs 12_Mar") ' name of worksheet with the data
    Set wsCrit = Worksheets.Add

    LastRow = wsData.Range("B" & Rows.Count).End(xlUp).Row

    ' column H has the criteria
    wsData.Range("F1:F" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsCrit.Range("B1"), Unique:=True

    Set rngCrit = wsCrit.Range("B2")
    While rngCrit.Value <> ""
    Set wsNew = Worksheets.Add
    ' change E to reflect columns to copy
    wsData.Range("B1:U" & LastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCrit.Offset(-1).Resize(2), CopyToRange:=wsNew.Range("B1"), Unique:=True
    wsNew.Name = rngCrit
    wsNew.Copy
    Set wbNew = ActiveWorkbook
    myCountry = rngCrit.Value
    mySourcePath = "http://one.akzonobel.intra/bu/113_90/projects/winport/team/Shared%20Documents/UPP/Country%20reviews/"
    myFinalFolder = "\Decison And Action Logs\"
    myNewPath = mySourcePath & myCountry
    On Error Resume Next
    MkDir myNewPath
    On Error GoTo 0
    myNewPath = myNewPath & myFinalFolder
    On Error Resume Next
    MkDir myNewPath
    On Error GoTo 0
    'saves new workbook in path of existing workbook
    dt = Format(CStr(Now), "dd_mm_yy")
    wbNew.SaveAs myNewPath & rngCrit & dt -----------get an error here .
    wbNew.Close SaveChanges:=True
    Application.DisplayAlerts = False
    wsNew.Delete
    rngCrit.EntireRow.Delete
    Set rngCrit = wsCrit.Range("B2")
    Wend

    wsCrit.Delete
    Application.DisplayAlerts = True

    End Sub

    Could someone kindly some this problem.. its taking me ages to solve it..

    Thanks,
    Anchal.

  2. #2
    Forum Contributor
    Join Date
    03-12-2013
    Location
    Chicago, USA
    MS-Off Ver
    Excel 2007
    Posts
    228

    Re: getting run time error 424 or 1004 when using Saveas method

    Does the location that you want to save it to exist on the network drive? If it only exists on your computer and someone else runs this macro, it will not be able to find the location to save it to.

  3. #3
    Registered User
    Join Date
    03-23-2013
    Location
    slough
    MS-Off Ver
    excel 2007
    Posts
    20

    Re: getting run time error 424 or 1004 when using Saveas method

    yes the location exists on the intranet .. well the source file is in country reviews folder and the destination files created will go under country reviews/ ITALY/Action and Decision Logs folder.. and the country name like ITALY and folder underneath named Action and Decision Logs will be created by my macro... please respond back majosum ... can it be done?

+ 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