.
Yesterday the following macro worked fine (literally). I haven't done anything to the code or the workbook since.
No runs, no drips, no errors.
Today, workbook #1 that is doing the pasting to the other workbook, won't run the macro. It stops execution on this line :
Set wks = wkb.Worksheets("Database")
No error code ... just a message that says 'Execution stopped.'
So I did more research and simply tried changing the variables ... no luck. (see different variables listed below)
I deleted the sheet in WB#1, saved the workbook, re-opened and re-created the worksheet with the macro pasted fresh.
Same error message.
The sheet names are accurate. The workbook names are accurate. Soooooooo .... why won't it run today ?
I swear Excel is of the female gender. I know ... I know ... that was politically incorrect but I'm angry and venting !
ORIGINAL REFERENCE:
Dim DestWkb As Workbook
Dim DestWks As Worksheet
Set DestWkb = Workbooks.Open(VBA.Environ("UserProfile") & "\Desktop\FSA-Spreadsheet02.xlsm")
Set DestWks = DestWkb.Worksheets("Database")
Worksheets("Database").Activate
SECOND REFERENCE:
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Set wkb = Workbooks(VBA.Environ("UserProfile") & "\Desktop\FSA-Spreadsheet02.xlsm")
Set wks = wkb.Worksheets("Database")
wks.Activate
COMPLETE MACRO:
Sub CopyUsedRange()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim DestWkb As Workbook
Dim DestWks As Worksheet
Dim erow As Long
Dim Last As Long
On Error Resume Next
Dim Msg As String, Title As String
Dim Config As Integer, Ans As Integer
Msg = "Are you sure ?"
Title = "Transfer Verification"
Config = vbYesNo + vbExclamation
Ans = MsgBox(Msg, Config, Title)
If Ans = vbYes Then GoTo letsDoIt
If Ans = vbNo Then Exit Sub
letsDoIt:
Set sh = Sheets("Database")
Set DestWkb = Workbooks.Open(VBA.Environ("UserProfile") & "\Desktop\FSA-Spreadsheet02.xlsm")
Set DestWks = DestWkb.Worksheets("Database")
If Sheet3.Range("A1").Value = "" Then
MsgBox "The database is empty. No records to transfer." & vbCrLf & _
"Transferring empty data will corrupt the database." & vbCrLf & _
"Cancelling request.", vbOKOnly, "Transfer Error"
ActiveWorkbook.Save
ActiveWorkbook.Close
Exit Sub
End If
Application.ScreenUpdating = False
If sh.UsedRange.Count > 1 Then
Last = lastRow(DestSh)
'Workbooks.Open VBA.Environ$("UserProfile") & "\Desktop\FSA-Spreadsheet02.xlsm"
Worksheets("Database").Activate
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
sh.UsedRange.Copy
ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveSheet.Visible = xlSheetHidden
Sheets("Main Menu").Range("B3").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False
End If
ClearData
Application.ScreenUpdating = True
MsgBox "Data transferred to other workbook.", vbOKOnly, "Saving data ..."
End Sub
Worksheet("Database") is hidden in the other workbook. This wasn't an issue previously. ????
Bookmarks