Copy selected cells to next page and paste in a different order!

[rob999]

Hi there,

I am having real trouble with something and I am now losing the will to live. Hopefully, a kind person out there will be able to help.

In a nutshell:

I need to copy certain cells from a row in the sheet called "Prices" into the next row (that doesn't contain data) in the sheet called "Order Summary". The column order on the two sheets is different. Is this even possible?

One further problem is that the unit prices on "Prices" are derived from a sheet called "Terminal" (as a simple =Terminal!G19 for example) and the haulage rate is the same (=Haulage!D4)

So as an example...

Prices A5 needs to go to Order Summary B4 (as it's the next data-less row)
B5 to D4
D5 to E4
E5 to C4
F5 to G4 (unit cost derived from =Terminal!G19, if that matters)
G5 to H4
H5 to I4 (haulage cost derived from =Haulage!D4)

And that's it!!

I'm a relative novice with macros, and although I have been trying to patch together something that works from various posts on here, I am really struggling. Any help would be greatly appreciated, and will save me from long nights of failure!

Thanks in advance,

Rob

 

[rob999]

Forgot to mention something that makes this even harder - the company I work for uses Excel 2003 !! I didn't know if this made any difference to the way in which people can help?

 

[Akashwani]

Hi Rob,

Welcome to MrExcel,

I don't write or understand VBA code so I am unable to help you further with this, sorry.

I have copied the following from a code I have and changed the cell references to suit yours...

Sub CopyData()
Application.ScreenUpdating = False

With Sheets("Order Summary")
    lRow = .Cells(Rows.Count, 2).End(xlUp).Row + 1
    
    .Cells(lRow, 2).Value = ActiveSheet.Range("A5")
    .Cells(lRow, 3).Value = ActiveSheet.Range("E5")
    .Cells(lRow, 4).Value = ActiveSheet.Range("B5")
    .Cells(lRow, 5).Value = ActiveSheet.Range("D5")
    .Cells(lRow, 7).Value = ActiveSheet.Range("F5")
    .Cells(lRow, 8).Value = ActiveSheet.Range("G5")
    .Cells(lRow, 9).Value = ActiveSheet.Range("H5")
    
End With

Application.ScreenUpdating = True
End Sub

Excel Workbook
	A	B	C	D	E	F	G	H
4	Data1	Data2	*	Data3	Data4	Data5	Data6	Data7
5	Test1 A5	Test1 B5	*	Test1 D5	Test1 E5	Test1 F5	Test1 G5	Test1 H5
Prices

Excel Workbook
	B	C	D	E	F	G	H	I
3	Prices A5	Prices E5	Prices B5	Prices D5	*	Prices F5	Prices G5	Prices H5
4	*	*	*	*	*	*	*	*
5	*	*	*	*	*	*	*	*
6	*	*	*	*	*	*	*	*
7	*	*	*	*	*	*	*	*
Order Summary

*** Run the Macro CopyData***

Excel Workbook
	B	C	D	E	F	G	H	I
3	Prices A5	Prices E5	Prices B5	Prices D5	*	Prices F5	Prices G5	Prices H5
4	Test1 A5	Test1 E5	Test1 B5	Test1 D5	*	Test1 F5	Test1 G5	Test1 H5
5	*	*	*	*	*	*	*	*
6	*	*	*	*	*	*	*	*
7	*	*	*	*	*	*	*	*
Order Summary

Excel Workbook
	A	B	C	D	E	F	G	H
4	Data1	Data2	*	Data3	Data4	Data5	Data6	Data7
5	Test2 A5	Test2 B5	*	Test2 D5	Test2 E5	Test2 F5	Test2 G5	Test2 H5
Prices

*** Run the Macro CopyData***

Excel Workbook
	B	C	D	E	F	G	H	I
3	Prices A5	Prices E5	Prices B5	Prices D5	*	Prices F5	Prices G5	Prices H5
4	Test1 A5	Test1 E5	Test1 B5	Test1 D5	*	Test1 F5	Test1 G5	Test1 H5
5	Test2 A5	Test2 E5	Test2 B5	Test2 D5	*	Test2 F5	Test2 G5	Test2 H5
6	*	*	*	*	*	*	*	*
7	*	*	*	*	*	*	*	*
Order Summary

To use the code I have provided, press alt F11 to open vba.
Click Insert, click Module and paste the code into the white area on the right. Press alt F11 to close vba.

You can Insert a Shape onto sheet Prices, Right click the shape, click Assign Macro, double click CopyData. When you click the button the code will run and copy the data from Prices to Order Summary

I hope that works for you.

Good luck.

Ak

 

[rob999]

Hi Akashwani,

That's absolutely fantastic, thank you. I've just tested it and it works perfectly. I really do appreciate your thoughtful and thorough reply.

Rob