I have some data in my excel file and based on that data I'm using a macro for generating the report which should be saved in the place specified by path provided by the user. On my laptop, with Win10 everything is working fine, but on PC there is an error when we try to generate a report. Instead of saving the report in provided place excel is asking me to save data in "Book16" as shows the screenshot below. Do I have no idea why?
Here is the code of macro responsible for creating the report:
Sub Nationalreports()
Dim sh1 As Worksheet, N As Long
Dim st As String
Dim wbUnSaved As Workbook
Dim wbSaved As Workbook
Dim RedemptiontypeIncHdgs As Range
Dim RedemptiontypeExcHdgs As Range
Dim Fr As Long, LR As Long
Dim vaFiles As Variant
Dim i As Long
Dim wbkToCopy As Workbook
Dim ws As Worksheet, strFile As String
Dim File_path As String
Dim qry As QueryTable
Dim FilNams As Variant
Dim FilNamCntr As Long
Dim strQryName As String
Dim lastrow As Long
Application.ScreenUpdating = False
If Range("E8").Value = 0 Then
MsgBox "Please Specify FilePath", vbExclamation, "Please Specify
FilePath"
Range("E8").Activate
Exit Sub
End If
File_path = Sheets("Control").Range("E8").Value
Set wbksaved = ActiveWorkbook
MsgBox "Please Select MVRT Reports", vbInformation, "Select Files"
FilNams = Application.GetOpenFilename(FileFilter:="CSV Files
(*.csv),*.csv", _
Title:="Select Textfile to
Import", _
MultiSelect:=True)
If TypeName(FilNams) = "Boolean" Then
MsgBox "No Files Selected", vbExclamation, "No Files Selected"
Exit Sub
Else
End If
For FilNamCntr = LBound(FilNams) To UBound(FilNams)
FilNams(FilNamCntr) = "TEXT;" & FilNams(FilNamCntr)
Next FilNamCntr
For FilNamCntr = LBound(FilNams) To UBound(FilNams)
Sheets("Data").Cells.NumberFormat = "@"
Set wbkToCopy = Workbooks.Add
With ActiveSheet
If .Range("A" & Rows.Count).End(xlUp).Row = 1 Then
lastrow = 1
Else
lastrow = .Range("A" & Rows.Count).End(xlUp).Row + 1
End If
Set qry = .QueryTables.Add(Connection:=FilNams(FilNamCntr), _
Destination:=.Range("A" & lastrow))
With qry
.Name = "Filename"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End With
'-------------------------------------------------------------------------------------------
'NEW CODE:
'-------------------------------------------------------------------------------------------
'progress
'Rows("1:1").Delete
'ActiveSheet.UsedRange.Columns("A:T").SpecialCells(xlCellTypeVisible).Copy
'wbksaved.Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Offset(0).PasteSpecial Paste:=xlPasteValues
ActiveSheet.UsedRange.Columns("A:T").SpecialCells(xlCellTypeVisible).Select
Selection.Copy
'wbksaved.Sheets("Data").Activate
'Range("A1").Select
'ActiveSheet.Paste
wbksaved.Sheets("Data").Paste
'ERROR IS HERE ^^^^^^^^^^^^^
'-------------------------------
'new:
Application.CutCopyMode = False
'-------------------------------
Application.DisplayAlerts = False
ActiveWorkbook.Close False
Application.DisplayAlerts = True
Next FilNamCntr
Set wbkToCopy = Workbooks.Add
wbkToCopy.Sheets(1).Name = "Duplicates,Invalid"
wbkToCopy.Worksheets.Add().Name = "Breakdown"
wbkToCopy.Worksheets.Add().Name = "Summary"
wbksaved.Sheets("Redemption").UsedRange.Columns("C:D").Copy
Sheets("Duplicates,Invalid").Range("A1").PasteSpecial Paste:=xlPasteValues
'-------------------------------
'new:
Application.CutCopyMode = False
'-------------------------------
' Duplicates & Invalids Sheet
Sheets("Duplicates,Invalid").Activate
Columns("B").Cut Destination:=Columns("F")
Columns("A").Cut Destination:=Columns("B")
Range("B1").Value = "Duplicate Codes"
Range("F1").Value = "Invalid Codes"
Range("A2").Value = "1"
Range("A3").Value = "2"
On Error Resume Next
Cells(2, 1).AutoFill Destination:=Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row), Type:=xlFillSeries
Range("E2").Value = "1"
Range("E3").Value = "2"
On Error Resume Next
Cells(2, 5).AutoFill Destination:=Range("E2:E" & Range("F" & Rows.Count).End(xlUp).Row), Type:=xlFillSeries
Range("A1").Value = "Nr."
Range("E1").Value = "Nr."
Columns("A:F").EntireColumn.AutoFit
Columns("A:F").HorizontalAlignment = xlCenter
Cells.Interior.Pattern = xlSolid
Cells.Interior.TintAndShade = -0.0499893185216834
Range("A1:B1,E1:F1").Interior.ThemeColor = xlThemeColorLight1
Range("A:B,E:F").Borders.LineStyle = xlContinuous
Range("A1:B1,E1:F1").Font.ThemeColor = xlThemeColorDark1
Range("A1:B1,E1:F1").Font.Bold = True
Application.Goto Reference:=Range("A1"), Scroll:=True
' Breakdown Sheet
Sheets("Breakdown").Activate
Cells.NumberFormat = "@"
wbksaved.Sheets("MVRT").UsedRange.Columns("A:D").SpecialCells(xlCellTypeVisible).Copy
wbkToCopy.Sheets("Breakdown").Cells(Rows.Count, "A").End(xlUp).Offset(0).PasteSpecial xlPasteValues
wbksaved.Sheets("MVRT").UsedRange.Columns("F:F").SpecialCells(xlCellTypeVisible).Copy
wbkToCopy.Sheets("Breakdown").Cells(Rows.Count, "E").End(xlUp).Offset(0).PasteSpecial xlPasteValues
wbksaved.Sheets("MVRT").UsedRange.Columns("H:H").SpecialCells(xlCellTypeVisible).Copy
wbkToCopy.Sheets("Breakdown").Cells(Rows.Count, "F").End(xlUp).Offset(0).PasteSpecial xlPasteValues
wbksaved.Sheets("MVRT").UsedRange.Columns("N:N").SpecialCells(xlCellTypeVisible).Copy
wbkToCopy.Sheets("Breakdown").Cells(Rows.Count, "G").End(xlUp).Offset(0).PasteSpecial xlPasteValues
wbksaved.Sheets("MVRT").UsedRange.Columns("S:S").SpecialCells(xlCellTypeVisible).Copy
wbkToCopy.Sheets("Breakdown").Cells(Rows.Count, "H").End(xlUp).Offset(0).PasteSpecial xlPasteValues
wbksaved.Sheets("MVRT").UsedRange.Columns("K:K").SpecialCells(xlCellTypeVisible).Copy
wbkToCopy.Sheets("Breakdown").Cells(Rows.Count, "I").End(xlUp).Offset(0).PasteSpecial xlPasteValues
wbksaved.Sheets("MVRT").UsedRange.Columns("J:J").SpecialCells(xlCellTypeVisible).Copy
wbkToCopy.Sheets("Breakdown").Cells(Rows.Count, "J").End(xlUp).Offset(0).PasteSpecial xlPasteValues
wbksaved.Sheets("MVRT").UsedRange.Columns("M:M").SpecialCells(xlCellTypeVisible).Copy
wbkToCopy.Sheets("Breakdown").Cells(Rows.Count, "K").End(xlUp).Offset(0).PasteSpecial xlPasteValues
wbksaved.Sheets("MVRT").UsedRange.Columns("Q:Q").SpecialCells(xlCellTypeVisible).Copy
ActiveSheet.Paste Destination:=Worksheets("Breakdown").Range("L:L")
'-------------------------------
'new:
Application.CutCopyMode = False
'-------------------------------
'Range("O:P").Delete
Range("A1").Value = "UUID"
Range("B1").Value = "Security Code"
Range("C1").Value = "Customer Code"
Range("D1").Value = "Country Code"
Range("E1").Value = "Salesforce Id"
Range("F1").Value = "Merchant Name"
Range("G1").Value = "Unit Status"
Range("H1").Value = "Redemption Status"
Range("I1").Value = "Expires At"
Range("J1").Value = "Expired"
Range("K1").Value = "Suspended"
Range("L1").Value = "Redemption Date"
'Range("M:M").Replace What:="Invalid Rights", Replacement:="Other Country"
Range("B:L").Sort Key1:=Range("I:I"), Order1:=xlAscending, Header:=xlYes
Range("A:L").EntireColumn.AutoFit
Range("A1:L1").HorizontalAlignment = xlCenter
Cells.Interior.Pattern = xlSolid
Cells.Interior.TintAndShade = -0.0499893185216834
Range("A1:L1").Interior.ThemeColor = xlThemeColorLight1
Range("A:L").Borders.LineStyle = xlContinuous
Range("A1:L1").Font.ThemeColor = xlThemeColorDark1
Rows("1:1").Font.Bold = True
Application.Goto Reference:=Range("A1"), Scroll:=True
'Summary Sheet
Sheets("Summary").Activate
Range("C9").Value = "Country"
Range("C10").Value = "Merchant Name"
Range("C11").Value = "Type"
Range("C16").Value = "Redemption Type"
Range("C17").Value = "Invalid"
Range("C18").Value = "Duplicates"
Range("C19").Value = "Suspended"
Range("C20").Value = "Voucher Expired"
Range("C21").Value = "Payment Invalid"
Range("C22").Value = "Payment Refunded"
Range("C23").Value = "Redeemed"
Range("C24").Value = "Total Codes Sent In"
Range("D9").FormulaR1C1 = "=Breakdown!R[-7]C[0]"
Range("D10").FormulaR1C1 = "=Breakdown!R[-8]C[2]"
Range("D11").FormulaR1C1 = "Offsite Redemptions"
Range("D16").FormulaR1C1 = "No."
Range("D17").FormulaR1C1 = "=COUNTA('Duplicates,Invalid'!C[2])-1"
Range("D18").FormulaR1C1 = "=COUNTA('Duplicates,Invalid'!C[-2])-1"
Range("D19").Formula = "=COUNTIFS(Breakdown!K:K,""*true*"",Breakdown!H:H,""*Forced redeemable*"")"
'Range("D19").AutoFill Destination:=Range("D19:D25"), Type:=xlFillCopy
Range("D20").Formula = "=COUNTIFS(Breakdown!J:J,""*true*"",Breakdown!K:K,""*false*"",Breakdown!G:G,""*collected*"",Breakdown!H:H,""*Forced redeemable*"")"
'payment not received:
Range("D21").Formula = "=COUNTIFS(Breakdown!K:K,""*false*"",Breakdown!G:G,""*resigned*"",Breakdown!H:H,""*Forced redeemable*"") + COUNTIFS(Breakdown!K:K,""*false*"",Breakdown!G:G,""*pending*"",Breakdown!H:H,""*Forced redeemable*"")"
'payment refunded:
Range("D22").Formula = "=COUNTIFS(Breakdown!G:G,""*deleted*"",Breakdown!K:K,""*false*"",Breakdown!H:H,""*Forced redeemable*"")"
Range("D23").Formula = "=COUNTIF(Breakdown!H:H,""*redeemed*"")"
'sum-formula:
Range("D24").Formula = "=COUNTA(Breakdown!A:A)-1 + SUM(D17:D18)"
Range("C:D").Copy
Range("C:C").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("A:B,E:F").ColumnWidth = 26
Range("C:D").ColumnWidth = 63.29
Range("C:D").HorizontalAlignment = xlCenter
Cells.Interior.Pattern = xlSolid
Cells.Interior.TintAndShade = -0.0499893185216834
Range("C1:D5,C9:C11,C16:D16,C24:D24").Interior.ThemeColor =
xlThemeColorLight1
Range("D9:D11,C16:D24").Borders.LineStyle = xlContinuous
Range("C9:C11,C16:D16,C24:D24").Font.ThemeColor = xlThemeColorDark1
Range("C9:D11,C16:D24").Font.Bold = True
Range("C3:D3").Merge True
Dim myR As Range
Set myR = Range("C3:D3")
wbksaved.Sheets("Control").Shapes("Groupon Logo").Copy
Range("C3:D3").PasteSpecial xlPasteFormats
'-------------------------------
'new:
Application.CutCopyMode = False
'-------------------------------
Selection.ShapeRange.ScaleWidth 1, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 1, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.IncrementLeft (myR.Width - Selection.ShapeRange.Width) / 2
Application.Goto Reference:=Range("A1"), Scroll:=True
Country = Range("D9").Value
'Merchant_ID = Range("D10").Value This was DELETED on Lorene request
Merchant_Name = Range("D10").Value
dt = Format(CStr(Now), "dd_mm_yyyy_hh_mm_ss")
File_Name = File_path & "\" & "Report" & " " & Merchant_Name & " " &
Merchant_ID & " " & dt & ".xlsx"
ActiveWorkbook.SaveAs Filename:=File_Name, FileFormat:=51
ActiveWorkbook.Close
Application.DisplayAlerts = False
Sheets("Data").Cells.Delete
Application.DisplayAlerts = True
Sheets("Control").Select
MsgBox "Report Created", vbInformation, "Report Created"
Application.ScreenUpdating = True
End Sub
