0

I have run this macro since I remember and it never failed before, today it failed to copy 4 worksheets into a new file, returning a "Permission denied" error

There's no many information about this issue, so what I've tried is pretty much to check that I am running everything on admin user, to run Excel as administrator. However, it only fails with .xlsm files, the others work fine

The code fails on the first line, when the Copy command is used. It is supposed to create a new workbook with those sheets copied. Instead, the "Permission denied" error shows, and at the end it shows ": \name.temp" where name starts with 'VB' and some numbers after that, the numbers always change whenever I try it again.

Worksheets(Array("POS", "VAL", "COL2(USD)", "COL2(PS)")).Copy 
Sheets("POS").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues
Sheets("VAL").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues
Sheets("COL2(USD)").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues
Sheets("COL2(USD)").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues

Does anyone know how to fix this?

Carlos Casio
  • 79
  • 9
  • What line is the error happening on? Also, you shouldn't be using select statements. see... https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – pgSystemTester Jul 03 '19 at 03:30
  • I'm sorry, it's on the first line, where I array the worksheets and tell to copy them. It doesn't create any workbook and it alwasy says "Permission denied: \name.temp" where name it's always some numbers and letters starting with 'VB' – Carlos Casio Jul 03 '19 at 17:05
  • So you've lost write permissions to a folder, and now Excel cannot create a temp file for the new workbook. Does it give you full path to that folder? If it does not, grab [procmon](https://learn.microsoft.com/en-us/sysinternals/downloads/procmon) and find out yourself. – GSerg Jul 03 '19 at 17:11
  • It doesn't give me any full paths. I have not used process monitor so far, so I will download it and try this solution. Is it possible for this to happen even though no changes were made from one day to another? @GSerg – Carlos Casio Jul 03 '19 at 17:44
  • @CarlosCasio It's about changes to your file system and/or Excel default folders paths, not the macro. – GSerg Jul 03 '19 at 18:57
  • @GSerg precisely, I meant there were no changes on aything, not in Windows nor in Excel. Actually it only fails when I use .xlsm files, and it doesn't matter if I try to copy the sheets with or without a macro, it will show the same error. It seems really weird to me, I will chec the prcomon tho. – Carlos Casio Jul 03 '19 at 19:41

0 Answers0