New to VBA and need help with a macro

Kahlan1177

New Member
Joined
Oct 4, 2018
Messages
6
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?


Code:
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,302
Office Version
2019, 2016, 2013
Platform
Windows
what is the bug message ?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,882
Office Version
365
Platform
Windows
You are declaring strfilepath as public but then also declaring it again in the first first code.
Remove
Code:
Dim strfilepath as String
from any code & just leave the Public declaration
 

Kahlan1177

New Member
Joined
Oct 4, 2018
Messages
6
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

New Member
Joined
Oct 4, 2018
Messages
6
Hi,
If i remove that line of code the macro stops at strfilepath = strPath & strfile and gives me a "ambiguous name detected" message.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,882
Office Version
365
Platform
Windows
That sounds as though you still have more than one declaration of strfilepath
 

Kahlan1177

New Member
Joined
Oct 4, 2018
Messages
6
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,882
Office Version
365
Platform
Windows
Is this line
Code:
Public strfilepath As String
at the very top of a module (before any code) and does it appear in any other modules?
 

Kahlan1177

New Member
Joined
Oct 4, 2018
Messages
6
Is this line
Code:
Public strfilepath As String
at the very top of a module (before any code) and does it appear in any other modules?
I do. :eek:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,882
Office Version
365
Platform
Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,984
Messages
5,447,700
Members
405,463
Latest member
Tommy5

This Week's Hot Topics

Top