0

I am a completely new to VBA

The goal of my code below is to open another excel workbook on a networked drive that's name changes with the month, copy and paste that data into thisworkbook, and close the file I just opened once its done with its task.

When I F8 through the VBA Editor this code actually works, as it in opens the excel file / copy pastes data, however I'm getting a yellow highlight around the filepath string that I made a variable when I try to actually run the macro (and it does not work).

The filepath is a concatenation of the text&cellreference&text within the Excel workbook, I'm wondering if that is causing the problem. The text is the filepath, the cell reference is the changing month. The month in the filepath is spelled out, aka C:...\location\November 2020 report.xlsx

Sub CopyDay1Report()

Dim Day1Report As Variant
Day1Report = Curtailment.Range("Q4")

'GVS1
Excel.Workbooks.Open (Day1Report)
Sheets("GVS1").Range("A1:M16").Copy

ThisWorkbook.Activate
Range("B27:N42").PasteSpecial xlPasteValuesAndNumberFormats

'GVS2
Excel.Workbooks.Open (Day1Report)
Sheets("GVS2").Range("A1:M16").Copy

ThisWorkbook.Activate
Range("B47:N62").PasteSpecial xlPasteValuesAndNumberFormats

'GVS3
Excel.Workbooks.Open (Day1Report)
Sheets("GVS3").Range("A1:M16").Copy

ThisWorkbook.Activate
Range("B66:N81").PasteSpecial xlPasteValuesAndNumberFormats

'GVS4
Excel.Workbooks.Open (Day1Report)
Sheets("GVS4").Range("A1:M16").Copy

ThisWorkbook.Activate
Range("B84:N99").PasteSpecial xlPasteValuesAndNumberFormats

Workbooks("Day1Report").Close SaveChanges:=False
Bryan
  • 1
  • 1
  • If `cellreference` is a date then VBA could be reading it as a number - 44186 for today's date as it's that many days since 1st Jan 1900. Try wrapping it within `Format` - `Format(cellreference, "dd-mmm-yy")` for example. – Darren Bartrup-Cook Dec 21 '20 at 08:09
  • I think `Day1Report` should be defined as `string`. Also, you use multiple times `Excel.Workbooks.Open (Day1Report)` without closing it. You can't open an already opened workbook, this will raise an error. – Foxfire And Burns And Burns Dec 21 '20 at 12:33
  • @Foxfire And Burns And Burns should I use Activate instead of Worksbooks.Open? – Bryan Dec 21 '20 at 19:26
  • 1
    You should avoid Select / Activate entirely. [See here](https://stackoverflow.com/q/10714251) – chris neilsen Dec 21 '20 at 20:46

0 Answers0