To look for the week number in row 2, change this line:

Set f = .Rows(1).Find(what:=Week, LookIn:=xlValues, lookat:=xlWhole)

to

Set f=.Rows(2).Find.....

Hopes you get the picture now ;-)

Per

On 3 Okt., 14:36, Bjørn wrote:
...still I haven't seen the big picture yet :-)

I've copied all the information into "Visual Basic" as you told me to, but
when I try out my command button, the message box says (as I have typed in
the code) "Week not found". In my case, I have my week-listing in row no. 2
starting at column B. (I've changed this in my coding in Visual Basic). Are
there other options or changes I need to do?

Thanks!



"Per Jessen" wrote:
HiBjørn

You enter a week number in a cell, then you enter your data to be copied in
one column. In my code I assume that week number is entered in A1.

The data you have entered has to be copied to all sheets in the workbook but
the source sheet. I assume you have week numbers in row 1 of sheet 2:16..
Data are to becopied to row 2 and down.

Only one button (from the command toolbox menu) is needed. Right click on
the button and select "View code", and copy the code below to the *code
sheet which appears. Change the cell references to suit and close the macro
editor. Exit design mode and try it.

Shuld the source data be cleared after it's copied to the ohter sheets?

Private Sub CommandButton1_Click()
Dim SourceSh As Worksheet
Dim Week As Integer
Dim f As Variant
Dim msg As String
Dim TargetCol As Integer
Dim SourceList As Range

Application.ScreenUpdating = False
Set SourceSh = Sheets("Sheet1") '<== Name of the SourceSheet
Set SourceList = SourceSh.Range("B2:B10") ' <== Change to refer to the data
to copy
Week = SourceSh.Range("A1").Value ' <== Change to suit

With Sheets("Sheet2") '<== Name of first data sheet
* * Set f = .Rows(1).Find(what:=Week, LookIn:=xlValues, lookat:=xlWhole)
End With
If f Is Nothing Then
* * msg = MsgBox("Week not found", vbCritical)
* * Exit Sub
End If
TargetCol = f.Column

SourceList.Copy
For Each sh In ThisWorkbook.Sheets
* * If sh.Name < SourceSh.Name Then
* * * * Sheets(sh.Name).Paste Destination:=Sheets(sh.Name).Cells(2,
TargetCol)
* * End If
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Best regards,
Per