New to VBA and need help with a macro

[Kahlan1177]

Hi,
So i am working on a macro that will allow me to open a window in workbook 1 to find an excel report file and then format that report. Once the format is complete it then copies and pastes to workbook 1 to the specified tab so that the original file is not touched or modified. I have 3 reports to do in this fashion.

This is what i have so far, but it keeps bugging when it goes to format, i only attached the main routine and one of the sub routines. Can anyone help me see my mistake?

Public Sub RiskOpen_Workbook_FileDialog()
'
' RiskOpen_Workbook_FileDialog Marco
' open file window to choose file
'
 
'
Dim fd As FileDialog
Dim lngPathLen As Long
Dim strPath As String, strfile As String, strTemp As String
strTemp = ""
Dim strfilepath as String
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
    .AllowMultiSelect = False
    .ButtonName = "Select"
    .InitialView = msoFileDialogViewDetails
    .Title = "Select File To Update"
   
    .InitialFileName = "\\Reports\Privacy & Reputational Risk Audit"
        With .Filters
  
        .Clear
        .Add "Excel Workbooks", "*.xls; *.xlsx"
     
    End With
   
    .FilterIndex = 1
   
    If .Show = -1 Then
        strTemp = .SelectedItems(1)
    End If
   
    Set fd = Nothing
  
End With
 
If strTemp <> "" Then
 
    lngPathLen = InStrRev(strTemp, "")
 
    strPath = Left(strTemp, lngPathLen)
    strfile = Right(strTemp, Len(strTemp) - lngPathLen)
   
    Debug.Print strPath
    strfilepath = strPath & strfile
   
    If Left(strfile, 6) = "MonAdj" Then
        Call MonAdj
    End If
   
     If Left(strfile, 5) = "Award" Then
        Call Award
    End If
   
    If Left(strfile, 5) = "Other" Then
        Call Other
    End If
   
End If
 
End Sub
 
 
 
 
 
 
 
Public strfilepath As String
 
Sub MonAdj()
'
' MonAdj Macro
 
'
    Workbooks.Open Filename:=strfilepath
    Application.Width = 1011
    Application.Height = 757.5
    Cells.Select
    Cells.EntireColumn.AutoFit
    Selection.ColumnWidth = 8.13
    Rows("1:4").Select
    Selection.Delete Shift:=xlUp
    ActiveWindow.SmallScroll Down:=-18
    Columns("B:B").ColumnWidth = 8
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:E").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:H").Select
    Selection.Delete Shift:=xlToLeft
   Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Columns("G:G").EntireColumn.AutoFit
    Cells.Select
    Selection.Copy
    Windows("MACRO Sampler .xlsm").Activate
    Sheets("Mon Adj").Select
    Cells.Select
    ActiveSheet.Paste
End Sub

 

[mole999]

what is the bug message ?

 

[Fluff]

You are declaring strfilepath as public but then also declaring it again in the first first code.
Remove

Dim strfilepath as String

from any code & just leave the Public declaration

 

[Kahlan1177]

what is the bug message ?

Hi,
I get:

Run-time error '1004':
" could not be found. Check the spelling of the file name, and verify that the file location is correct.

If you are trying ti open the file from your list of most recently used files, make sure that the file has not been renamed, moved, or deleted.

and the file is there. i have not deleted it or renamed it.

 

[Kahlan1177]

Hi,
If i remove that line of code the macro stops at strfilepath = strPath & strfile and gives me a "ambiguous name detected" message.

 

[Fluff]

That sounds as though you still have more than one declaration of strfilepath

 

[Kahlan1177]

That sounds as though you still have more than one declaration of strfilepath

Hi Fluff,
I'm suspecting i have something wrong in either these lines:

If strTemp <> "" Then

lngPathLen = InStrRev(strTemp, "")

strPath = Left(strTemp, lngPathLen)
strfile = Right(strTemp, Len(strTemp) - lngPathLen)

Debug.Print strPath
strfilepath = strPath & strfile

If Left(strfile, 6) = "MonAdj" Then
Call MonAdj
End If

If Left(strfile, 5) = "Award" Then
Call Award
End If

If Left(strfile, 5) = "Other" Then
Call Other
End If

End If

End Sub



or in this the sub routine that is being Called:


Public strfilepath As String

Sub MonAdj()
'
' MonAdj Macro

'
Workbooks.Open Filename:=strfilepath
Application.Width = 1011
Application.Height = 757.5
Cells.Select
Cells.EntireColumn.AutoFit
Selection.ColumnWidth = 8.13
Rows("1:4").Select
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=-18
Columns("B:B").ColumnWidth = 8
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Columns("G:G").EntireColumn.AutoFit
Cells.Select
Selection.Copy
Windows("MACRO Sampler .xlsm").Activate
Sheets("Mon Adj").Select
Cells.Select
ActiveSheet.Paste
End Sub

 

[Fluff]

Is this line

Public strfilepath As String

at the very top of a module (before any code) and does it appear in any other modules?

 

[Kahlan1177]

Is this line

Public strfilepath As String

at the very top of a module (before any code) and does it appear in any other modules?

I do.
I have that line in another Module. I have 3 reports that i need to format, i was told by a friend to have 3 different modules. That's why i have the 3 IF statements calling different sub routines. so would the marco run if i merged all my 3 sub routines into one module??

 

[Fluff]

That's fine, but you should only have one declaration for the variable.
If you delete all but one of lines, does your code work?