C
Carlee
Hi there, i am getting a runtime error 1004...that the file could not be
accessed. The code it below, and the line in question is:
sbFile.SaveAs Filename:=WshShell.SpecialFolders("Desktop") & "\Copreco
Reading.xlsx """
================================================
Sub ExportCoprecoReadingData()
Const sourceSheet = "Site Reading Log" ' source
Dim sourceBook As String
Dim destBook As String
Dim sourceRange As Range
Dim destRange As Range
Application.ScreenUpdating = False
sourceBook = ThisWorkbook.Name
'get the new workbook object
Set sbFile = Workbooks.Add ' create new book
destBook = ActiveWorkbook.Name
Windows(sourceBook).Activate
Worksheets(sourceSheet).Select
Set sourceRange = ActiveSheet.Rows("1:1")
Set destRange = Workbooks(destBook).Worksheets("Sheet1").Rows("1:1")
destRange.Value = sourceRange.Value
'change "A" to column with data in last row
Range("A" & Rows.Count).End(xlUp).Select
Set sourceRange = ActiveSheet.Rows(ActiveCell.Row & ":" & ActiveCell.Row)
Set destRange = Workbooks(destBook).Worksheets("Sheet1").Rows("2:2")
destRange.Value = sourceRange.Value
Set sourceRange = Nothing
Set destRange = Nothing
'get the desktop path, save and close new workbook
Set WshShell = CreateObject("WScript.Shell")
sbFile.SaveAs Filename:=WshShell.SpecialFolders("Desktop") & "\Copreco
Reading.xlsx """
sbFile.Close
Set WshShell = Nothing
Set sbFile = Nothing
End Sub
accessed. The code it below, and the line in question is:
sbFile.SaveAs Filename:=WshShell.SpecialFolders("Desktop") & "\Copreco
Reading.xlsx """
================================================
Sub ExportCoprecoReadingData()
Const sourceSheet = "Site Reading Log" ' source
Dim sourceBook As String
Dim destBook As String
Dim sourceRange As Range
Dim destRange As Range
Application.ScreenUpdating = False
sourceBook = ThisWorkbook.Name
'get the new workbook object
Set sbFile = Workbooks.Add ' create new book
destBook = ActiveWorkbook.Name
Windows(sourceBook).Activate
Worksheets(sourceSheet).Select
Set sourceRange = ActiveSheet.Rows("1:1")
Set destRange = Workbooks(destBook).Worksheets("Sheet1").Rows("1:1")
destRange.Value = sourceRange.Value
'change "A" to column with data in last row
Range("A" & Rows.Count).End(xlUp).Select
Set sourceRange = ActiveSheet.Rows(ActiveCell.Row & ":" & ActiveCell.Row)
Set destRange = Workbooks(destBook).Worksheets("Sheet1").Rows("2:2")
destRange.Value = sourceRange.Value
Set sourceRange = Nothing
Set destRange = Nothing
'get the desktop path, save and close new workbook
Set WshShell = CreateObject("WScript.Shell")
sbFile.SaveAs Filename:=WshShell.SpecialFolders("Desktop") & "\Copreco
Reading.xlsx """
sbFile.Close
Set WshShell = Nothing
Set sbFile = Nothing
End Sub