Arrays
 

Hello,

I have an interesting situation, would like some input.

I have a set of values on a sheet which have one similarity which is the acct#
now i want excel to copy that data to an appropriate tab which is numbered
with that specific acct#, i want it to copy all the activity that is in the
same format as the tab is.

So instead of me copying and pasting is there a simpler way of acoomplishing
this task.

Somehow I believe by using an array in a loop to test back and forth untill
it copies all the values.

Here is the data

A1: ACCT# B1: INV# C1: TOTAL $
A2: 333 B2: 551 C2: 33
A3: 333 B3: 552 C3: 55
A4: 642 B4: 553 C4: 136
A5: 642 B5: 554 C5: 352

Everything is already sorted by acct #, so i am just copying and pasting the
section onto a seperate sheet, there is just so many accts and especially
some of them only have one transaction.

So what i need the fomula to do is maybe get the INV# of this sheet and fill
it down and then maybe use a vlookup to get the rest of the information.

Thanks in advance.

Dan

Dan
This is best done with a macro. The following macro loops through all
the values in Column A starting with A2. For each value in Column A, this
macro copies Columns A:C and pastes that into the next empty row in the
sheet that is named the value in Column A.
Note that there is no error trapping in this macro. It is assumed that
a sheet exists for each value in Column A.
This macro has one line of code to clear the row once it has been copied.
That line of code is remarked-out at present. I didn't know what you wanted
to do with the original data once it is copied.
If you wish, send me, via email, a valid email address for you and I
will send you a small file with this macro properly placed. HTH Otto
Sub MoveData()
Dim RngColA As Range
Dim i As Range
Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For Each i In RngColA
i.Resize(, 3).Copy Sheets(Format(i.Value, "@")). _
Range("A" & Rows.Count).End(xlUp).Offset(1)
'i.Resize(, 3).ClearContents
Next i
End Sub
"Dan" wrote in message
...
Hello,

I have an interesting situation, would like some input.

I have a set of values on a sheet which have one similarity which is the
acct#
now i want excel to copy that data to an appropriate tab which is numbered
with that specific acct#, i want it to copy all the activity that is in
the
same format as the tab is.

So instead of me copying and pasting is there a simpler way of
acoomplishing
this task.

Somehow I believe by using an array in a loop to test back and forth
untill
it copies all the values.

Here is the data

A1: ACCT# B1: INV# C1: TOTAL $
A2: 333 B2: 551 C2: 33
A3: 333 B3: 552 C3: 55
A4: 642 B4: 553 C4: 136
A5: 642 B5: 554 C5: 352

Everything is already sorted by acct #, so i am just copying and pasting
the
section onto a seperate sheet, there is just so many accts and especially
some of them only have one transaction.

So what i need the fomula to do is maybe get the INV# of this sheet and
fill
it down and then maybe use a vlookup to get the rest of the information.

Thanks in advance.

Dan

I should have added that my email address is . Remove
the "nop" from this address. Otto
"Otto Moehrbach" wrote in message
...
Dan
This is best done with a macro. The following macro loops through all
the values in Column A starting with A2. For each value in Column A, this
macro copies Columns A:C and pastes that into the next empty row in the
sheet that is named the value in Column A.
Note that there is no error trapping in this macro. It is assumed that
a sheet exists for each value in Column A.
This macro has one line of code to clear the row once it has been copied.
That line of code is remarked-out at present. I didn't know what you
wanted to do with the original data once it is copied.
If you wish, send me, via email, a valid email address for you and I
will send you a small file with this macro properly placed. HTH Otto
Sub MoveData()
Dim RngColA As Range
Dim i As Range
Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For Each i In RngColA
i.Resize(, 3).Copy Sheets(Format(i.Value, "@")). _
Range("A" & Rows.Count).End(xlUp).Offset(1)
'i.Resize(, 3).ClearContents
Next i
End Sub
"Dan" wrote in message
...
Hello,

I have an interesting situation, would like some input.

I have a set of values on a sheet which have one similarity which is the
acct#
now i want excel to copy that data to an appropriate tab which is
numbered
with that specific acct#, i want it to copy all the activity that is in
the
same format as the tab is.

So instead of me copying and pasting is there a simpler way of
acoomplishing
this task.

Somehow I believe by using an array in a loop to test back and forth
untill
it copies all the values.

Here is the data

A1: ACCT# B1: INV# C1: TOTAL $
A2: 333 B2: 551 C2: 33
A3: 333 B3: 552 C3: 55
A4: 642 B4: 553 C4: 136
A5: 642 B5: 554 C5: 352

Everything is already sorted by acct #, so i am just copying and pasting
the
section onto a seperate sheet, there is just so many accts and especially
some of them only have one transaction.

So what i need the fomula to do is maybe get the INV# of this sheet and
fill
it down and then maybe use a vlookup to get the rest of the information.

Thanks in advance.

Dan

Note that:

=CELL("filename",A1)

gives you a file path & sheetname for a saved workbook.

To extract just the sheetname (your INV#)...

If the sheetname is always the same length (e.g., your invoice number
is always 10 characters long), use this formula, for example:

=RIGHT(CELL("filename",A1),10)

- D Hilberg