copy and paste
 

i currently use Excel 2003 and have a copy and paste query.

i have a spreadsheet called "Orders Wk1" that has tabs for each day of the week and each tab has several column headings i.e Customer 1, Customer 2, Customer 3 etc.
I would like a macro that identifies a particular column heading (i.e Customer 2) and then copies the contents of that single column into a separate spreadsheet called "Order Summary"; this needs to be done for each day of the week.
The idea is to build up a history of daily orders for Customer 2.

Also. each time the macro is activated the data needs to be copied into the "Order Summary" in the next available column.

The "Order Summary" spreadsheet will be used all the time but the Orders spreadsheet will change each week i.e "Order Wk1", "Orders Wk2" etc.

TIA for all your help




--------------= Posted using GrabIt =----------------
------= Binary Usenet downloading made easy =---------
-= Get GrabIt for free from http://www.shemes.com/ =-

copy and paste
 

I think you want something like this. I assumed the Order Summary sheet
already existed.

Sub MakeSummary()

Customer = "Me"
With Sheets("Order Summary")
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
NewCol = LastCol + 1
End With
'repeat for each sheet except Order Summary Sheet
For Each sht In ThisWorkbook.Sheets
If sht.Name < "Order Summary" Then
'Find customer in Row 1
Set c = sht.Rows(1).Find(what:=Customer, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cound not find customer : " & Customer & _
"in Sheet : " & sht.Name)
Else
c.EntireColumn.Copy _
Destination:=Sheets("Order Summary").Columns(NewCol)
NewCol = NewCol + 1
End If
End If
Next sht

End Sub

"MickkyB" wrote:

i currently use Excel 2003 and have a copy and paste query.

i have a spreadsheet called "Orders Wk1" that has tabs for each day of the week and each tab has several column headings i.e Customer 1, Customer 2, Customer 3 etc.
I would like a macro that identifies a particular column heading (i.e Customer 2) and then copies the contents of that single column into a separate spreadsheet called "Order Summary"; this needs to be done for each day of the week.
The idea is to build up a history of daily orders for Customer 2.

Also. each time the macro is activated the data needs to be copied into the "Order Summary" in the next available column.

The "Order Summary" spreadsheet will be used all the time but the Orders spreadsheet will change each week i.e "Order Wk1", "Orders Wk2" etc.

TIA for all your help




--------------= Posted using GrabIt =----------------
------= Binary Usenet downloading made easy =---------
-= Get GrabIt for free from http://www.shemes.com/ =-