CODEWITHSUNDEEP
excelvba
davids1985
davids1985

Reputation: 27

how save changes and close without error using vba

I am new in using vba and this is my first attempt to write code.

The first workbook, MACRO2018, is my calculation template. Into MACRO2018 I make a calculation and save it every time with a different name. The second workbook UzklausulenteleGeras is where I paste and store all summaries of these calculations.

The code which I have copies and pastes all information as I require, but UzklausulenteleGeras does not save and does not close. In the end I get the error

Microsoft excel cannot access the file 'C:\C788DF00'. There are several possible reasons:

I tried many workbook close and save combinations, but got the same error.

So, i'm waiting your comments and thanks in advance.

Private Sub Uzklausa_Click()

   Workbooks.Open ("C:\UzklausulenteleGeras.xlsx")

   Workbooks("UzklausulenteleGeras").Worksheets("2018").Range("B" & Rows.Count).End(xlUp).EntireRow.Select
   Selection.Copy
   Selection.Insert Shift:=xlDown
   Workbooks("UzklausulenteleGeras").Worksheets("2018").Range("B" & Rows.Count).End(xlUp).EntireRow.Select
   Application.CutCopyMode = False
   Selection.ClearContents

   ThisWorkbook.Worksheets("Skaiciavimai").Range("B2:U2").Copy

   Workbooks("UzklausulenteleGeras").Worksheets("2018").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
   Application.CutCopyMode = False
   Application.ScreenUpdating = True

   ActiveWorkbook.Close SaveChanges:=True

End Sub

Upvotes: 0

Views: 2995

Answers (1)

Cindy Meister
Cindy Meister

Reputation: 25673

Begin by declaring objects to represent the various workbooks. It's dangerous to rely on ActiveWorkbook - it may not be the workbook you think it is! Using a Workbook object you can always be sure which workbook is being acted on.

Private Sub Uzklausa_Click()
   Dim wbUzkl as Workbook

   Set wbUzkl = Workbooks.Open("C:\UzklausulenteleGeras.xlsx")

   wbUzkl.Worksheets("2018").Range("B" & Rows.Count).End(xlUp).EntireRow.Select
   Selection.Copy
   Selection.Insert Shift:=xlDown
   wbUzkl.Worksheets("2018").Range("B" & Rows.Count).End(xlUp).EntireRow.Select
   Application.CutCopyMode = False
   Selection.ClearContents

   ThisWorkbook.Worksheets("Skaiciavimai").Range("B2:U2").Copy

   wbUzkl.Worksheets("2018").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
   Application.CutCopyMode = False
   Application.ScreenUpdating = True

   wbUzkl.Close SaveChanges:=True
End Sub

Upvotes: 0

Related Questions