Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to change code in a sheet and workbook module | Excel Programming | |||
Sheet Code Module: copying code to | Excel Programming | |||
Sheet code module question | Excel Programming | |||
Reference Addin Module from sheet code | Excel Programming | |||
Write VBA code into a sheet module using VBA | Excel Programming |