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(xlCellTypeBlanks).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

Be sure you place the line:

Public WKB As Workbook

in the correct place - in a standard module before any subs or functions.
--
Gary's Student


"(E-Mail Removed)" wrote:

> 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(xlCellTypeBlanks).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
>
>

This is from VBA help:

This example shows how to call the function macro My_Func_Sum, which is
defined on the macro sheet Mycustom.xlm (the macro sheet must be open). The
function takes two numeric arguments (1 and 5, in this example).

mySum = Application.Run("MYCUSTOM.XLM!My_Func_Sum", 1, 5)

Note: No period after Run and Parenthses enclose the workbook and macro
names.
The arguments 1 and 5 are optional.

If it still does not call with the correct sytax, you have somehow severed
the connection between the macro and Upload2. If Upload2 is your workbook,
then you are calling in reverse order, but I assume since you use the .xls
after GL Macros that that is the Workbook where the macro Upload2 resides.


"(E-Mail Removed)" wrote:

> 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(xlCellTypeBlanks).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
>
>

Hi Gary,

I did place this before any Subs and functions and referenced it but
it still does not recognise it when it moves to a new workbook.
i.e. the Varient is empty.
I thought that if it was Public it would be available across all
project and workbooks?

TW

Gary''s Student wrote:
> Be sure you place the line:
>
> Public WKB As Workbook
>
> in the correct place - in a standard module before any subs or functions.
> --
> Gary's Student
>
>
> "(E-Mail Removed)" wrote:
>
> > 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(xlCellTypeBlanks).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
> >
> >

Hi JLGWhiz,
I do not understand the use of the Function Macro.

I was looking at an alternative way to call the macro between
workbooks,
without using the Reference link , because the Journal Upload will be
used by a number of user and will save their own versiion in a
different directoty.
In John Walkers book he recommened using
Application.Run"Filename& extention!Procedure" but it does not seem to
work.
Is my syntax correct?

Thanks
TW

JLGWhiz wrote:
> This is from VBA help:
>
> This example shows how to call the function macro My_Func_Sum, which is
> defined on the macro sheet Mycustom.xlm (the macro sheet must be open). The
> function takes two numeric arguments (1 and 5, in this example).
>
> mySum = Application.Run("MYCUSTOM.XLM!My_Func_Sum", 1, 5)
>
> Note: No period after Run and Parenthses enclose the workbook and macro
> names.
> The arguments 1 and 5 are optional.
>
> If it still does not call with the correct sytax, you have somehow severed
> the connection between the macro and Upload2. If Upload2 is your workbook,
> then you are calling in reverse order, but I assume since you use the .xls
> after GL Macros that that is the Workbook where the macro Upload2 resides.
>
>
> "(E-Mail Removed)" wrote:
>
> > 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(xlCellTypeBlanks).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
> >
> >