Help me solve this problem I've been struggling with for so long!

[Bjørn]

1) You have a source-list in sheet no. 1.
2) Sheet no 2, 3, 4.... 16 are sheets where the datas are going in when you
write them in the cells in the source list (sheet no.1).
3) The source-list should be used for the same purpose every week. The other
15 sheets are each containing one row for week 36, then week 37, 38, 39 and
so on. The results that you put in the source-sheet should come up in the
right row every week.

My question is:
- How will I be able to use the same source-list every week to register the
data only by changing the weekly number in sheet 1 (Source sheet). Are there
any formulas that I need to put in?

Thank you very much!

 

[Per Jessen]

Hi

I think you should use a macro to do this. The macro can be called from a
button on the source sheet.

If you need help writing the macro, ´more information about where to find
the source data and in where the first column of data should be stored.

HTH
Per

 

[Bjørn]

Thank you very much! You confirmed what I had in mind about using macro to do
this. The next question is how to write the macro for this purpose? I tried
to make buttons for each week, but I didn't manage to link it to the right
column in sheet no. 2, 3, 4....16.

I looked at your tip to write a macro, but where do I search for ´more
information about where to find the source data and in where the first column
of data should be stored'?

Thank you!

Bjørn

 

[Per Jessen]

Hi Bjø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

 

[Bjørn]

....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]

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