passing variables

[Squid]

I created some code in the workbook_open event that
worked great. Basically, when workbook1 opens it opens
workbook2 and copies its contents, and paste it into a
sheet within workbook1. Then workbook2 closes.
Everything all fine and good until something needs added
to workbook2. Workaround is closing workbook1 and reopen
to refresh the data.

I want to add an option to refresh without closing it.
Below is a snippet of the code (which doesnt work). I am
trying to get NumRows variable to work, but having
problems getting the value of NumRows to pass.

Private Sub Workbook_Open()

Dim NumRows As Long
Dim sh1 As Worksheet 'Sheet1!Contracts.xls
Dim sh2 As Worksheet 'Contracts!Settlement4b.xls
Dim rng1 As String
Dim i, j As Integer

'Refresh data of Contracts worksheet in
C:\CCF\Settlement4.xls from data contained
'in worksheet Sheet1 in C:\CCF\Contracts1.xls
Application.StatusBar = "Loading Contract File..."

Workbooks.Open Filename:="C:\CCF\Contracts1.xls"
Set sh1 = Workbooks("Contracts1.xls").Worksheets
("Sheet1")
Set sh2 = Workbooks("Copy of
Settlement4b.xls").Worksheets("Contracts")
'Determine number of rows in C:\CCF\Contracts1.xls
Sheet1

'numRows = Application.CountA(sh1.Range("A:A"))
'CountRows

'Copy from C:\CCF\Contracts1.xls!Sheet1 and
'Paste in C:\CCF\Settlement4.xls!Contracts
sh1.Range("A1:AJ" & NumRows).Copy sh2.Range("A1")

'Create a string variable to be used later in code
rng1 = ("A1:AJ" & NumRows)

Some more code
End sub

In Module1:

Option Explicit
Global NumRows As Long


Function CountRows(ByVal NumRows As Long)

Dim sht1 As Object
Set sht1 = Worksheets("Sheet1")

NumRows = Application.CountA(sht1.Range("A:A"))
End Function

 

[Bob Flanagan]

Change the workbook_open to:

Private Sub Workbook_Open()
DoModifications
End sub

In a regular module put

Sub DoModifications
'all code from the workbook_open
End sub

Then, you can run DoModificaitons whenever you need it.

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel