0

I am relatively new to programming but I will try to explain my issue as best as I can.

I have multiple VBA modules saved in the personal Excel workbook that copy a sheet from a workbook saved on a server and paste it into the active workbook (saved on same server) when pressing assigned buttons on the ribbon.

This works perfectly until I try to 'saveas' to a different location on the same server. Then when hitting the ribbon buttons I get a"permission denied" error message and a .tmp is created in the location I am trying to save to.

I have attached the code below. Sorry if the formatting is off.

Any help would be much appreciated

Blockquote

Public Sub TEST()

    Dim sourceBook As Workbook
    Application.ScreenUpdating = False
    Set targetBook = ActiveWorkbook
    Set sourceBook = Workbooks.Open("\\serv01\company\TEST\TEST.xlsx")
    sourceBook.Sheets("TEST").Copy Before:=targetBook.Sheets(targetBook.Sheets.Count - 1)
    sourceBook.Close
    Application.ScreenUpdating = True
    Sheets(targetBook.Sheets.Count - 2).Select
        Range("A6:A10").Select
    Selection.insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1:A5").Select
    Selection.Delete Shift:=xlUp
    Range("A1:A5").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Sheets(targetBook.Sheets.Count).Select
    Range("A1:F5").Select
    Selection.Copy
    Sheets(targetBook.Sheets.Count - 2).Select
    Range("A1:F5").Select
    ActiveSheet.Paste
    Range("C3").Value = "TEST"
    SendKeys "{ESC}"
    Range("A6").Select
End Sub
Zack E
  • 696
  • 7
  • 23
craig_wilson1996
  • 1
  • 1
  • 1
    Sounds like you are lacking write privileges for the location on the server. Also your code doesn't save the workbook. And, most importantly: Have a look at [this](https://stackoverflow.com/a/10717999/8769365), to avoid using Select and Activate. – Nacorid Jul 01 '19 at 15:25
  • Hi Thanks for the response, I have full privileges for the server. The code is only designed to copy a sheet from the server. I only get the error when I try to save the workbook manually then run the macro again. – craig_wilson1996 Jul 01 '19 at 15:32
  • What happens if you manually copy the sheet and try to save the workbook? Because I don't see anything in the code that would prevent you from saving. – Nacorid Jul 01 '19 at 15:35
  • Saving the workbook is fine. I get the error when running the above macro from the saved workbook. – craig_wilson1996 Jul 01 '19 at 15:38
  • Now I'm confused. What line is throwing the error? – Nacorid Jul 01 '19 at 15:46
  • Line 5 starting: "sourceBook.Sheets("TEST")". I will try and clarify. I have a server that has a template workbook saved on it. When I first open this template and run the macro shown above it works as intended by copying a sheet from a different workbook on the server and applying formatting changes . However, if I save the file under a different name and try to run the macro again I get the "permission denied" error. – craig_wilson1996 Jul 01 '19 at 15:54
  • Can you manually copy the sheet after saving? – Nacorid Jul 01 '19 at 15:59
  • Yes, That works fine. – craig_wilson1996 Jul 01 '19 at 16:03

0 Answers0