0

This code works great and is called in the Print code, but I can't figure how to then Save and Close this workbook. The Inventory workbook Saves and Closes itself with Worksheet_Change code, but this workbook just sticks at the last direction given before opening the Inventory file. If there is no solution, I will use a button to Save and Close, which works fine but I know will cause files to be left open and confuse users (mostly farm laborers). I have tried several ideas, such as Exit Sub and adding a separate macro that, in order, calls CopyAndPasteToTracking then SaveAndClose. I think that the End Sub command is being ignored because I can get no other code to execute after the last line before End Sub (except for using button to run another macro). This is the end of a volunteer project I started in 2017 and had never used VBA before, so I am very appreciative of the help y'all have given me over the years, not only directly through stackoverflow but also as a wonderful reference. Anyway, this is my last weird issue that online research or looking through my books doesn't address, so I sure hope that someone knows an answer.

Sub CopyPasteToTracking()

Sheets("Export Table").Select
Range("$A$1:$K$" & Range("M1")).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sales Record").Select
Range("$A$10").Select

Workbooks.Open Filename:= _
    "C:\Users\Michelle\Documents\Maple Inventory System\YYYY\YYYY SYRUP MANAGEMENT\test 
YYYY SYRUP INVENTORY TRACKING.xlsm"
ActiveWindow.ScrollWorkbookTabs Sheets:=5
Sheets("Sales").Select
Range("$B$" & (1 + Range("Y1"))).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
    
End Sub

In case it helps to know, I also tried adding ThisWorkbook.Save and ThisWorkbook.Close after the command in Print procedure to call the macro and it didn't do either action.

Michelle Trudeau
  • 67
  • 10
  • 2
    Try to [avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and [refer to each sheet](https://www.techrepublic.com/article/10-ways-to-reference-excel-workbooks-and-sheets-using-vba/) instead of using the active sheet – cybernetic.nomad Apr 25 '23 at 21:41
  • Thank you, but I do not believe that's the problem because I got the same behavior when I ended that part of the code at "copy". I added the rest so that users would back on the sheet they started on. – Michelle Trudeau Apr 25 '23 at 22:00
  • 2
    If you just stop using things like `Select`, `ActiveWindow`, `Selection`, `Copy`, `Paste` and unqualified ranges and instead look into other options, the sheet wouldn't have to change in the first place, and it would be much faster. – braX Apr 25 '23 at 22:37
  • Thank you, Brax. If I remove the second part of the code to (go to the other workbook and paste) and instead close this workbook, it closes the workbook. If I rewrite it, I get the same behavior, and the users action will meanwhile getting the printed pages in hand, so I decided to stick with language that someone would be more likely to understand, even though I doubt it will be seen again. – Michelle Trudeau Apr 25 '23 at 22:48
  • before you open the other workbook assign the activeworkbook to a variable (ex. mywb) then at the end after the paste use `mywb.close 1` – k1dr0ck Apr 26 '23 at 07:27
  • Thank you, k1dr0ck, but then the rest of the code did not run. I have ended up with the most forceful approach of using application.quit – Michelle Trudeau Apr 26 '23 at 11:17

1 Answers1

0

I have decided to force it to close, which works fine and won't hurt office users who might have other workbooks open. Reason for this approach is that no user has ever posted their sales per the quick and easy instructions, so I have had to do it each year prior to files turnover for the new year. This solves that issue.

Sub CopyPasteToTracking()

Sheets("Export Table").Select
Range("$A$1:$K$" & Range("M1")).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sales Record").Select
Range("$A$9").Select

Workbooks.Open Filename:= _
    "C:\Users\Michelle\Documents\Maple Inventory System\YYYY\YYYY SYRUP MANAGEMENT\test YYYY SYRUP INVENTORY TRACKING.xlsm"
ActiveWindow.ScrollWorkbookTabs Sheets:=5
Sheets("Sales").Select
Range("$B$" & (1 + Range("Y1"))).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
    
For Each w In Application.Workbooks
    w.Save
    Next w
    Application.Quit

End Sub

Michelle Trudeau
  • 67
  • 10