+ Reply to Thread
Results 1 to 5 of 5

part of macro works on wrong file - Why?

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    11-20-2012
    Location
    stockholm
    MS-Off Ver
    Excel 2010
    Posts
    123

    part of macro works on wrong file - Why?

    Hi,

    The code below is from a macro that collect data from a master file to a forecast file. At the end I have a bit of code (placed within *********) that adds a formula to certain cells. The problem is that the part of code works on the master file instead of the forecast file. I tought adding the wsMaster.select code would make the trick but it doesn't. Do you know how to fix this? Many thanks in advance.

    All the best,
    Martin


    Option Explicit
    
    Sub ImportForecast()
    Dim wbSRC As Workbook, wsMASTER As Worksheet
    Dim fPATHNAME As String, MyFilter As String, lr As Long
    Dim kr As Long          
    Dim rcell As Range      
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect
    
    
    fPATHNAME = "N:\ForecastInput_NEW.xls"
    Set wsMASTER = ThisWorkbook.Sheets("ForecastInput")        'edit this sheetname as needed
    MyFilter = wsMASTER.Range("A1").Value              'criteria to gather from the source file
    
    wsMASTER.UsedRange.Offset(16).Clear                  'clears prior entries, resets the Master sheet
    
    On Error Resume Next
    Set wbSRC = Workbooks.Open(fPATHNAME)
    If wbSRC Is Nothing Then
        MsgBox "The Source.xls file could not be found"
        Exit Sub
    End If
    
    With wbSRC.Sheets("Sheet1")                     'edit the sheetname as needed where the data resides
        .AutoFilterMode = False                     'remove any prior filters
        .Rows(6).AutoFilter                         'add a new filter
        .Rows(6).AutoFilter 2, MyFilter             'apply the 'nordic' filter test to column A
        lr = .Range("A" & .Rows.Count).End(xlUp).Row
        If lr > 6 Then                              'if rows found, copy them all to the MASTER
            '.Range("A7:A" & LR).EntireRow.Copy wsMASTER.Range("A17")
            .Range("A7:AN" & lr).Copy               'copy the data in A:Z
            wsMASTER.Range("A17").PasteSpecial xlPasteValues    'paste the values only in the MASTER
    
            
        End If
    End With
    
    'Sum forecast and actual in forecast file
    '****************************************************
    'dim kr and din rcell added above
    wsMASTER.Select
    kr = Cells(Rows.Count, 1).End(xlUp).Row
    
    For Each rcell In Range("AP17:AQ" & kr)
        
            Cells(4, rcell.Column).Copy                             ' AP4 and AQ4 contains a sum formula and are copied to all cells in range AP17:AQ"kr"
            rcell.PasteSpecial xlPasteFormulas
        
    Next rcell
    '******************************************************
    
    wbSRC.Close False                               'close the source file, no changes saved
    Application.ScreenUpdating = True
    End Sub
    Last edited by masben; 03-01-2013 at 02:15 PM.

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: part of macro works on wrong file - Why?

    the on error resume next is hiding the fact you can't select a sheet in an inactive workbook. move the line that closes the other workbook before your problem code or properly qualify your range objects
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Forum Contributor
    Join Date
    11-20-2012
    Location
    stockholm
    MS-Off Ver
    Excel 2010
    Posts
    123

    Re: part of macro works on wrong file - Why?

    Quote Originally Posted by JosephP View Post
    the on error resume next is hiding the fact you can't select a sheet in an inactive workbook. move the line that closes the other workbook before your problem code or properly qualify your range objects
    Whao you are fast!!! Thanks I'll try that.
    Cheers, Martin

  4. #4
    Forum Contributor
    Join Date
    11-20-2012
    Location
    stockholm
    MS-Off Ver
    Excel 2010
    Posts
    123

    Re: part of macro works on wrong file - Why?

    JosephP,

    It works perfectly!

    Thanks
    //Martin

  5. #5
    Forum Contributor
    Join Date
    11-20-2012
    Location
    stockholm
    MS-Off Ver
    Excel 2010
    Posts
    123

    Re: part of macro works on wrong file - Why?

    Of course if anyone knows of a better way to add the sum formula to the right of all rows in a dynamic range you are most welcome to share that. In my file a like two sum formulas added to sum two different part of each row.
    Thanks again!

+ 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