CODE FOR SHEET MODULE

K

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
 
M

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.
 
J

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

K

K

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
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top