| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell data change
Hello,
I am looking for some help in solving an issue i have with a shift rosta. I have 17 rows in excel whick relate to 17 shift persons Cell A1 to A17 as follows A B C D E F G H I J K L M N O P Q What I am looking for is for on every thursday I need data in cell A17 to be input into cell A1 and all the other cells to go down 1 cell, e.g. data from cell A1 to Cell A2 and so on, this is the data I need to rotate no other data needs to rotate. Can anyone give me some help on this. Many thanks terilad |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell data change
This is great, thanks, is thre any way that this can be done automatically
when the excel sheet is opened on a Thursday for instance? Many thanks Terilad "Jacob Skaria" wrote: Try the below macro. If you are new to macros Set the Security level to low/medium in (Tools|Macro|Security). 'Launch VBE using short-key Alt+F11. Insert a module and paste the below code. Save. Get back to Workbook. Tools|Macro|Run MacroTest() Sub MacroTest() Dim varValue varValue = Range("A17") Range("A17") = "" Range("A1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range("A1") = varValue End Sub -- If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: Hello, I am looking for some help in solving an issue i have with a shift rosta. I have 17 rows in excel whick relate to 17 shift persons Cell A1 to A17 as follows A B C D E F G H I J K L M N O P Q What I am looking for is for on every thursday I need data in cell A17 to be input into cell A1 and all the other cells to go down 1 cell, e.g. data from cell A1 to Cell A2 and so on, this is the data I need to rotate no other data needs to rotate. Can anyone give me some help on this. Many thanks terilad |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell data change
Sure you can..
Launch VBE using short-key Alt+F11. On the left treeview double click 'This Workbook '. Drop down to get the Workbook open event Paste the below code in Workbook Open event. Private Sub Workbook_Open() If Format(Date, "ddd") = "Thu" Then Dim varValue varValue = Range("A17") Range("A17") = "" Range("A1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range("A1") = varValue End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: This is great, thanks, is thre any way that this can be done automatically when the excel sheet is opened on a Thursday for instance? Many thanks Terilad "Jacob Skaria" wrote: Try the below macro. If you are new to macros Set the Security level to low/medium in (Tools|Macro|Security). 'Launch VBE using short-key Alt+F11. Insert a module and paste the below code. Save. Get back to Workbook. Tools|Macro|Run MacroTest() Sub MacroTest() Dim varValue varValue = Range("A17") Range("A17") = "" Range("A1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range("A1") = varValue End Sub -- If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: Hello, I am looking for some help in solving an issue i have with a shift rosta. I have 17 rows in excel whick relate to 17 shift persons Cell A1 to A17 as follows A B C D E F G H I J K L M N O P Q What I am looking for is for on every thursday I need data in cell A17 to be input into cell A1 and all the other cells to go down 1 cell, e.g. data from cell A1 to Cell A2 and so on, this is the data I need to rotate no other data needs to rotate. Can anyone give me some help on this. Many thanks terilad |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell data change
Thanks a lot for your help. just to further bother you, can this still be
done if there is a spare row of cells below each row. e.g. cell A1 to move to Cell A3 and so on, data move 2 cells? Many thanks Terilad "Jacob Skaria" wrote: Sure you can.. Launch VBE using short-key Alt+F11. On the left treeview double click 'This Workbook '. Drop down to get the Workbook open event Paste the below code in Workbook Open event. Private Sub Workbook_Open() If Format(Date, "ddd") = "Thu" Then Dim varValue varValue = Range("A17") Range("A17") = "" Range("A1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range("A1") = varValue End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: This is great, thanks, is thre any way that this can be done automatically when the excel sheet is opened on a Thursday for instance? Many thanks Terilad "Jacob Skaria" wrote: Try the below macro. If you are new to macros Set the Security level to low/medium in (Tools|Macro|Security). 'Launch VBE using short-key Alt+F11. Insert a module and paste the below code. Save. Get back to Workbook. Tools|Macro|Run MacroTest() Sub MacroTest() Dim varValue varValue = Range("A17") Range("A17") = "" Range("A1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range("A1") = varValue End Sub -- If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: Hello, I am looking for some help in solving an issue i have with a shift rosta. I have 17 rows in excel whick relate to 17 shift persons Cell A1 to A17 as follows A B C D E F G H I J K L M N O P Q What I am looking for is for on every thursday I need data in cell A17 to be input into cell A1 and all the other cells to go down 1 cell, e.g. data from cell A1 to Cell A2 and so on, this is the data I need to rotate no other data needs to rotate. Can anyone give me some help on this. Many thanks terilad |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell data change
Repeat the insert line and adjust the Range accordingly. What you can do is
try the original macro with Step In mode. F8. You will see what happens and then you can adjust the code accordingly -- If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: Thanks a lot for your help. just to further bother you, can this still be done if there is a spare row of cells below each row. e.g. cell A1 to move to Cell A3 and so on, data move 2 cells? Many thanks Terilad "Jacob Skaria" wrote: Sure you can.. Launch VBE using short-key Alt+F11. On the left treeview double click 'This Workbook '. Drop down to get the Workbook open event Paste the below code in Workbook Open event. Private Sub Workbook_Open() If Format(Date, "ddd") = "Thu" Then Dim varValue varValue = Range("A17") Range("A17") = "" Range("A1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range("A1") = varValue End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: This is great, thanks, is thre any way that this can be done automatically when the excel sheet is opened on a Thursday for instance? Many thanks Terilad "Jacob Skaria" wrote: Try the below macro. If you are new to macros Set the Security level to low/medium in (Tools|Macro|Security). 'Launch VBE using short-key Alt+F11. Insert a module and paste the below code. Save. Get back to Workbook. Tools|Macro|Run MacroTest() Sub MacroTest() Dim varValue varValue = Range("A17") Range("A17") = "" Range("A1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range("A1") = varValue End Sub -- If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: Hello, I am looking for some help in solving an issue i have with a shift rosta. I have 17 rows in excel whick relate to 17 shift persons Cell A1 to A17 as follows A B C D E F G H I J K L M N O P Q What I am looking for is for on every thursday I need data in cell A17 to be input into cell A1 and all the other cells to go down 1 cell, e.g. data from cell A1 to Cell A2 and so on, this is the data I need to rotate no other data needs to rotate. Can anyone give me some help on this. Many thanks terilad |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell data change
Hi jacob, I cant seem to get this to work to move 2 rows.
In cells A4 A6 A8 A10 A12 A14 A16 and so on until A36 I have text Relief and need these to be fixed to these cells, Cells A3 A5 A7 A9 A11 and so on until A35 these are the cells that contain the staff name and these are the cells that I require to rotate, I have tried some alternatives but it moves borders down the page and removed borders from the cells that are rotating. Do you have any ideas, I appreciate your help. Regards Terilad "Jacob Skaria" wrote: Repeat the insert line and adjust the Range accordingly. What you can do is try the original macro with Step In mode. F8. You will see what happens and then you can adjust the code accordingly -- If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: Thanks a lot for your help. just to further bother you, can this still be done if there is a spare row of cells below each row. e.g. cell A1 to move to Cell A3 and so on, data move 2 cells? Many thanks Terilad "Jacob Skaria" wrote: Sure you can.. Launch VBE using short-key Alt+F11. On the left treeview double click 'This Workbook '. Drop down to get the Workbook open event Paste the below code in Workbook Open event. Private Sub Workbook_Open() If Format(Date, "ddd") = "Thu" Then Dim varValue varValue = Range("A17") Range("A17") = "" Range("A1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range("A1") = varValue End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: This is great, thanks, is thre any way that this can be done automatically when the excel sheet is opened on a Thursday for instance? Many thanks Terilad "Jacob Skaria" wrote: Try the below macro. If you are new to macros Set the Security level to low/medium in (Tools|Macro|Security). 'Launch VBE using short-key Alt+F11. Insert a module and paste the below code. Save. Get back to Workbook. Tools|Macro|Run MacroTest() Sub MacroTest() Dim varValue varValue = Range("A17") Range("A17") = "" Range("A1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range("A1") = varValue End Sub -- If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: Hello, I am looking for some help in solving an issue i have with a shift rosta. I have 17 rows in excel whick relate to 17 shift persons Cell A1 to A17 as follows A B C D E F G H I J K L M N O P Q What I am looking for is for on every thursday I need data in cell A17 to be input into cell A1 and all the other cells to go down 1 cell, e.g. data from cell A1 to Cell A2 and so on, this is the data I need to rotate no other data needs to rotate. Can anyone give me some help on this. Many thanks terilad |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell data change
Try the below and feedback
Private Sub Workbook_Open() If Format(Date, "ddd") = "Thu" Then Dim lngRow As Long Dim intTemp As Integer Dim arrData(17) As Variant arrData(0) = Range("A35") For lngRow = 3 To 35 Step 2 intTemp = intTemp + 1 arrData(intTemp) = Range("A" & lngRow) Range("A" & lngRow) = arrData(intTemp - 1) Next Range("A1") = varValue End If End Sub If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: Hi jacob, I cant seem to get this to work to move 2 rows. In cells A4 A6 A8 A10 A12 A14 A16 and so on until A36 I have text Relief and need these to be fixed to these cells, Cells A3 A5 A7 A9 A11 and so on until A35 these are the cells that contain the staff name and these are the cells that I require to rotate, I have tried some alternatives but it moves borders down the page and removed borders from the cells that are rotating. Do you have any ideas, I appreciate your help. Regards Terilad "Jacob Skaria" wrote: Repeat the insert line and adjust the Range accordingly. What you can do is try the original macro with Step In mode. F8. You will see what happens and then you can adjust the code accordingly -- If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: Thanks a lot for your help. just to further bother you, can this still be done if there is a spare row of cells below each row. e.g. cell A1 to move to Cell A3 and so on, data move 2 cells? Many thanks Terilad "Jacob Skaria" wrote: Sure you can.. Launch VBE using short-key Alt+F11. On the left treeview double click 'This Workbook '. Drop down to get the Workbook open event Paste the below code in Workbook Open event. Private Sub Workbook_Open() If Format(Date, "ddd") = "Thu" Then Dim varValue varValue = Range("A17") Range("A17") = "" Range("A1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range("A1") = varValue End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: This is great, thanks, is thre any way that this can be done automatically when the excel sheet is opened on a Thursday for instance? Many thanks Terilad "Jacob Skaria" wrote: Try the below macro. If you are new to macros Set the Security level to low/medium in (Tools|Macro|Security). 'Launch VBE using short-key Alt+F11. Insert a module and paste the below code. Save. Get back to Workbook. Tools|Macro|Run MacroTest() Sub MacroTest() Dim varValue varValue = Range("A17") Range("A17") = "" Range("A1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range("A1") = varValue End Sub -- If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: Hello, I am looking for some help in solving an issue i have with a shift rosta. I have 17 rows in excel whick relate to 17 shift persons Cell A1 to A17 as follows A B C D E F G H I J K L M N O P Q What I am looking for is for on every thursday I need data in cell A17 to be input into cell A1 and all the other cells to go down 1 cell, e.g. data from cell A1 to Cell A2 and so on, this is the data I need to rotate no other data needs to rotate. Can anyone give me some help on this. Many thanks terilad |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell data change
Many thanks Jacob for your help on this, works a treat.
Many thanks Terilad "Jacob Skaria" wrote: Try the below and feedback Private Sub Workbook_Open() If Format(Date, "ddd") = "Thu" Then Dim lngRow As Long Dim intTemp As Integer Dim arrData(17) As Variant arrData(0) = Range("A35") For lngRow = 3 To 35 Step 2 intTemp = intTemp + 1 arrData(intTemp) = Range("A" & lngRow) Range("A" & lngRow) = arrData(intTemp - 1) Next Range("A1") = varValue End If End Sub If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: Hi jacob, I cant seem to get this to work to move 2 rows. In cells A4 A6 A8 A10 A12 A14 A16 and so on until A36 I have text Relief and need these to be fixed to these cells, Cells A3 A5 A7 A9 A11 and so on until A35 these are the cells that contain the staff name and these are the cells that I require to rotate, I have tried some alternatives but it moves borders down the page and removed borders from the cells that are rotating. Do you have any ideas, I appreciate your help. Regards Terilad "Jacob Skaria" wrote: Repeat the insert line and adjust the Range accordingly. What you can do is try the original macro with Step In mode. F8. You will see what happens and then you can adjust the code accordingly -- If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: Thanks a lot for your help. just to further bother you, can this still be done if there is a spare row of cells below each row. e.g. cell A1 to move to Cell A3 and so on, data move 2 cells? Many thanks Terilad "Jacob Skaria" wrote: Sure you can.. Launch VBE using short-key Alt+F11. On the left treeview double click 'This Workbook '. Drop down to get the Workbook open event Paste the below code in Workbook Open event. Private Sub Workbook_Open() If Format(Date, "ddd") = "Thu" Then Dim varValue varValue = Range("A17") Range("A17") = "" Range("A1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range("A1") = varValue End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: This is great, thanks, is thre any way that this can be done automatically when the excel sheet is opened on a Thursday for instance? Many thanks Terilad "Jacob Skaria" wrote: Try the below macro. If you are new to macros Set the Security level to low/medium in (Tools|Macro|Security). 'Launch VBE using short-key Alt+F11. Insert a module and paste the below code. Save. Get back to Workbook. Tools|Macro|Run MacroTest() Sub MacroTest() Dim varValue varValue = Range("A17") Range("A17") = "" Range("A1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range("A1") = varValue End Sub -- If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: Hello, I am looking for some help in solving an issue i have with a shift rosta. I have 17 rows in excel whick relate to 17 shift persons Cell A1 to A17 as follows A B C D E F G H I J K L M N O P Q What I am looking for is for on every thursday I need data in cell A17 to be input into cell A1 and all the other cells to go down 1 cell, e.g. data from cell A1 to Cell A2 and so on, this is the data I need to rotate no other data needs to rotate. Can anyone give me some help on this. Many thanks terilad |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| change the display data format cell already contain data as date | Excel Discussion (Misc queries) | |||
| change cell data without leaving cell? | Excel Discussion (Misc queries) | |||
| change cell colour when cell data changes | Excel Discussion (Misc queries) | |||
| Change color of cell when different cell has data entered | Excel Discussion (Misc queries) | |||
| How do I change cell color based upon data range within the cell? | Excel Worksheet Functions | |||
Linear Mode
