0

here is my code that is failing

Dim OpenFileName As String
Dim wb As Workbook, wb2 As Workbook
Dim lastrow
'Select and Open workbook
OpenFileName = Application.GetOpenFilename(",*.csv")
If OpenFileName = "False" Then Exit Sub
Set wb = Workbooks(OpenFileName)
Set wb2 = Workbooks("MasterLogFile.xlsm")

MsgBox OpenFileName
With wb
'lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Range("A3:ME" & lastrow).Copy
.Sheets("Log Imports").Range("A7").Paste
End With

it fails on the set wb line, what am i doing wrong?

Vityata
  • 42,633
  • 8
  • 55
  • 100
DanM
  • 185
  • 7
  • The GetOpenFilename method show the Open dialog, but does not open the file (it just returns the filename selected). As answered by Vityata, you have to open (and close) it afterward. You got an error because you are trying to get a reference to a workbook not (yet) opened. – Vincent G Aug 31 '17 at 12:51

1 Answers1

1

Change the code to this one:

Set wb = Workbooks.Open(OpenFileName)

Then close it at the end like this:

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/workbook-close-method-excel

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • now im getting a run time error 438 on this line lastrow = .Cells(Rows.Count, "A").End(xlUp).Row – DanM Aug 31 '17 at 12:53
  • write `WorkSheets(1).Range("A3:ME" & lastrow).Copy` instead of just `Range...` Or see here - https://stackoverflow.com/questions/12288588/excel-vba-error-438-object-doesnt-support-this-property-or-method – Vityata Aug 31 '17 at 12:55
  • but my error is the lastrow line, the locals window says the expression lastrow is empty. what am i doing wrong? – DanM Aug 31 '17 at 13:01
  • @DanM - quite a few things... Write `Dim lastrow as Long` on top as well. – Vityata Aug 31 '17 at 13:03
  • so i dim the variable correctly but still erroring out on the LastRow setting. what is wrong with this statement? LastRow = .Cells(Rows.Count, "A").End(xlUp).Row – DanM Aug 31 '17 at 13:06
  • 1
    @DanM - you are refering only the workbook. You have to refer the worksheet as well. Check the link in my comment. Something like this: `lastrow = wb.worksheets(1).Cells(wb.worksheets(1).Rows.Count, "A").End(xlUp).Row` – Vityata Aug 31 '17 at 13:06