I have an application that exports a stream of data to Excel. Each time the
program runs it append to the previous run in column A. The data for each
subset takes up the same amount of rows as the previous. Example; the first
group of data will occupy cells A1 thru 10 and the second group A11 thru A20.
I want the second group to occupy B1 thru B10. In stead of cutting and
pasting is there a macro or function that will do this with only changing a
few parameters? Thanks for your help.

It appears that your application keeps track of the last row where data was
entered, and enters the next batch at Ax, where x is lastrow+1. You need
change the application to keep track of the last COLUMN where data was
entered, and enter the next batch at X1, where X is lastcolumn+1. This will
be easier to do if you switch to R1C1 referencing.

If you cannot change the application, then after running the app you would
have to manually move the new data from wherever it was written in Col. A to
the next empty column.
--
TedMi

"Ben Vann" wrote:

I have an application that exports a stream of data to Excel. Each time the
program runs it append to the previous run in column A. The data for each
subset takes up the same amount of rows as the previous. Example; the first
group of data will occupy cells A1 thru 10 and the second group A11 thru A20.
I want the second group to occupy B1 thru B10. In stead of cutting and
pasting is there a macro or function that will do this with only changing a
few parameters? Thanks for your help.

Unfortunatly I cannot change the application. I was hoping for code that
would do it automatically if changed the number of rows in a group.

"tedmi" wrote:

It appears that your application keeps track of the last row where data was
entered, and enters the next batch at Ax, where x is lastrow+1. You need
change the application to keep track of the last COLUMN where data was
entered, and enter the next batch at X1, where X is lastcolumn+1. This will
be easier to do if you switch to R1C1 referencing.

If you cannot change the application, then after running the app you would
have to manually move the new data from wherever it was written in Col. A to
the next empty column.
--
TedMi

"Ben Vann" wrote:

I have an application that exports a stream of data to Excel. Each time the
program runs it append to the previous run in column A. The data for each
subset takes up the same amount of rows as the previous. Example; the first
group of data will occupy cells A1 thru 10 and the second group A11 thru A20.
I want the second group to occupy B1 thru B10. In stead of cutting and
pasting is there a macro or function that will do this with only changing a
few parameters? Thanks for your help.

Don't know how/when the data gets into A11:A20 so can't suggest which type
of event code to use.

A macro would be as so........

Sub Move_Data()
Dim rng1 As Range
Dim rng2 As Range
With ActiveSheet
Set rng1 = .Range("A11:A20")
Set rng2 = .Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
rng1.Copy Destination:=rng2
End With
End Sub

Run whenever you want.

If you have more details on the import operation perhaps the macro could be
event-driven.


Gord Dibben MS Excel MVP

On Thu, 25 Sep 2008 12:28:26 -0700, Ben Vann <Ben
wrote:

I have an application that exports a stream of data to Excel. Each time the
program runs it append to the previous run in column A. The data for each
subset takes up the same amount of rows as the previous. Example; the first
group of data will occupy cells A1 thru 10 and the second group A11 thru A20.
I want the second group to occupy B1 thru B10. In stead of cutting and
pasting is there a macro or function that will do this with only changing a
few parameters? Thanks for your help.

Thanks Gord; Maybe this will help. I operate a Mitutoyo coordinate measuring
machine that can export the measurement results to Excel. Each time I run the
program it opens the file and appends the data to the last time it ran. If
the results were 25 rows long and I had four groups of data (4 parts), how
would that look in the code you supplied? Could it be deleted and shifted up
so the next group would be moved? Could there be input via a dialog box that
asked for number of rows and number of parts?

"Gord Dibben" wrote:

Don't know how/when the data gets into A11:A20 so can't suggest which type
of event code to use.

A macro would be as so........

Sub Move_Data()
Dim rng1 As Range
Dim rng2 As Range
With ActiveSheet
Set rng1 = .Range("A11:A20")
Set rng2 = .Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
rng1.Copy Destination:=rng2
End With
End Sub

Run whenever you want.

If you have more details on the import operation perhaps the macro could be
event-driven.


Gord Dibben MS Excel MVP

On Thu, 25 Sep 2008 12:28:26 -0700, Ben Vann <Ben
wrote:

I have an application that exports a stream of data to Excel. Each time the
program runs it append to the previous run in column A. The data for each
subset takes up the same amount of rows as the previous. Example; the first
group of data will occupy cells A1 thru 10 and the second group A11 thru A20.
I want the second group to occupy B1 thru B10. In stead of cutting and
pasting is there a macro or function that will do this with only changing a
few parameters? Thanks for your help.

Ben

Assume you have a blank worksheet.

On first run yoyo could pass any number of rows to column A........right?

This number would not change on subsequent runs...........right?

On second yoyo run it could pass any number of rows again to column
A........right?

I guess you could store the number of rows from first run and subtract that
from total rows after second run so's you would know how many second run
rows to move to next empty column.

Same for subsequent runs.

I don't understand the 4 groups and where they would be moved to.

Yes..........you could have an input box that requested how many rows but
not sure where the "parts" comes in.


Gord


On Thu, 25 Sep 2008 15:35:01 -0700, Ben Vann
wrote:

Thanks Gord; Maybe this will help. I operate a Mitutoyo coordinate measuring
machine that can export the measurement results to Excel. Each time I run the
program it opens the file and appends the data to the last time it ran. If
the results were 25 rows long and I had four groups of data (4 parts), how
would that look in the code you supplied? Could it be deleted and shifted up
so the next group would be moved? Could there be input via a dialog box that
asked for number of rows and number of parts?

"Gord Dibben" wrote:

Don't know how/when the data gets into A11:A20 so can't suggest which type
of event code to use.

A macro would be as so........

Sub Move_Data()
Dim rng1 As Range
Dim rng2 As Range
With ActiveSheet
Set rng1 = .Range("A11:A20")
Set rng2 = .Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
rng1.Copy Destination:=rng2
End With
End Sub

Run whenever you want.

If you have more details on the import operation perhaps the macro could be
event-driven.


Gord Dibben MS Excel MVP

On Thu, 25 Sep 2008 12:28:26 -0700, Ben Vann <Ben
wrote:

I have an application that exports a stream of data to Excel. Each time the
program runs it append to the previous run in column A. The data for each
subset takes up the same amount of rows as the previous. Example; the first
group of data will occupy cells A1 thru 10 and the second group A11 thru A20.
I want the second group to occupy B1 thru B10. In stead of cutting and
pasting is there a macro or function that will do this with only changing a
few parameters? Thanks for your help.