Coding not crossing line3

[gauravkrgautam]

Hi All,

There is some problem in coding it goes to line 2 after line 3. I dont know why, might be somebody knows this..

Sub test()
Dim Last_Row As Double, File_Name As String, sht As Worksheet, Wb As Workbook
File_Name = Application.GetOpenFilename(filefilter:="Excel Files (*.xls*), .xls", Title:="Open Weekly Report - Step 1 of 8")
Workbooks.Open Filename:=File_Name
Last_Row = ActiveWorkbook.Sheets("report").Range("f" & Rows.Count).End(xlUp).Row
Set sht = ActiveWorkbook.Sheets("Report")
Union(sht.Range("F" & Last_Row), sht.Range("E" & Last_Row), sht.Range("H" & Last_Row)).Copy
End Sub

Regards,

 

[NARAYANK991]

Hi Gaurav ,

I did not face any problem ; the code runs to completion , and does what it is supposed to do.

Narayan

 

[gauravkrgautam]

Thanks Narayan,

Narayan actually i am working for automation of my report which includes:
1-open 1st file
2-copy some range
3-open 2nd file
4-paste values of 1st value in 2nd file
3.close 1st file

Honestly i want to become a ninja but still a newbie in vba from past 1 year.
Small things are always pulling my legs.
Regards,

 

[Hui]

Guarav

The code also runs fine for me in Excel 2010 & 2013

If you are stepping through the code using F8, It will not stop on the Dim line and so the first line will be the File_Name = line
then the Workbooks.open line

 

[gauravkrgautam]

I am using F8 keys to see step by step action and after opening file it again goes on line2 to :

File_Name = Application.GetOpenFilename(filefilter:="Excel Files (*.xls*), .xls", Title:="Open Weekly Report - Step 1 of 8")

Regards,

 

[gauravkrgautam]

Y it is happening coz i am a vba newbie and see each & every steps/action
pls suggest what should i do to proceed in my learning towards ninja.

Regards,

 

[NARAYANK991]

Hi Gaurav ,

I am not able to suggest anything much for the code you have posted ; try this and see if the results are different :

Sub test()
    Dim Last_Row As Double
    Dim File_Name As String
    Dim sht As Worksheet
    Dim Wb As Workbook
    Workbooks.Open Application.GetOpenFilename(filefilter:="Excel Files (*.xls*), .xls", Title:="Open Weekly Report - Step 1 of 8")
    Last_Row = ActiveWorkbook.Sheets("Report").Range("F" & Rows.Count).End(xlUp).Row
    Set sht = ActiveWorkbook.Sheets("Report")
    Union(sht.Range("F" & Last_Row), sht.Range("E" & Last_Row), sht.Range("H" & Last_Row)).Copy
End Sub

The only change is that the 2 statements have been combined in one.

Narayan

 

[Hui]

Thats what it should do
Pressing F8 ignores the Dim statements, it just jumps over them

 

[Hui]

I'd also suggest

Sub test()
    Dim Last_Row As Double
    Dim File_Name As String
    Dim sht As Worksheet
    Dim Wb As Workbook
    Workbooks.Open Application.GetOpenFilename(filefilter:="Excel Files (*.xls*), .xls", Title:="Open Weekly Report - Step 1 of 8")
    Set sht = ActiveWorkbook.Sheets("Report")
    Last_Row = sht.Range("F" & Rows.Count).End(xlUp).Row
    Union(sht.Range("F" & Last_Row), sht.Range("E" & Last_Row), sht.Range("H" & Last_Row)).Copy
End Sub