Hi ,
I am trying to take out as much of this coding into another Workbook
and use the Run or Call option to link to manage the macro.
This is because if changes occur I have to change all the macros in a
number of workbooks and I was lloking for a way to control the changes
centrally in one Workbook- hope this make sence so far.

I tried to set up a public variable for the "Journal =
ActiveWorkbook.Name"
Eg Public WKB As Workbook
then in the code
Set WKB = ThisWorkbook
Journal = WKB.Name

Sheets("GL Journal").Range("M3").ClearContents
UserName = Environ("UserName")
Sheets("TABLES").Range("O3").Value = UserName ' puts
username into table selection

Call upload2

BUT when it goes to the upload2 the WKB info is not retained for
use.Why is that?

Also when I try Application.Run."GL Macros.xls'!UPLOAD2" it does not go
to the Procedure,
Any ideas why this too may happen.


MANY THANKS
TW


Full Coding Below

Const thepath = "\\saffy\FINANCE\BIS\7-Journals\"
Const JournalIDBook = "Journal ID Book.xls"
Const RestructureJournal = "Restructure Journal.xlt"
Const RestructureJournalJPN = "Restructure Journal JPN.xlt"


Sub UPLOAD()
Dim ID As Range
Dim Journal

On Error GoTo ErrHandler:

' UPLOAD Macro
' Version 2006.12 by Denzil


'User Info
Journal = ActiveWorkbook.Name
Sheets("GL Journal").Range("M3").ClearContents
UserName = Environ("UserName")
Sheets("TABLES").Range("O3").Value = UserName ' puts
username into table selection

'Opens Journal ID book and selects the next available Department ID
number and copies it to Journal
Workbooks.Open Filename:=thepath & JournalIDBook
Set ID = Sheets("Current
Year").Columns("B:B").SpecialCells(xlCellTypeBlank s).Offset(0,
-1).Range("A1")
Workbooks(Index:=Journal).Sheets("GL
Journal").Range("I9").Value = ID

' copies other info from Journal & copies into ID book
ID.Activate

Workbooks(Index:=Journal).Sheets("TABLES").Range(" JournalIdData").Copy
Windows(Index:=JournalIDBook).ActiveCell.Offset(0,
1).Range("A1").PasteSpecial Paste:=xlValues, Transpose:=True
ActiveWorkbook.Save
ActiveWorkbook.Close

'Opens Restructure Journal and Copies data from Journal Upload Workbook
into a Upload temlpate
Application.DisplayAlerts = False
thefilename = Sheets("GL Journal").Range("I9").Value

'Determines which Restructure Template to use
If Range("E9") = "JPY" Then
Sheets("Journal Upload").Range("A1").CurrentRegion.Copy
Workbooks.Add template:=thepath &
RestructureJournalJPN
Else
Sheets("Journal Upload").Range("A1").CurrentRegion.Copy
Workbooks.Add template:=thepath & RestructureJournal
End If
Range("A1").PasteSpecial Paste:=xlValues
On Error GoTo 0
With ActiveWorkbook
.SaveAs Filename:=thepath & thefilename &
".csv", FileFormat:=xlCSV
.Close
End With

' Copies data to paste on SAP upload
Sheets("TABLES").Range("SapUploadData").Copy
Sheets("GL Journal").Range("M3").Select
Application.DisplayAlerts = True
Application.ShowWindowsInTaskbar = True


Exit Sub

' Code to excute if error occurs
Label1:
Application.DisplayAlerts = False
ActiveWorkbook.Close
Workbooks(Index:=Journal).Sheets("GL
Journal").Range("I9").ClearContents
User = Sheets("TABLES").Range("P3").Value ' Defines user
name for message box
MsgBox "Hello!! " & User & ". A problem has occurred
during your Upload Process.After you click the OKAY button, you will
have the option of READ-WRITE or CANCEL, Please Select CANCEL and then
press the UPLOAD button AGAIN to process your Journal. Thank You. ",
vbExclamation, "WARNING - PLEASE DO NOT IGNORE"
Application.DisplayAlerts = True
Exit Sub

ErrHandler:
' Goes to the line at Label1
Resume Label1


End Sub