Vista - Finding & using the next available row in a spreadsheet

I have two sheets within a workbook called Jobs

The two sheets are called Concrete & Database

I need to archive cell data from Concrete & piggyback it onto the next available row in Database (currently this worksheet has about 1100 rows of active data).

Therefore I would like the macro coding to first find the next available row in Column A (call it row n) in Database & using that address move the following data from Concrete to to the relevant cells in Row n of Database:

Concrete(E1) to Database(An)
Concrete(B3) to Database(Bn)
Concrete(B1) to Database(Cn)
Concrete(E5) to Database(Gn)
Concrete(B55) to Database(Jn)
Concrete(E2) to Database(Kn)

Then save the workbook Jobs

Thank You

"lakelands" <guest@xxxxxx-email.com> wrote in message
news:686a233bd668c55d0d4add4fc0491304@xxxxxx-gateway.com...What is the reason for wanting to do this task in VBScript (the name of this
newsgroup), rather than Excel's flavor of Visual Basic for Applications
(VBA)? VBScript would have to do it by manipulating the Excel.Application
object, or by using ADO to manipulate the .XLS file. If you want to do it
from VBA, I would suggest posting to Microsoft.Public.Excel.Programming.
There seems to be no Microsoft newsgroup with both Excel and VBA in its
name.

-Paul Randall

lakelands wrote:
The only way I know to find the first blank row is to loop through all of
them until you find a blank in one of the columns that should always have a
value if the row is used. Note below that what you call cell B3 will be row
3, column 2, which is Cell(3, 2). If I understand your requirements, this
should work:
=========
Option Explicit

Dim objExcel, strExcelPath, objSheet1, objSheet2
Dim intRow

' Bind to Excel object.

' Specify spreadsheet file.
strExcelPath = "c:\rlm\Scripts\TestLines.xls"

' Open spreadsheet.
Set objExcel = CreateObject("Excel.Application")
objExcel.WorkBooks.Open strExcelPath
' Open worksheet "Concrete".
Set objSheet1 = objExcel.ActiveWorkbook.Worksheets("Concrete")
' Open worksheet "Database".
Set objSheet2 = objExcel.ActiveWorkbook.Worksheets("Database")

' Iterate through the rows of the worksheet "Database" until
' blank value found in column A.
intRow = 1
Do While objSheet2.Cells(intRow, 1).Value <> ""
intRow = intRow + 1
Loop

' Copy data from "Concrete" to "Database".
' Concrete(E1) to Database(An)
objSheet2.Cells(intRow, 1).Value = objSheet1.Cells(1, 5).Value
' Concrete(B3) to Database(Bn)
objSheet2.Cells(intRow, 2).Value = objSheet1.Cells(3, 2).Value
' Concrete(B1) to Database(Cn)
objSheet2.Cells(intRow, 3).Value = objSheet1.Cells(1, 2).Value
' Concrete(E5) to Database(Gn)
objSheet2.Cells(intRow, 7).Value = objSheet1.Cells(5, 5).Value
' Concrete(B55) to Database(Jn)
objSheet2.Cells(intRow, 10).Value = objSheet1.Cells(55, 2).Value
' Concrete(E2) to Database(Kn)
objSheet2.Cells(intRow, 11).Value = objSheet1.Cells(2, 5).Value

' Save spreadsheet.
objExcel.ActiveWorkbook.SaveAs strExcelPath

' Close workbook and quit Excel.
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit

Wscript.Echo "Done"
========
This prompts you to verify you want to replace the existing spreadsheet when
it saves. This probably can be avoided.

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

In VBA you would typically use something like this

dim rw as range
set rw =
shtDatabase.cells(shtDatabase.rows.count,1).End(xlUp).Offset(1,0).EnireRow

(that last one is all one line)

This assumes there is no information below your "database" block, and that
ColumnA always gets a value when you transfer a row.

If using from vbscript then substitute xlUp for its numeric value (which you
can get via Excel object browser)

Tim.


"lakelands" <guest@xxxxxx-email.com> wrote in message
news:686a233bd668c55d0d4add4fc0491304@xxxxxx-gateway.com...

"Tim Williams" <timjwilliams@xxxxxx> wrote in message
news:%23xTT2ax1JHA.1900@xxxxxx".EnireRow" or ".EntireRow"?

/Al

Yes, the one that without the typo.
I should always type in the VBE first...

Thanks Al,

Tim

"Al Dunbar" <alandrub@xxxxxx> wrote in message
news:OA44DRy1JHA.5276@xxxxxx

Sorry am only new to this area & I should have put this request into Microsoft.Public.Excel.Programming as suggested as I only wanted a Visual Basic macro to do the process required

Thans again for your help anyway