I need to move (cut & paste ) the data from certain cells in multiple
positions (different columns & rows) into a single row and then repeat
the same process over-and-over again. The procedure should continue to
drill-down the worksheet, Approx. 6000 rows) until these referenced
cells (relative to their position) become empty. The data in Cell B3
would need to go into Cell F11, data in Cell B4 into Cell G11, B5 into
cell J11 ...data in Cell B6 into Cell H11, B7 into Cell I11, and then
move data in Cell F8 into E11... finally move down 54 rows/positions
and repeat this process for the rest of the relative cells.
See below: existing position of data and final position.

Since a picture says a thousand words:

Existing positions of data: B3,B4,B5,B6,B7,F8

Final positions of data after moved: F11,G11,J11,H11,I11,E11

Example:

| A | B | C | D | E | F | G | H | I | J |
______|___________________________________________ __________________
Row 1 |
Row 2 |
Row 3 | Cust
Row 4 | Prod
Row 5 | Desc
Row 6 | Date
Row 7 | Rev#
Row 8 | Sp.G
Row 9 |
Row 10|
Row 11| Sp.G Cust Prod Date Rev# Desc


|
| * Skip Down 54 spaces (Relative to Starting Position),(B3).
|
| * Repeat the (Cut & Paste or Move) operation.
|
|
| Existing positions of data: B57,B58,B59,B60,B61,F62
|
| Final positions of data after moved: F65,G65,J65,H65,I65,E65
V

| A | B | C | D | E | F | G | H | I | J |
_______|__________________________________________ ___________________
Row 54 |
Row 56 |
Row 57 | Cust
Row 58 | Prod
Row 59 | Desc
Row 60 | Date
Row 61 | Rev#
Row 62 | Sp.G
Row 63 |
Row 64 |
Row 65 | Sp.G Cust Prod Date Rev# Desc


* Repeat the (Cut & Paste or Move) operation until the cells to
copy become empty/null or Approx. Row 6000.

I will then proceed to move this data into an access database.
I thought an action query to do this would be even more
difficult for me to try and figure out. However, I'm still
open to any suggestions.

Thanks,
Robert

Try this against a copy of your worksheet--just in case:

Option Explicit
Sub testme()

'B3 , B4, B5, B6, B7, F8
'F11,G11,J11,H11,I11,E11

'B57,B58,B59,B60,B61,F62
'F65,G65,J65,H65,I65,E65

Dim wks As Worksheet
Dim iRow As Long
Dim LastRow As Long
Dim FirstRow As Long

Set wks = Worksheets("sheet1")

With wks
FirstRow = 3
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For iRow = FirstRow To LastRow Step 54
.Cells(iRow, "B").Resize(5, 1).Copy
.Cells(iRow + 8, "F").PasteSpecial Transpose:=True

.Cells(iRow + 5, "F").Copy _
Destination:=.Cells(iRow + 8, "E")
Next iRow
End With

End Sub

objRobertMitchell wrote:

I need to move (cut & paste ) the data from certain cells in multiple
positions (different columns & rows) into a single row and then repeat
the same process over-and-over again. The procedure should continue to
drill-down the worksheet, Approx. 6000 rows) until these referenced
cells (relative to their position) become empty. The data in Cell B3
would need to go into Cell F11, data in Cell B4 into Cell G11, B5 into
cell J11 ...data in Cell B6 into Cell H11, B7 into Cell I11, and then
move data in Cell F8 into E11... finally move down 54 rows/positions
and repeat this process for the rest of the relative cells.
See below: existing position of data and final position.

Since a picture says a thousand words:

Existing positions of data: B3,B4,B5,B6,B7,F8

Final positions of data after moved: F11,G11,J11,H11,I11,E11

Example:

| A | B | C | D | E | F | G | H | I | J |
______|___________________________________________ __________________
Row 1 |
Row 2 |
Row 3 | Cust
Row 4 | Prod
Row 5 | Desc
Row 6 | Date
Row 7 | Rev#
Row 8 | Sp.G
Row 9 |
Row 10|
Row 11| Sp.G Cust Prod Date Rev# Desc

|
| * Skip Down 54 spaces (Relative to Starting Position),(B3).
|
| * Repeat the (Cut & Paste or Move) operation.
|
|
| Existing positions of data: B57,B58,B59,B60,B61,F62
|
| Final positions of data after moved: F65,G65,J65,H65,I65,E65
V

| A | B | C | D | E | F | G | H | I | J |
_______|__________________________________________ ___________________
Row 54 |
Row 56 |
Row 57 | Cust
Row 58 | Prod
Row 59 | Desc
Row 60 | Date
Row 61 | Rev#
Row 62 | Sp.G
Row 63 |
Row 64 |
Row 65 | Sp.G Cust Prod Date Rev# Desc

* Repeat the (Cut & Paste or Move) operation until the cells to
copy become empty/null or Approx. Row 6000.

I will then proceed to move this data into an access database.
I thought an action query to do this would be even more
difficult for me to try and figure out. However, I'm still
open to any suggestions.

Thanks,
Robert

--

Dave Peterson

Sub ProcessData()
r = 3
do while cells(r,2) < ""
cells(r,2).Resize(6,1).copy
cells(r+8,"F").PasteSpecial xlPasteAll, Transpose:=True
cells(r,2).Resize(6,1).ClearContents
r = r + 54
Loop
end sub

--
Regards,
Tom Ogilvy


"objRobertMitchell" wrote in message
oups.com...
I need to move (cut & paste ) the data from certain cells in multiple
positions (different columns & rows) into a single row and then repeat
the same process over-and-over again. The procedure should continue to
drill-down the worksheet, Approx. 6000 rows) until these referenced
cells (relative to their position) become empty. The data in Cell B3
would need to go into Cell F11, data in Cell B4 into Cell G11, B5 into
cell J11 ...data in Cell B6 into Cell H11, B7 into Cell I11, and then
move data in Cell F8 into E11... finally move down 54 rows/positions
and repeat this process for the rest of the relative cells.
See below: existing position of data and final position.

Since a picture says a thousand words:

Existing positions of data: B3,B4,B5,B6,B7,F8

Final positions of data after moved: F11,G11,J11,H11,I11,E11

Example:

| A | B | C | D | E | F | G | H | I | J |
______|___________________________________________ __________________
Row 1 |
Row 2 |
Row 3 | Cust
Row 4 | Prod
Row 5 | Desc
Row 6 | Date
Row 7 | Rev#
Row 8 | Sp.G
Row 9 |
Row 10|
Row 11| Sp.G Cust Prod Date Rev# Desc


|
| * Skip Down 54 spaces (Relative to Starting Position),(B3).
|
| * Repeat the (Cut & Paste or Move) operation.
|
|
| Existing positions of data: B57,B58,B59,B60,B61,F62
|
| Final positions of data after moved: F65,G65,J65,H65,I65,E65
V

| A | B | C | D | E | F | G | H | I | J |
_______|__________________________________________ ___________________
Row 54 |
Row 56 |
Row 57 | Cust
Row 58 | Prod
Row 59 | Desc
Row 60 | Date
Row 61 | Rev#
Row 62 | Sp.G
Row 63 |
Row 64 |
Row 65 | Sp.G Cust Prod Date Rev# Desc


* Repeat the (Cut & Paste or Move) operation until the cells to
copy become empty/null or Approx. Row 6000.

I will then proceed to move this data into an access database.
I thought an action query to do this would be even more
difficult for me to try and figure out. However, I'm still
open to any suggestions.

Thanks,
Robert

Tom Ogilvy wrote:
Sub ProcessData()
r = 3
do while cells(r,2) < ""
cells(r,2).Resize(6,1).copy
cells(r+8,"F").PasteSpecial xlPasteAll, Transpose:=True
cells(r,2).Resize(6,1).ClearContents
r = r + 54
Loop
end sub

--
Regards,
Tom Ogilvy


"objRobertMitchell" wrote in message
oups.com...
I need to move (cut & paste ) the data from certain cells in multiple
positions (different columns & rows) into a single row and then repeat
the same process over-and-over again. The procedure should continue to
drill-down the worksheet, Approx. 6000 rows) until these referenced
cells (relative to their position) become empty. The data in Cell B3
would need to go into Cell F11, data in Cell B4 into Cell G11, B5 into
cell J11 ...data in Cell B6 into Cell H11, B7 into Cell I11, and then
move data in Cell F8 into E11... finally move down 54 rows/positions
and repeat this process for the rest of the relative cells.
See below: existing position of data and final position.

Since a picture says a thousand words:

Existing positions of data: B3,B4,B5,B6,B7,F8

Final positions of data after moved: F11,G11,J11,H11,I11,E11

Example:

| A | B | C | D | E | F | G | H | I | J |
______|___________________________________________ __________________
Row 1 |
Row 2 |
Row 3 | Cust
Row 4 | Prod
Row 5 | Desc
Row 6 | Date
Row 7 | Rev#
Row 8 | Sp.G
Row 9 |
Row 10|
Row 11| Sp.G Cust Prod Date Rev# Desc


|
| * Skip Down 54 spaces (Relative to Starting Position),(B3).
|
| * Repeat the (Cut & Paste or Move) operation.
|
|
| Existing positions of data: B57,B58,B59,B60,B61,F62
|
| Final positions of data after moved: F65,G65,J65,H65,I65,E65
V

| A | B | C | D | E | F | G | H | I | J |
_______|__________________________________________ ___________________
Row 54 |
Row 56 |
Row 57 | Cust
Row 58 | Prod
Row 59 | Desc
Row 60 | Date
Row 61 | Rev#
Row 62 | Sp.G
Row 63 |
Row 64 |
Row 65 | Sp.G Cust Prod Date Rev# Desc


* Repeat the (Cut & Paste or Move) operation until the cells to
copy become empty/null or Approx. Row 6000.

I will then proceed to move this data into an access database.
I thought an action query to do this would be even more
difficult for me to try and figure out. However, I'm still
open to any suggestions.

Thanks,
Robert



Thanks Guys!

There will always be more than one way to skin a cat.

.....As proven during one of my Anatomy & Physiology Labs!

....Sorry Kitty!

Robert M.

No, this isn't correct and I don't believe Dave's is either. See my second
post.

--
Regards,
Tom Ogilvy


"objRobertMitchell" wrote in message
oups.com...

Tom Ogilvy wrote:
Sub ProcessData()
r = 3
do while cells(r,2) < ""
cells(r,2).Resize(6,1).copy
cells(r+8,"F").PasteSpecial xlPasteAll, Transpose:=True
cells(r,2).Resize(6,1).ClearContents
r = r + 54
Loop
end sub

--
Regards,
Tom Ogilvy


"objRobertMitchell" wrote in message
oups.com...
I need to move (cut & paste ) the data from certain cells in multiple
positions (different columns & rows) into a single row and then repeat
the same process over-and-over again. The procedure should continue to
drill-down the worksheet, Approx. 6000 rows) until these referenced
cells (relative to their position) become empty. The data in Cell B3
would need to go into Cell F11, data in Cell B4 into Cell G11, B5 into
cell J11 ...data in Cell B6 into Cell H11, B7 into Cell I11, and then
move data in Cell F8 into E11... finally move down 54 rows/positions
and repeat this process for the rest of the relative cells.
See below: existing position of data and final position.

Since a picture says a thousand words:

Existing positions of data: B3,B4,B5,B6,B7,F8

Final positions of data after moved: F11,G11,J11,H11,I11,E11

Example:

| A | B | C | D | E | F | G | H | I | J |
______|___________________________________________ __________________
Row 1 |
Row 2 |
Row 3 | Cust
Row 4 | Prod
Row 5 | Desc
Row 6 | Date
Row 7 | Rev#
Row 8 | Sp.G
Row 9 |
Row 10|
Row 11| Sp.G Cust Prod Date Rev# Desc


|
| * Skip Down 54 spaces (Relative to Starting Position),(B3).
|
| * Repeat the (Cut & Paste or Move) operation.
|
|
| Existing positions of data: B57,B58,B59,B60,B61,F62
|
| Final positions of data after moved: F65,G65,J65,H65,I65,E65
V

| A | B | C | D | E | F | G | H | I | J |
_______|__________________________________________ ___________________
Row 54 |
Row 56 |
Row 57 | Cust
Row 58 | Prod
Row 59 | Desc
Row 60 | Date
Row 61 | Rev#
Row 62 | Sp.G
Row 63 |
Row 64 |
Row 65 | Sp.G Cust Prod Date Rev# Desc


* Repeat the (Cut & Paste or Move) operation until the cells to
copy become empty/null or Approx. Row 6000.

I will then proceed to move this data into an access database.
I thought an action query to do this would be even more
difficult for me to try and figure out. However, I'm still
open to any suggestions.

Thanks,
Robert



Thanks Guys!

There will always be more than one way to skin a cat.

....As proven during one of my Anatomy & Physiology Labs!

...Sorry Kitty!

Robert M.