VBA - Open workbook to copy and paste separate csv file

[breynolds0431]

Hello:

I have a semi-working macro that I discovered primarily with Recording. The premise is to have a main workbook with two buttons. One to open a dialogue box, navigate to a csv file, copy the contents, and paste into a predetermined tab of the main workbook. The second button does the same thing, but to an updated csv file. Then another tab analyzes for any variances. It works fine outside of the program that is handling our excel files. The problem is with the line of vba which states "Windows("PS&R Variance Anaylsis.xlsm").Activate. When the excel file is loaded into this other program, it doesn't recognize the Window name. Does anyone see an alternative to the Windows().Activate line?

Also, and this may kill two birds with one stone, but when the open-file dialogue box opens and the user hits cancel the active worksheet is copied into the destination. Is there a way to stop that?

Sub GetNewPSR()
'
' GetNewPSR Macro
'
    Application.Dialogs(xlDialogOpen).Show Arg1:="*.*"
    Cells.Select
    Selection.Copy
    [U]Windows("PS&R Variance Analysis.xlsm")[/U].Activate
    Sheets("New PSR").Select
    Range("A1").Select
    ActiveSheet.Paste
    Dim Rng As Range, dn As Range
    If Application.CountA(Worksheets("New PSR").Range("A:A"), Target) > 1000 Then
    MsgBox "TOO MANY ROWS OF DATA. This Variance Analysis is only capable of analyzing 1,000 rows of data. Rerun Settlement in HFS."
    Worksheets("Analysis").Visible = False
    Worksheets("No Go").Visible = True
    Worksheets("Instructions").Visible = False
    Worksheets("Old PSR").Visible = False
    Worksheets("New PSR").Visible = False
    Sheets("No Go").Select
    Exit Sub
    End If
    Sheets("Instructions").Activate
End Sub

 

[AlphaFrog]

Try this...

[color=darkblue]Sub[/color] GetNewPSR()
[color=green]'[/color]
[color=green]' GetNewPSR Macro[/color]
[color=green]'[/color]
    ThisWorkbook.Sheets("New PSR").UsedRange.ClearContents
    Application.Dialogs(xlDialogOpen).Show Arg1:="*.*"
    [color=darkblue]If[/color] ActiveWorkbook [color=darkblue]Is[/color] ThisWorkbook [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color] [color=green]'User canceled[/color]
    
    ActiveSheet.UsedRange.Cells.Copy _
        Destination:=ThisWorkbook.Sheets("New PSR").Range("A1") [color=green]'ThisWorkbook is the workbook that contains this code[/color]
        
    ActiveWorkbook.Close SaveChanges:=[color=darkblue]False[/color] [color=green]'Close the Opened workbook[/color]
    
    [color=darkblue]If[/color] Application.CountA(Worksheets("New PSR").Range("A:A")) > 1000 [color=darkblue]Then[/color]
        MsgBox "TOO MANY ROWS OF DATA. This Variance Analysis is only capable of analyzing 1,000 rows of data. Rerun Settlement in HFS."
        Worksheets("Analysis").Visible = [color=darkblue]False[/color]
        Worksheets("No Go").Visible = [color=darkblue]True[/color]
        Worksheets("Instructions").Visible = [color=darkblue]False[/color]
        Worksheets("Old PSR").Visible = [color=darkblue]False[/color]
        Worksheets("New PSR").Visible = [color=darkblue]False[/color]
        Sheets("No Go").Select
    [color=darkblue]Else[/color]
        Sheets("Instructions").Activate
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[breynolds0431]

That did it. Thanks so much!