EXCEL 2010: VBA to Offset Cells (Vertical Order) to horizontal (row orders) on a 2nd page

[mrsklb]

Hi there,

I have a set of data, however the data is 1 row per line of information, per order. 9 lines per order in total + 1 blank row between each.

I am looking for a way to move them to a new sheet, and all data 1 line per order (as setout below).

I am looking for 2 ways of doing this;
1) Straight forward VBA to move each cell from it's relevant sheet, to it's new sheet starting at A11(12,14 etc), I would define the Cell to move the data to.

• Raw Data = Sheet: Raw; Cell (A11) move ->
• Stage 2 = Sheet: Stage 2; Cell (A20, or defined by me)

2) Copy via VBA from the original sheet, to the next available row on sheet #2 (I would define the sheet, but not the cells).

• Raw Data(sh2) = Sheet: Raw(2); Cell (A11) move ->
• Stage 2(b) = Sheet: Stage2(b); Cell - first row available, past (A11)

(it has to be past row 11 as (A11) is blank on this page, and the 'last row+1' code overwrites everything and now in my VBA I have to make sure the first thing I do is (A11)cell="something".

I run 2 sheets for Raw, data from 1 source & data from a 2nd, the layout is the same, but the data needs to be treated separately. I later merge them to 1 sheet, but only after I've collated each source, and tagged each 1 with a brand/ID.

The process is:
login 1, download data, clean data on sheet 2 (this step), add brand/ID (autofill), move to second data
login 2, download data, clean data on sheet 4, add brand/ID (autofill) then move to the bottom of the original data, then next stage of the history collation.

I have 2 raw sheets (login 1, login 2)
I have 1 stage 2 sheets (login 1, stage 2 & login 2, stage 2 (below login 1)).

If I knew a way I could reduce this to just 2 sheets, that would be awesome, but for now I treat separately as there is no distinct way of defining the 2 sources.


Here is my raw data:
(my sheets show (A10) but they will be on (A11) for Raw & Raw2
raw

*	A	B
10	Order	#123456
11	Product	widget1
12	SKU	w-widget1
13	Commission	$10.00
14	Payment status	Pending
15	Status	Complete
16	Customer	customer name (customer email)
17	Date	15-Feb-15
18	Price	$50.00
19	*	*
20	Order	#123457
21	Product	widget2
22	SKU	w-widget2
23	Commission	$20.00
24	Payment status	Pending
25	Status	Complete
26	Customer	customer name (customer email)
27	Date	15-Feb-15
28	Price	$60.00
29	*	*
30	Order	#123458
31	Product	widget3
32	SKU	w-widget3
33	Commission	$30.00
34	Payment status	Pending
35	Status	Complete
36	Customer	customer name (customer email)
37	Date	8-Feb-15
38	Price	$70.00

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

What I am needing it to look like:

stage2
I remove the # tag post processing using 'text to columns'.

*	A	B	C	D	E	F	G	H	I
10	Order	Product	SKU	Commission	Payment status	Status	Customer	Date	Price
11	123456	widget1	w-widget1	$10.00	Pending	Complete	customer name (customer email)	15-Feb-15	$50.00
12	123457	widget2	w-widget2	$20.00	Pending	Complete	customer name (customer email)	15-Feb-15	$60.00
13	123458	widget3	w-widget3	$30.00	Pending	Complete	customer name (customer email)	8-Feb-15	$70.00

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

I have been using a code previously, however I can't make head nor tails of it & I can't get it to fill on a line of my choice, or on the "last row+1". The data keeps getting skewed.

Many thanks

 

[mrsklb]

Gentle bump