Using VBA to open a workbook and find the first blank row

[DanR1245]

Hi-

I am trying to write some code in an excel workbook that will open another excel workbook and go to the first blank line. I will then take some fields from the first workbook and insert them in appropriate columns in the second workbook.

I am running into trouble trying to have VBA go to the first blank line in the second workbook that was open using VBA.

Here is the code I have so far. The second workbook is opening but it is not making the first cell in the first blank row the active cell.

Thanks!

........


Private Sub CheckBox134_Click()

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
'xlApp.ScreenUpdating = False
Set xlWB = xlApp.Workbooks.Open("file.xls")
'ActiveWorkbook.Close SaveChanges:=True
ActiveWorkbook.Activate

'Set oExcel = GetObject(, "Excel.Application")
'With x1WB.ActiveSheet

Sheets("file").Activate
Selection.End(xlDown).Select


End Sub

[just_jon]

Do you really mean the 1st empty row, or the next row down after the end of data?

Is there a column which will always have content if there is anything at all on the row?

[DRJ]

Hi - Welcome to the board

After you open the workbook put this

Sheets("Sheet1").Activate 'Change as needed
Range("A65536").End(xlUp).Offset(1,0).Select

[DanR1245]

Thanks. I get a "subscript out of range" with this code. The workbook opens as it should. Do I need to do something else to make it active?

What is happening is excel is taking me to the first blank row of the initial workbook I have open.

[just_jon]

Try changing "Sheet1" to your sheetname.

[DanR1245]

Yes, this has already been changed. But for some reason, VBA is not performing this action on the desired excel file. It is going to the first blank line of the file from which I ran the macro, not the opened file.

[Cbrine]

That's the way a macro works. It runs within the worksheet/workbook where is resides. If you want to run it on a different file, you need to reference that file. Try this.

Dim WS as worksheet

Set WS = workbooks("Your FileName").worksheets("Your WorkSheet")

ws.Activate 'Change as needed
ws.Range("A65536").End(xlUp).Offset(1,0).Select

[DRJ]

That's the way a macro works. It runs within the worksheet/workbook where is resides {snip}

Not really. Generally macros will run on the active workbook/worksheet. Not necessarily this workbook.