PDA

View Full Version : create and run a worksheet template macro in a middle of other process !!!



Dipwind
03-22-2007, 08:47 AM
Hello,

I'm trying to record a worksheet template macro, but the thing is a bit big and slows down everything.

I also try to record a copy of the template, but when I try to run the macro The page come up blank .

There is another way to do it?

What I want to do is, run the template in a middle of a process. When a specific code is running I want when the workbook is opened run the template, and then save the file as indicated.

Also if possible when the template is added I would like to link some cells to a sheet ?DI? where after change a cell trigger the code to open a new workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WB As Workbook
On Error GoTo ErrH
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Or Target.Row < 3 Then Exit Sub
Application.EnableEvents = False
Select Case Target.Column
Case 7
Set WB = Workbooks.Add

Here I want to run the template and add some links to the cells in the sheet that trigger this code.

WB.SaveAs "G:\x\x\x\IssueN" & Target.Row - 8 & ".xls"
WB.Close
Set WB = Nothing
Case 11
Email Target
End Select
ErrH:
Application.EnableEvents = True
End Sub


Hope this make sense and be understandable

Thanks
Jo

Dipwind
03-22-2007, 03:21 PM
the macro with the template is here, its only a sample, but illustrates better what I intended to do.

Sub template()
'
' template Macro
' teste
'
' Keyboard Shortcut: Ctrl+Shift+T
'
Range("A1:G2").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A4:G4").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A8:G8").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A6:G6").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("C1:E1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("L8").Select
End Sub


What I want is this in the midle of the other code, is it possible?

and then the cell C1 on the template should be = to G9 on "SPMF" and on the next file should be = to G10 on "SPMF" and so on. that means the issueN1 should be linked with "SPMF" and with "DI" extract data from the same row. assuming that each row is a issueN.

hope more understandable
thanks in advance

I attached the file.

Dipwind
03-23-2007, 12:42 PM
Can someone help me?

or what I ask is not possible to do?

thanks
jo

Dipwind
03-26-2007, 08:58 AM
bump

lucas
03-26-2007, 09:11 AM
This copies a template sheet that can be hidden and adds it to the end of the list and renames it....name of the template is Blank Sheet
Sub AddNewSheets()
Dim TotalSheets As Variant
TotalSheets = Worksheets.Count - 1
Worksheets("Blank Sheet").Activate
ActiveSheet.Copy after:=Worksheets("Page " & TotalSheets)
Worksheets("Blank Sheet (2)").Activate
ActiveSheet.Name = "Page " & TotalSheets + 1
ActiveSheet.Visible = True

End Sub

Dipwind
03-27-2007, 10:32 AM
Hi Lucas,

Thanks a lot, but I have some doubts in how to proceed.

I'm getting an error "Subscript out of range", definitively I'm doing something wrong.

can you please help me with the steps.

I'm blank regarding code, if it is not evident it's a night mare for me.

questions?

Where I should put the code? in a new module? after the other code that creates the new WB?

Should I change the name of the sheet, help please.


thanks in advance

joao