Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help me solve this problem I've been struggling with for so long!
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help me solve this problem I've been struggling with for so long!
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" skrev i meddelelsen ... 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help me solve this problem I've been struggling with for so long!
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" wrote: 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" skrev i meddelelsen ... 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help me solve this problem I've been struggling with for so long!
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" skrev i meddelelsen ... 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" wrote: 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" skrev i meddelelsen ... 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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I feel that I am closer to the answer thanks to you! But...
....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: 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" skrev i meddelelsen ... 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" wrote: 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" skrev i meddelelsen ... 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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I feel that I am closer to the answer thanks to you! But...
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to solve too long coding for Macro ? | Excel Worksheet Functions | |||
Pls help me to solve this problem... | Excel Worksheet Functions | |||
Please solve this problem. | Excel Worksheet Functions | |||
please solve the problem | Excel Discussion (Misc queries) | |||
Can someone solve a problem for me? | Excel Discussion (Misc queries) |