CODE FOR SHEET MODULE
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 |
CODE FOR SHEET MODULE
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. |
CODE FOR SHEET MODULE
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 |
CODE FOR SHEET MODULE
On Feb 25, 3:54 pm, "michael.beckinsale"
wrote: 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/micro...a9ad186f6a231a |
CODE FOR SHEET MODULE
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 |
CODE FOR SHEET MODULE
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 |
CODE FOR SHEET MODULE
Maybe this article will help you figure it out.
http://support.microsoft.com/kb/136314 "K" wrote: 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 |
All times are GMT +1. The time now is 01:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com