Unable to SAVE AS a wb

[Vaghela]

In the following code, I am getting error when saving the file...... Please help



Sub Del_Column()


Dim wb As Workbook
Dim wbName As String




wbName = "Book"


For Each wb In Application.Workbooks


If wb.Name Like wbName & "*" Then
Debug.Print wb.Name

wb.Sheets("Sheet1").Name = "D Track"
wb.Sheets("Sheet2").Name = "Comp Yes"


With wb.Sheets("D Track").Range("B:J,L:L,N:T,V:V,X:X,Z:AM,AO:AV").EntireColumn.Delete


wb.Sheets("Comp Yes").Range("B:I,K:K,M:N,P:S,U:U,W:W,Y:AK,AM:AP").EntireColumn.Delete


End With
End If

ActiveWorkbook.SaveAs Filename:="\\Desktop\\Daily Sheet" & Format(Now(), "DDMMMYY") & ".xlsx"


Next wb


End Sub

 

[ranman256]

use a drive letter:
C:\Users" & Environ("USERPROFILE") & "\Desktop\Daily Sheet" & Format(Now(), "DDMMMYY") & ".xlsx"

 

[Vaghela]

use a drive letter:
C:\Users" & Environ("USERPROFILE") & "\Desktop\Daily Sheet" & Format(Now(), "DDMMMYY") & ".xlsx"

Its not the drive letter as I am successfully running the code for other macro

Windows("A_report.xls").Activate
Sheets("Sheet1").Select
Range("A1").Select
ActiveWorkbook.SaveAs Filename:= _
"\\Desktop\Report " & Format(Now(), "DDMMYY") & ".xls"


The only difference in both the codes is on queried code I am using a wb with name starting Book "*" and for the above it is a specific named wb (A_report). Is this an issue? if yes how to resolve it

 

[Eric Golf]

Hi Vaghela,

What is the exact error you are getting? Without that everything is a guess.

I do note that your save statement is outside the If statement but inside the loop, so will attempt to save all workbooks processed as the same name?

Edit - It is also saving the ActiveWorkbook which may or may not equal wb.

Hope this helps,

Eric Golf.

 

[Fluff]

Try

Sub Del_Column()

Dim wb As Workbook
Dim wbName As String

wbName = "Book"

For Each wb In Application.Workbooks
    If wb.Name Like wbName & "*" Then
        Debug.Print wb.Name
        
        wb.Sheets("Sheet1").Name = "D Track"
        wb.Sheets("Sheet2").Name = "Comp Yes"
        
        wb.Sheets("D Track").Range("B:J,L:L,N:T,V:V,X:X,Z:AM,AO:AV").EntireColumn.Delete
        wb.Sheets("Comp Yes").Range("B:I,K:K,M:N,P:S,U:U,W:W,Y:AK,AM:AP").EntireColumn.Delete
    
        wb.SaveAs FileName:="\\Desktop\\Daily Sheet" & Format(Date, "DDMMMYY") & ".xlsx"

    End If
    


Next wb


End Sub

There was no need for the with statement (which was wrong anyway) & I've moved the Saveas inside the the If statement.

 

[Vaghela]

Try

Sub Del_Column()

Dim wb As Workbook
Dim wbName As String

wbName = "Book"

For Each wb In Application.Workbooks
    If wb.Name Like wbName & "*" Then
        Debug.Print wb.Name
        
        wb.Sheets("Sheet1").Name = "D Track"
        wb.Sheets("Sheet2").Name = "Comp Yes"
        
        wb.Sheets("D Track").Range("B:J,L:L,N:T,V:V,X:X,Z:AM,AO:AV").EntireColumn.Delete
        wb.Sheets("Comp Yes").Range("B:I,K:K,M:N,P:S,U:U,W:W,Y:AK,AM:AP").EntireColumn.Delete
    
        wb.SaveAs FileName:="\\Desktop\\Daily Sheet" & Format(Date, "DDMMMYY") & ".xlsx"

    End If
    


Next wb


End Sub

There was no need for the with statement (which was wrong anyway) & I've moved the Saveas inside the the If statement.

This has worked,,, thanks for quick response

 

[Fluff]

Glad we could help & thanks for the feedback