Copy , combine and Paste Multiple Ranges to different worksheet in VBA

[eyupeyup]

Hi guys,

I am finding this site very useful but fully admit I have set myself a challenge that is way beyond my knowledge! I hope I can explain sufficiently.

I have generic data i.e. customer, order numbers, references (total 16 entries) (Cells, C2, C3, C4, C5, C6, C7, C8, C11, O4, O5, O6, O7, O8, O9, O10, O11)

I then have, on the same sheet, specific data i.e. lot numbers, weights, dates, specification criteria in 20 rows, with 15 columns (Row 1 range A18:O18, Row 2 range, A19:O19, Row 3 range A20:O20 etc)

I need to the generica data with the specific data, copy and then paste together to another sheet in the same workbook so the generic data and specific data appears as one row in table:

ROW1 C2, C3, C4, C5, C6, C7, C8, C11, O4, O5, O6, O7, O8, O9, O10, O11, A18, B18, C18, D18, E18, F18, G18, H18, I18, J18, K18, L18, M18, N18, O18
ROW2 C2, C3, C4, C5, C6, C7, C8, C11, O4, O5, O6, O7, O8, O9, O10, O11, A19, B19, C19, D19, E19, F19, G19, H19, I19, J19, K19, L19, M19, N19, O19
ROW3 C2, C3, C4, C5, C6, C7, C8, C11, O4, O5, O6, O7, O8, O9, O10, O11, A20, B20, C20, D20, E20, F20, G20, H20, I20, J20, K20, L20, M20, N20, O20
ROW4 etc
ROW5 etc

I tried selecting ranges which failed as I couldnt combine the ranges but my reading led me to "Unions".

Please could somebody give an example of how I would write the code to use Unions with the above and allow me to combine copy and paste the above to create my data table?

My intention is to have one button that runs multiple modules to save the final sheet in pdf (can do this), prints the sheet for archive (can do this) and create the data table for easy manipulation/statistics later (subject of this post). Its an interesting project

Thank you

 

[footoo]

Dim sWS As Worksheet, dWs As Worksheet
Set sWS = Sheets("Sheet1")
Set dWs = Sheets("sheet2")
sWS.[C2:C8].Copy dWs.[A1:G20]
sWS.[C11].Copy dWs.[H1:H20]
sWS.[O4:O11].Copy dWs.[I1:P20]
sWS.[A18:O37].Copy dWs.[Q18:X37]

 

[footoo]

Revised :

Dim sWS As Worksheet, dWs As Worksheet
Set sWS = Sheets("Sheet1")
Set dWs = Sheets("sheet2")
sWS.[C2:C8].Copy
dWs.[A1:G20].PasteSpecial Paste:=xlPasteAll, Transpose:=True
sWS.[C11].Copy dWs.[H1:H20]
sWS.[O4:O11].Copy
dWs.[I1:P20].PasteSpecial Paste:=xlPasteAll, Transpose:=True
sWS.[A18:O37].Copy dWs.[Q18:X37]