YellowLabPro
09-28-2007, 04:56 AM
I have to subs. The main calls a secondary sub, Sub getname().
The secondary's job is to retrieve the active workbook's name and pass it back to the Main sub.
The secondary sub is ending, losing the variable.
How do I pass the variable back from the secondary sub to the main sub?
variable i want to pass is wbsource
Sub CopyRange()
Dim wbsource As Workbook, wbTarget As Workbook
Dim wsSource As Worksheet, wsTarget As Worksheet
Dim lrSource As Long
Dim wbnt As String, wsnt As String, wPath As String
Call getname
'wbns =
wbnt = "Book1.xls" '"TGSItemRecordCreator.xls"
wsnt = "Test" '"RecordCreator"
wPath = "C:\Documents and Settings\Doug\Desktop\Excel Projects\"
'Set wbTarget = Workbooks(wbn)
'Set wsTarget = Workbooks(wbn).Worksheets(wsn)
lrSource = LR(ActiveSheet, "H")
If IsWbOpen(wbnt) Then
Set wsTarget = Workbooks(wbnt).Worksheets(wsnt)
Else
If Not (Dir(wPath & wbnt) = "") Then
Workbooks.Open (wPath & wbnt)
Set wsTarget = Workbooks(wbnt).Worksheets(wsnt)
Else
MsgBox ("The Workbook """ & wbnt & """ Does Not Exist" & vbCrLf & vbCrLf & "In The " & wPath & " Folder"), vbCritical
Exit Sub
End If
End If
wsTarget.Range("H6:S" & lrSource).Value = wbsource.Range("H6:S" & lrSource).Value
wsTarget.Range("Z6:AC" & lrSource).Value = wbsource.Range("Z6:AC" & lrSource).Value
Exit Sub
End Sub
Sub getname()
Dim wbsource As Workbook
Dim wsSource As Worksheet
Debug.Print ActiveWorkbook.Name
Set wbsource = ActiveWorkbook
Debug.Print wbsource.Name
End Sub
The secondary's job is to retrieve the active workbook's name and pass it back to the Main sub.
The secondary sub is ending, losing the variable.
How do I pass the variable back from the secondary sub to the main sub?
variable i want to pass is wbsource
Sub CopyRange()
Dim wbsource As Workbook, wbTarget As Workbook
Dim wsSource As Worksheet, wsTarget As Worksheet
Dim lrSource As Long
Dim wbnt As String, wsnt As String, wPath As String
Call getname
'wbns =
wbnt = "Book1.xls" '"TGSItemRecordCreator.xls"
wsnt = "Test" '"RecordCreator"
wPath = "C:\Documents and Settings\Doug\Desktop\Excel Projects\"
'Set wbTarget = Workbooks(wbn)
'Set wsTarget = Workbooks(wbn).Worksheets(wsn)
lrSource = LR(ActiveSheet, "H")
If IsWbOpen(wbnt) Then
Set wsTarget = Workbooks(wbnt).Worksheets(wsnt)
Else
If Not (Dir(wPath & wbnt) = "") Then
Workbooks.Open (wPath & wbnt)
Set wsTarget = Workbooks(wbnt).Worksheets(wsnt)
Else
MsgBox ("The Workbook """ & wbnt & """ Does Not Exist" & vbCrLf & vbCrLf & "In The " & wPath & " Folder"), vbCritical
Exit Sub
End If
End If
wsTarget.Range("H6:S" & lrSource).Value = wbsource.Range("H6:S" & lrSource).Value
wsTarget.Range("Z6:AC" & lrSource).Value = wbsource.Range("Z6:AC" & lrSource).Value
Exit Sub
End Sub
Sub getname()
Dim wbsource As Workbook
Dim wsSource As Worksheet
Debug.Print ActiveWorkbook.Name
Set wbsource = ActiveWorkbook
Debug.Print wbsource.Name
End Sub