On Feb 25, 4:07*pm, JLGWhiz wrote:
As the code is now written, it will work on any sheet that is the active
sheet if the code is in the general code module1. *If you put it behind a
sheet, you will essentially be making it private which will only work on that
sheet and must be called from that sheet module.



"K" wrote:
Hi I have this macro (please see below) which is on a button in sheet.
It works fine. *I want to put this macro in Sheet module so if I copy
this sheet some where else so the macro will get copied too. Where as
I cant do it now

Sub InsertLines()
Dim LastRow As Long
Dim StartRow As Long
StartRow = Cells(Rows.Count, 2).End(xlUp).Row - 1
Cells(StartRow + 1, 1).Resize(6, 1).EntireRow.Insert
For i = 1 To 6
Cells(StartRow + i, 9).Resize(1, 6).Merge
Next i
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
Application.ScreenUpdating = False
With Range(Cells(StartRow, 2), Cells(LastRow, 2))
..DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=1, Trend:=False
End With
Application.ScreenUpdating = True

End Sub- Hide quoted text -

- Show quoted text -

there must be a way to do this. please see below the macro the way i
think it should work. instead clicking on button i am trying it to do
that if any value been enter into the cell then it should do the work
but the macro i am trying to do is not working yet. any ideas please
if you can give. if i make it work from sheet module then all the
problem is solved


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LastRow As Long
Dim StartRow As Long
Dim slc As Long
slc = Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Select
If slc < "" Then
StartRow = Cells(Rows.Count, 2).End(xlUp).Row - 1
Cells(StartRow + 1, 1).Resize(6, 1).EntireRow.Insert
For i = 1 To 6
Cells(StartRow + i, 9).Resize(1, 6).Merge
Next i
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
Application.ScreenUpdating = False
With Range(Cells(StartRow, 2), Cells(LastRow, 2))
.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=1, Trend:=False
End With
Application.ScreenUpdating = True
End If
End Sub