Results 1 to 10 of 10

Windows("file name always different").Activate cant reference the workbook in my MACRO !!

Threaded View

  1. #1
    Registered User
    Join Date
    06-12-2013
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    18

    Windows("file name always different").Activate cant reference the workbook in my MACRO !!

    All
    My macro is not working and im at a loss

    I have 2 workbooks “FORM” and “EXPORT MASTER” and essentially FORM is for entering data and EXPORT MASTER is for collecting it
    Unfortunately because all users need to print FORM and get a signature on it before sending it on to a different department to then bring the data back up using the unique reference number that’s created this is the only way it can be done and explaining the ins and outs of the 20 departments it affects would take me a long time so your gonna have to trust me theres no way around it.


    When users have completed all information in FORM and start the macro then FORM is saved as a new filename which changes every time (and is taken from Cell AV36) using the below code……

    'Save form as filename CRN+AMOUNT
        If ActiveSheet.Range("AW30") = 0 Then
        SAVENAME1 = ActiveSheet.Range("AV36")
        SAVENAME2 = "S:\Finance\Financial Operations\CHQ REQ\PAYREQs\" & SAVENAME1
        ActiveWorkbook.SaveAs Filename:=SAVENAME2
        
        Else
        If ActiveSheet.Range("AW30") > 0 Then
        Exit Sub
    
    
    End If
    End If
    Then EXPORT MASTER workbook is activated and a unique reference number created which is then copied…….

    '  OPEN MASTER SHEET
        Workbooks.Open Filename:= _
            "S:\Finance\Financial Operations\CHQ REQ\EXPORT MASTER.xls"
            
        Windows("EXPORT MASTER.xls").Activate
        Range("A1").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(1, 0).Range("A1").Select
        ActiveCell.FormulaR1C1 = "=R[-1]C+1"
        ActiveCell.Select
        Selection.Copy
    This is where my problem occurs…..


    Windows("SAVENAME2").Activate
        Range("U2:X2").Select
        ActiveSheet.Paste
    As I try to refer back to FORM under its new filename I do not know how to refer to it correctly so I can paste the unique number back
    I have tried many different versions of
    Windows("SAVENAME2").Activate
    But none seem to work

    After I have got back to it then the rest of the macro worsk fine…
    ' COPY DATA LINE TO MASTERWORKBOOK
    
        Sheets("UPLOAD LINE").Select
        Rows("4:5").Select
        Application.CutCopyMode = False
        Selection.Copy
        Windows("EXPORT MASTER.xls").Activate
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Range("A1").Select
        
    ' SAVE AND CLOSE
    
        ActiveWorkbook.Save
        ActiveWindow.Close
        Sheets("FORM").Select
        Range("A1").Select
        
        
    End Sub
    I wish to add more but will not complicate the macro any further until ive fixed this part

    Many thanks in advance for your help 

    Michael
    Last edited by michaelproctor001; 06-18-2013 at 11:11 AM. Reason: add CODE tags

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