0

This macro uses a workbook name, but it changes sometimes. Is it possible to ignore it or adjust a different name? I tried to add it as string, but looks like I need something different.

Dim planavimas As String
planavimas = ActiveSheet.Name
Windows("Gamyba.xlsx").Activate
Range("A6:Y36").Select
Selection.Copy
Windows("PlanavimasET-19-01.xlsm").Activate  ' << (this workbook name changes) 
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
T.M.
  • 9,436
  • 3
  • 33
  • 57
Vytautas Lukošiūnas
  • 7
  • 5
  • 1
    Set a reference when you open it? – SJR Jan 29 '19 at 09:39
  • 2
    Firstly, you should avoid `Select` `Activate` `ActiveSheet` etc ( [see here for help on this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) ). Secondly, don't use `Windows` to access the workbook. If the code is in the workbook you want, use `ThisWorkbook`. If your code opens the workbokk, set a reference when it does so. If there is a pattern to the name, loop the `Application.Workbooks` collection and check for that pattern. – chris neilsen Jan 29 '19 at 09:49
  • Thanks everyone, I deleted first two code lines and used Chris Neilsen suggestion - ThisWorkbook. It works fine, simple and effective. – Vytautas Lukošiūnas Jan 29 '19 at 10:06

0 Answers0