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
|
Similar Threads
