VBA, open concern folder directly and message display off

[ateebali]

I am using following code;

Sub t()

Dim sh As Worksheet, fPath As String, fName As String
Set sh = ActiveSheet
fPath = "ThisWorkbook.Path\Backup\"
fName = Application.GetOpenFilename("Excel Files (" & fPath & "*.xlsb)," & fPath & "*.xlsb")
Set wb = Workbooks.Open(fName)
wb.Sheets("Layout").Select
 Columns("BM:BO").Select
    Selection.EntireColumn.Hidden = False
    Range("BN5").Select
    ActiveSheet.Range("$A$5:$DZ$500").AutoFilter Field:=66
    Selection.EntireColumn.Hidden = True
    Range("BM5").Select
Range("B6:Q498").Copy sh.Range("B6")
wb.Close False




End Sub

#1 It is giving error message which we need press yes

A formula or sheet you want to move or copy contains the name 'Stitch', which already exist on the destination worksheet. Do you want to use this version of the name?
. To use the name as defined in the destination sheet, click Yes
. To rename the range referred to in formula or worksheet, click No, and enter a new name in Name Conflict dialog box.

I want this error not to display.

#2 Second thing, I want to open always the folder "Thisworkbook.path/Backup"
Currently it is opening another folder, then I need to select destination again and again

 

[JLGWhiz]

Re: Help in VB Code, open concern folder directly and message display off

Range("B6:Q498").Copy sh.Range("B6")

Don't know if you realize it, but this does not copy just the filtered rows. It copies hidden rows as well. To copy only the filtered rows it should be:

Range("B6:Q498").SpecialCells(xlCellTypeVisible).Copy sh.Range("B6")

To stop the message from appearing, you can put this statement before the line that causes it

Application.DisplayAlerts = False

And this statement immediately after the line that causes the message

Application.DisplayAlerts = True

 

[ateebali]

Re: Help in VB Code, open concern folder directly and message display off

Do you mean like this?

Sub t()

Dim sh As Worksheet, fPath As String, fName As String
Set sh = ActiveSheet
fPath = "ThisWorkbook.Path\Backup\"
fName = Application.GetOpenFilename("Excel Files (" & fPath & "*.xlsb)," & fPath & "*.xlsb")
Set wb = Workbooks.Open(fName)
wb.Sheets("Layout").Select
 Columns("BM:BO").Select
    Selection.EntireColumn.Hidden = False
    Range("BN5").Select
    ActiveSheet.Range("$A$5:$DZ$500").AutoFilter Field:=66
    Selection.EntireColumn.Hidden = True
    Range("BM5").Select
Range("B6:Q498").Copy
Application.DisplayAlerts = False
sh.Range ("B6")
Application.DisplayAlerts = True
wb.Close False




End Sub

 

[JLGWhiz]

Re: Help in VB Code, open concern folder directly and message display off

More like this

Sub t()
Dim sh As Worksheet, fPath As String, fName As String
Set sh = ActiveSheet
fPath = "ThisWorkbook.Path\Backup\"
fName = Application.GetOpenFilename("Excel Files (" & fPath & "*.xlsb)," & fPath & "*.xlsb")
Set wb = Workbooks.Open(fPath & fName)
wb.Sheets("Layout").Select
Application.DisplayAlerts = False
 Columns("BM:BO").Select
    Selection.EntireColumn.Hidden = False
    Range("BN5").Select
    ActiveSheet.Range("$A$5:$DZ$500").AutoFilter Field:=66
    Selection.EntireColumn.Hidden = True
    Range("BM5").Select
Range("B6:Q498").SpecialCells(xlCellTypeVisible).Copy sh.Range("B6")
Application.DisplayAlerts = True
wb.Close False
End Sub

 

[JLGWhiz]

Re: Help in VB Code, open concern folder directly and message display off

I think this is probably what you are looking for.

To get it to open the dialogue box in another directory besides the default directory you have to use ChDir method.

Sub t()
Dim sh As Worksheet, fPath As String, fName As String
Set sh = ActiveSheet
ChDir ThisWorkbook.Path & "\Backup"
fName = Application.GetOpenFilename("Excel Files (*.xlsb), *.xlsb")
Set wb = Workbooks.Open(fName)
wb.Sheets("Layout").Select
Application.DisplayAlerts = False
 Columns("BM:BO").Select
    Selection.EntireColumn.Hidden = False
    Range("BN5").Select
    ActiveSheet.Range("$A$5:$DZ$500").AutoFilter Field:=66
    Selection.EntireColumn.Hidden = True
    Range("BM5").Select
Range("B6:Q498").SpecialCells(xlCellTypeVisible).Copy sh.Range("B6")
Application.DisplayAlerts = True
wb.Close False
End Sub

 

[ateebali]

Re: Help in VB Code, open concern folder directly and message display off

Sorry sir, this is giving error
Any solution to open relative folder directly with the code;
thiworkbook.path/backup

 

[JLGWhiz]

Re: Help in VB Code, open concern folder directly and message display off

Sorry sir, this is giving error
Any solution to open relative folder directly with the code;
thiworkbook.path/backup

I am out of ideas, sorry.
Regards, JLG