PDA

View Full Version : Running a macro for any workbook/worksheet



zen
05-07-2007, 05:13 AM
Hi! I was able to create a macro that formats the worksheet that I've downloaded from a system. However, I would like to run the same macro to different worksheets. I tried saving the macro in the personal.xls file, but it formats the sheet in the personal.xls file. Below is the code that I would like to use for different worksheets:



Dim RowCount As Integer
Sub Format()
Columns("C:C").Select
Selection.Insert Shift:=x1ToRight
Columns("I:I").Select
Selection.Insert Shift:=x1ToRight
Columns("K:L").Select
Selection.Cut
Columns("R:R").Select
ActiveSheet.Paste
Columns("K:L").Select
Selection.Delete Shift:=x1ToLeft
Columns("L:L").Select
Selection.Insert Shift:=x1ToRight
Call HeadingFormat
Sheet1.Activate
Rows("1:1").Select
ActiveSheet.Paste
Call Autofit
Windows("Lookup tables.xls").Activate
Range("C2").Select
Selection.Copy
Sheet1.Activate
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("C2").Select
Selection.Copy
RowCount = 3
Do Until IsEmpty(Cells(RowCount, 2))
If Cells(RowCount, 2).Value <> EmptyCell Then
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
RowCount = RowCount + 1
End If
Loop

Windows("Lookup tables.xls").Activate
Range("I2").Select
Selection.Copy
Sheet1.Activate
Range("I2").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("I2").Select
Selection.Copy
RowCount = 3
Do Until IsEmpty(Cells(RowCount, 8))
If Cells(RowCount, 8).Value <> EmptyCell Then
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
RowCount = RowCount + 1
End If
Loop
End Sub
Public Sub HeadingFormat()
Windows("Lookup tables.xls").Activate
Rows("1:1").Select
Selection.Copy
End Sub
Public Sub Autofit()
Cells.Select
Selection.Columns.Autofit
End Sub


Please help, it would greatly improve my process and not have to format everytime I download an excel file.

Thank you so much!

Simon Lloyd
05-07-2007, 03:47 PM
Cross Posted http://www.ozgrid.com/forum/showthread.php?p=355504&posted=1#post355504

Regards,
Simon