Copy Cells to new spreadsheet based on Cells(i, 4) value

[christian]

Very new to VBA - my apologies for such a newbie question...

* Need to read each row in a spreasheet (number of rows in the
spreadsheet will always be changing)
* if there is a value in Cells(i, 4) I want to copy columns 4,5,7,10
to a new workbook. [one or more rows makes up one record - the value
in column 4 signifies a new record]
* This new spreadsheet will be turned into a CSV to feed another
program.



How would you approach this?
Any help is appreciated!!

-christian

 

[Bob Kilmer]

Some tips:
* Look up "last cell" in microsoft.public.excel.programming on Google
Groups. That will tell you how to find the last cell in a column, or sheet,
and hence the number of occupied rows.

* generally, copy works like this

Range-Object-to-copy-from.Copy Range-Object-to-copy-to

Workbook("Book1.xls").Worksheets("Sheet1").Columns(4).Copy Workbook(...
etc.

If needed, can do
Range-Object-to-copy-from.Copy
Range-Object-to-copy-to.PasteSpecial(<look up options>)

* Can SaveAs csv type. Look up SaveAs.

* looping thru cells

Dim v as variant
For each v in <range>.Cells
Debug.print v.Text
Next v

or

Dim n as Long '(not Integer; too small for all rows)
For n = 1 to <range>.Cells.Count
Debug.Print <range>.Cells(n).Text
Debug.Print <range>.Cells(n,4).Text
Next n

Ranges are fully qualified if they include the workbook, worksheet, etc down
to the column, row, cell or cells you are interested in. If you don't
specifiy the workbook and worksheet, the default is the "active" one,
whatever that is at the time you run your code.

Bob

 

[Bob Kilmer]

Should be "Workbooks("Book1.xls")..."

(plural; it is a Collection).

Look up Workbooks Collection, Add method, in Help too.

Some tips:
* Look up "last cell" in microsoft.public.excel.programming on Google
Groups. That will tell you how to find the last cell in a column, or sheet,
and hence the number of occupied rows.

* generally, copy works like this

Range-Object-to-copy-from.Copy Range-Object-to-copy-to

Workbook("Book1.xls").Worksheets("Sheet1").Columns(4).Copy Workbook(...
etc.

If needed, can do
Range-Object-to-copy-from.Copy
Range-Object-to-copy-to.PasteSpecial(<look up options>)

* Can SaveAs csv type. Look up SaveAs.

* looping thru cells

Dim v as variant
For each v in <range>.Cells
Debug.print v.Text
Next v

or

Dim n as Long '(not Integer; too small for all rows)
For n = 1 to <range>.Cells.Count
Debug.Print <range>.Cells(n).Text
Debug.Print <range>.Cells(n,4).Text
Next n

Ranges are fully qualified if they include the workbook, worksheet, etc down
to the column, row, cell or cells you are interested in. If you don't
specifiy the workbook and worksheet, the default is the "active" one,
whatever that is at the time you run your code.

Bob

Very new to VBA - my apologies for such a newbie question...

* Need to read each row in a spreasheet (number of rows in the
spreadsheet will always be changing)
* if there is a value in Cells(i, 4) I want to copy columns 4,5,7,10
to a new workbook. [one or more rows makes up one record - the value
in column 4 signifies a new record]
* This new spreadsheet will be turned into a CSV to feed another
program.



How would you approach this?
Any help is appreciated!!

-christian