CODE FOR SHEET MODULE

[K]

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

 

[michael.beckinsale]

Hi K,

If you mean that this macro is assigned to a button on a sheet then if
you create a copy of the sheet the button will be copied and still
assigned to the macro.

Regards

Michael.

 

[JLGWhiz]

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]

Hi K,

If you mean that this macro is assigned to a button on a sheet then if
you create a copy of the sheet the button will be copied and still
assigned to the macro.

Regards

Michael.

Hi again michael. Please see the link below which will explain more
my question to you
please if you can come up with any sulotion then that will be very
great as I still didn't have
suitable answer yet

http://groups.google.com/group/micr...a7abfd2ff47/5fa9ad186f6a231a#5fa9ad186f6a231a

 

[K]

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.







- 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

 

[michael.beckinsale]

Hi K,

If you want to trigger the macro by an entry in cell A1 then


If Target = Range("A1") Then

......put your code here

End If

If you look at the example code l have sent you today you should be
able to figure how to do it for an entry in any cell. BEWARE you will
need some error handling........what if there is only a single entry
in say A3.............surely your code will crash!

Signing off until tommorow

Regards

Michael

 

[JLGWhiz]

Maybe this article will help you figure it out.

http://support.microsoft.com/kb/136314