Excel 2000
I've got a workbook with 4 worksheets, I have a macro which is setup to copy
two of the worksheets and email them to a different department. I also have
another macro which combines some of the cells so the address is in one line
to make it easier for the other department. The problem I have is when the
first macro is activated, it copies all of the data, as it should, but leaves
the macro information behind, so when the other department try to run their
"quicker magic address button" it doesn't work.

Is there a way to have a macro which will work for all workbooks? Or is
there away that I can get the macros to go over at the same time as the
information on the worksheets?

Thanks in advance.

Create a template file with that macro in, and copy the sheets into the
template.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Paul" wrote in message
...
Excel 2000
I've got a workbook with 4 worksheets, I have a macro which is setup to
copy
two of the worksheets and email them to a different department. I also
have
another macro which combines some of the cells so the address is in one
line
to make it easier for the other department. The problem I have is when the
first macro is activated, it copies all of the data, as it should, but
leaves
the macro information behind, so when the other department try to run
their
"quicker magic address button" it doesn't work.

Is there a way to have a macro which will work for all workbooks? Or is
there away that I can get the macros to go over at the same time as the
information on the worksheets?

Thanks in advance.

You have a couple options. One is to put your code into an add-in and
distribute to all involved parties. Another option, that is probably more
appropriate, is to insert the code into all the files that you distribute.

The following example writes the wkbSrc workbook modSpecialModule code
module out to a file (modSpecialModule.bas) and them imports it into the
wkbDst workbook.

Two important notes:
-WriteBinaryFileFromSheet sub creates a file from a sheet where each cell in
the first column has the the binary contents of a file (one byte per cell -
limited to ~65k bytes, obvisously).
-Need to previously have read the code module into the sheet using
ReadBinaryFileToSheet sub. You could modify the code to do a code module
export instead of creating the file byte-wise form a sheet. I had a specific
need to do it this way, so this is the sample code I had readily available.

Regards,
Bill


Public Sub CopyCodeModule(wkbSrc As Workbook, wkbDst As Workbook,
strCodeModule As String)
Dim vbComp As Object 'VBIDE.VBComponent
Dim vbCM As Object 'VBIDE.CodeModule
Dim wks As Worksheet
Dim rngFindFirst As Range
Dim rngAC As Range
Dim strExtension As String
Dim strFilename As String
Dim strDirSource As String

strExtension = ".bas"
strFilename = wkbSrc.Path & "\" & strCodeModule & strExtension
On Error Resume Next

Call Kill(strFilename)

Set wkbSrc = ThisWorkbook

strDirSource = wkbSrc.Path & "\"
Call WriteBinaryFileFromSheet(strDirSource, strCodeModule,
strCodeModule & strExtension)
DoEvents

Call wkbDst.VBProject.VBComponents.Import(strFilename)
DoEvents

Call Kill(strFilename)

Application.Calculate


End Sub


Private Sub ReadBinaryFileToSheet(strFilename As String, strSheet As String)
Dim lngFileLength As Long
Dim lngFnum As Long
Dim bytArray() As Byte
Dim i As Long
Dim rngData As Range
Dim wkb As Workbook

Set wkb = ThisWorkbook
Set rngData = wkb.Worksheets(strSheet).Range("A1")
rngData.Value = Dir$(strFilename)
DoEvents

Set rngData = rngData.Offset(1, 0)

wkb.Worksheets(strSheet).Range(rngData.Address & ":" &
rngData.SpecialCells(xlCellTypeLastCell).Address). EntireRow.Delete

Set rngData = wkb.Worksheets(strSheet).Range("A1")
Set rngData = rngData.Offset(1, 0)

lngFileLength = FileLen(strFilename)

lngFnum = FreeFile
ReDim bytArray(1 To lngFileLength)

Open strFilename For Binary As lngFnum
Get lngFnum, 1, bytArray
Close lngFnum

For i = 1 To lngFileLength
rngData.Offset(i, 0).Value = Format$(bytArray(i))
Next i

End Sub





Public Sub WriteBinaryFileFromSheet(strPath As String, strSheet As String,
strFilename As String)
Dim lngFileLength As Long
Dim lngFnum As Long
Dim bytArray() As Byte
Dim i As Long
Dim rngData As Range
Dim wkb As Workbook
Dim lngCount As Long
Dim lngValue As Long
Dim lngValueCount As Long

Set wkb = ThisWorkbook
Set rngData = wkb.Worksheets(strSheet).Range("A1")
strFilename = rngData.Value
Set rngData = rngData.Offset(1, 0)

Set rngData = wkb.Worksheets(strSheet).Range("A1")

lngCount = rngData.SpecialCells(xlCellTypeLastCell).Row

For i = 2 To lngCount - 1
'If (Len(rngData.Offset(i, 0).Value) 0) Then
lngValue = rngData.Offset(i, 0).Value

lngValueCount = lngValueCount + 1
ReDim Preserve bytArray(1 To lngValueCount)
bytArray(lngValueCount) = lngValue
'End If
Next i

' Delete any existing file

On Error Resume Next
Kill strPath & strFilename
On Error GoTo 0

' Save the file.
lngFnum = FreeFile
Open strPath & strFilename For Binary As lngFnum
Put lngFnum, 1, bytArray
Close lngFnum

End Sub



"Paul" wrote:

Excel 2000
I've got a workbook with 4 worksheets, I have a macro which is setup to copy
two of the worksheets and email them to a different department. I also have
another macro which combines some of the cells so the address is in one line
to make it easier for the other department. The problem I have is when the
first macro is activated, it copies all of the data, as it should, but leaves
the macro information behind, so when the other department try to run their
"quicker magic address button" it doesn't work.

Is there a way to have a macro which will work for all workbooks? Or is
there away that I can get the macros to go over at the same time as the
information on the worksheets?

Thanks in advance.

Bill

It looks good but I have no idea whatsoever what most of that means or what
to do with it.

Is there an idiots guide to what you have written?

:)

Paul

"Bill Pfister" wrote:

You have a couple options. One is to put your code into an add-in and
distribute to all involved parties. Another option, that is probably more
appropriate, is to insert the code into all the files that you distribute.

The following example writes the wkbSrc workbook modSpecialModule code
module out to a file (modSpecialModule.bas) and them imports it into the
wkbDst workbook.

Two important notes:
-WriteBinaryFileFromSheet sub creates a file from a sheet where each cell in
the first column has the the binary contents of a file (one byte per cell -
limited to ~65k bytes, obvisously).
-Need to previously have read the code module into the sheet using
ReadBinaryFileToSheet sub. You could modify the code to do a code module
export instead of creating the file byte-wise form a sheet. I had a specific
need to do it this way, so this is the sample code I had readily available.

Regards,
Bill


Public Sub CopyCodeModule(wkbSrc As Workbook, wkbDst As Workbook,
strCodeModule As String)
Dim vbComp As Object 'VBIDE.VBComponent
Dim vbCM As Object 'VBIDE.CodeModule
Dim wks As Worksheet
Dim rngFindFirst As Range
Dim rngAC As Range
Dim strExtension As String
Dim strFilename As String
Dim strDirSource As String

strExtension = ".bas"
strFilename = wkbSrc.Path & "\" & strCodeModule & strExtension
On Error Resume Next

Call Kill(strFilename)

Set wkbSrc = ThisWorkbook

strDirSource = wkbSrc.Path & "\"
Call WriteBinaryFileFromSheet(strDirSource, strCodeModule,
strCodeModule & strExtension)
DoEvents

Call wkbDst.VBProject.VBComponents.Import(strFilename)
DoEvents

Call Kill(strFilename)

Application.Calculate


End Sub


Private Sub ReadBinaryFileToSheet(strFilename As String, strSheet As String)
Dim lngFileLength As Long
Dim lngFnum As Long
Dim bytArray() As Byte
Dim i As Long
Dim rngData As Range
Dim wkb As Workbook

Set wkb = ThisWorkbook
Set rngData = wkb.Worksheets(strSheet).Range("A1")
rngData.Value = Dir$(strFilename)
DoEvents

Set rngData = rngData.Offset(1, 0)

wkb.Worksheets(strSheet).Range(rngData.Address & ":" &
rngData.SpecialCells(xlCellTypeLastCell).Address). EntireRow.Delete

Set rngData = wkb.Worksheets(strSheet).Range("A1")
Set rngData = rngData.Offset(1, 0)

lngFileLength = FileLen(strFilename)

lngFnum = FreeFile
ReDim bytArray(1 To lngFileLength)

Open strFilename For Binary As lngFnum
Get lngFnum, 1, bytArray
Close lngFnum

For i = 1 To lngFileLength
rngData.Offset(i, 0).Value = Format$(bytArray(i))
Next i

End Sub





Public Sub WriteBinaryFileFromSheet(strPath As String, strSheet As String,
strFilename As String)
Dim lngFileLength As Long
Dim lngFnum As Long
Dim bytArray() As Byte
Dim i As Long
Dim rngData As Range
Dim wkb As Workbook
Dim lngCount As Long
Dim lngValue As Long
Dim lngValueCount As Long

Set wkb = ThisWorkbook
Set rngData = wkb.Worksheets(strSheet).Range("A1")
strFilename = rngData.Value
Set rngData = rngData.Offset(1, 0)

Set rngData = wkb.Worksheets(strSheet).Range("A1")

lngCount = rngData.SpecialCells(xlCellTypeLastCell).Row

For i = 2 To lngCount - 1
'If (Len(rngData.Offset(i, 0).Value) 0) Then
lngValue = rngData.Offset(i, 0).Value

lngValueCount = lngValueCount + 1
ReDim Preserve bytArray(1 To lngValueCount)
bytArray(lngValueCount) = lngValue
'End If
Next i

' Delete any existing file

On Error Resume Next
Kill strPath & strFilename
On Error GoTo 0

' Save the file.
lngFnum = FreeFile
Open strPath & strFilename For Binary As lngFnum
Put lngFnum, 1, bytArray
Close lngFnum

End Sub



"Paul" wrote:

Excel 2000
I've got a workbook with 4 worksheets, I have a macro which is setup to copy
two of the worksheets and email them to a different department. I also have
another macro which combines some of the cells so the address is in one line
to make it easier for the other department. The problem I have is when the
first macro is activated, it copies all of the data, as it should, but leaves
the macro information behind, so when the other department try to run their
"quicker magic address button" it doesn't work.

Is there a way to have a macro which will work for all workbooks? Or is
there away that I can get the macros to go over at the same time as the
information on the worksheets?

Thanks in advance.

Bob

The file where the macros are, is a template and part of the macro creates a
new workbook then pastes the two worksheets in to it. In an ideal world I
could do with it being available at all times in Excel, as the department
which use it will need this magic button at least 100 times a week.

I'm hoping for a simple solution :) I was hoping to find the template that
Excel starts with and try and put the macro in there, but not found it yet.

Paul



"Bob Phillips" wrote:

Create a template file with that macro in, and copy the sheets into the
template.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Paul" wrote in message
...
Excel 2000
I've got a workbook with 4 worksheets, I have a macro which is setup to
copy
two of the worksheets and email them to a different department. I also
have
another macro which combines some of the cells so the address is in one
line
to make it easier for the other department. The problem I have is when the
first macro is activated, it copies all of the data, as it should, but
leaves
the macro information behind, so when the other department try to run
their
"quicker magic address button" it doesn't work.

Is there a way to have a macro which will work for all workbooks? Or is
there away that I can get the macros to go over at the same time as the
information on the worksheets?

Thanks in advance.

Move the macro to an addin and build a toolbar to execute the code.

Keep only the code in the template that is required in the file that should be
sent.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

Paul wrote:

Excel 2000
I've got a workbook with 4 worksheets, I have a macro which is setup to copy
two of the worksheets and email them to a different department. I also have
another macro which combines some of the cells so the address is in one line
to make it easier for the other department. The problem I have is when the
first macro is activated, it copies all of the data, as it should, but leaves
the macro information behind, so when the other department try to run their
"quicker magic address button" it doesn't work.

Is there a way to have a macro which will work for all workbooks? Or is
there away that I can get the macros to go over at the same time as the
information on the worksheets?

Thanks in advance.

--

Dave Peterson

That is called Book.xlt in the XLStart directory.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Paul" wrote in message
...
Bob

The file where the macros are, is a template and part of the macro creates
a
new workbook then pastes the two worksheets in to it. In an ideal world I
could do with it being available at all times in Excel, as the department
which use it will need this magic button at least 100 times a week.

I'm hoping for a simple solution :) I was hoping to find the template that
Excel starts with and try and put the macro in there, but not found it
yet.

Paul



"Bob Phillips" wrote:

Create a template file with that macro in, and copy the sheets into the
template.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Paul" wrote in message
...
Excel 2000
I've got a workbook with 4 worksheets, I have a macro which is setup
to
copy
two of the worksheets and email them to a different department. I also
have
another macro which combines some of the cells so the address is in
one
line
to make it easier for the other department. The problem I have is when
the
first macro is activated, it copies all of the data, as it should, but
leaves
the macro information behind, so when the other department try to run
their
"quicker magic address button" it doesn't work.

Is there a way to have a macro which will work for all workbooks? Or
is
there away that I can get the macros to go over at the same time as
the
information on the worksheets?

Thanks in advance.

Dave

I've managed to get my floating toolbar and it comes up on my new documents
:) The next problem I seem to have is the first macro which is used copies
two of the worksheets then puts it into a what I thought was a new file then
attaches it to an email, but somewhere in the bit below I need to ensure that
it does create a new file and hopefully that way it will have the floating
toolbar on. When I try the file that I currently have the sheets are copied
but no macros are there :( But on any new document, i.e. FileNew the
floating toolbar is.

Sub Mail_SheetsArray_Outlook()
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Object
Dim OutMail As Object
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
Sheets(Array("Project Record Sheet", "Tapered U Value")).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs (Range("E6") & (" ") & Range("E8") & (".xls"))
'.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"

Thanks for the help so far.

Paul

"Dave Peterson" wrote:

Move the macro to an addin and build a toolbar to execute the code.

Keep only the code in the template that is required in the file that should be
sent.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

Paul wrote:

Excel 2000
I've got a workbook with 4 worksheets, I have a macro which is setup to copy
two of the worksheets and email them to a different department. I also have
another macro which combines some of the cells so the address is in one line
to make it easier for the other department. The problem I have is when the
first macro is activated, it copies all of the data, as it should, but leaves
the macro information behind, so when the other department try to run their
"quicker magic address button" it doesn't work.

Is there a way to have a macro which will work for all workbooks? Or is
there away that I can get the macros to go over at the same time as the
information on the worksheets?

Thanks in advance.

--

Dave Peterson

Right. I was suggesting that you have a single separate addin that contained
the code. Keep that separate from the data.

If you need any of the utilities that are in that toolbar, then distribute that
addin file, too.

This means that instead of having the same/similar code duplicated in lots and
lots of workbooks, you have that code in a single addin. If something changes
(and it will!), you'll only have to update that one addin (and redistribute it).

If you put the code into each workbook, it's gonna be a big problem to get all
the copies working correctly.



Paul wrote:

Dave

I've managed to get my floating toolbar and it comes up on my new documents
:) The next problem I seem to have is the first macro which is used copies
two of the worksheets then puts it into a what I thought was a new file then
attaches it to an email, but somewhere in the bit below I need to ensure that
it does create a new file and hopefully that way it will have the floating
toolbar on. When I try the file that I currently have the sheets are copied
but no macros are there :( But on any new document, i.e. FileNew the
floating toolbar is.

Sub Mail_SheetsArray_Outlook()
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Object
Dim OutMail As Object
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
Sheets(Array("Project Record Sheet", "Tapered U Value")).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs (Range("E6") & (" ") & Range("E8") & (".xls"))
'.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"

Thanks for the help so far.

Paul

"Dave Peterson" wrote:

Move the macro to an addin and build a toolbar to execute the code.

Keep only the code in the template that is required in the file that should be
sent.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

Paul wrote:

Excel 2000
I've got a workbook with 4 worksheets, I have a macro which is setup to copy
two of the worksheets and email them to a different department. I also have
another macro which combines some of the cells so the address is in one line
to make it easier for the other department. The problem I have is when the
first macro is activated, it copies all of the data, as it should, but leaves
the macro information behind, so when the other department try to run their
"quicker magic address button" it doesn't work.

Is there a way to have a macro which will work for all workbooks? Or is
there away that I can get the macros to go over at the same time as the
information on the worksheets?

Thanks in advance.

--

Dave Peterson


--

Dave Peterson

And if you have to have code in those workbooks that you send, then create a
template file and copy the worksheets into a workbook based on that template.

I would still separate my code to mail from the code in that template, though.

Dave Peterson wrote:

Right. I was suggesting that you have a single separate addin that contained
the code. Keep that separate from the data.

If you need any of the utilities that are in that toolbar, then distribute that
addin file, too.

This means that instead of having the same/similar code duplicated in lots and
lots of workbooks, you have that code in a single addin. If something changes
(and it will!), you'll only have to update that one addin (and redistribute it).

If you put the code into each workbook, it's gonna be a big problem to get all
the copies working correctly.

Paul wrote:

Dave

I've managed to get my floating toolbar and it comes up on my new documents
:) The next problem I seem to have is the first macro which is used copies
two of the worksheets then puts it into a what I thought was a new file then
attaches it to an email, but somewhere in the bit below I need to ensure that
it does create a new file and hopefully that way it will have the floating
toolbar on. When I try the file that I currently have the sheets are copied
but no macros are there :( But on any new document, i.e. FileNew the
floating toolbar is.

Sub Mail_SheetsArray_Outlook()
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Object
Dim OutMail As Object
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
Sheets(Array("Project Record Sheet", "Tapered U Value")).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs (Range("E6") & (" ") & Range("E8") & (".xls"))
'.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"

Thanks for the help so far.

Paul

"Dave Peterson" wrote:

Move the macro to an addin and build a toolbar to execute the code.

Keep only the code in the template that is required in the file that should be
sent.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

Paul wrote:

Excel 2000
I've got a workbook with 4 worksheets, I have a macro which is setup to copy
two of the worksheets and email them to a different department. I also have
another macro which combines some of the cells so the address is in one line
to make it easier for the other department. The problem I have is when the
first macro is activated, it copies all of the data, as it should, but leaves
the macro information behind, so when the other department try to run their
"quicker magic address button" it doesn't work.

Is there a way to have a macro which will work for all workbooks? Or is
there away that I can get the macros to go over at the same time as the
information on the worksheets?

Thanks in advance.

--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

I apologize for mixing streams, but I'll try to bring the two thoughts
together.

First issue - separation of code -
Dave, I definitely agree that code separated from data is generally the
better approach. I have found that there are times when the overhead of an
add-in is not warranted by the project. Situations include where end-users
aren't receptive to add-ins (I've seen a lot of pushback from users not
wanting a separate distribution for macros) or when I have reasonable
assurance that the code won't need to be repetitively modified. If you
really wanted to be clever, you would implement self-updating codemodules
that updated themselves given triggers.

Second issue - "idiot's guide" - here's a summary of the steps:
One-time setup
-manually export your code module (from the VBE project explorer, export
your module that contains only the code to distribute)
-store the exported file into a sheet in your "primary" workbook (using
ReadBinaryFileToSheet()). The codemodule is stored into a sheet b/c it is
more reliable for me than trying to export a codemodule programmatically
through VBE.

Each time you produce your exported workbooks
-create the "output" workbook
-put the codemodule into the "output" workbook using CopyCodeModule()

Does this make more sense?



"Paul" wrote:

Bill

It looks good but I have no idea whatsoever what most of that means or what
to do with it.

Is there an idiots guide to what you have written?

:)

Paul

"Bill Pfister" wrote:

You have a couple options. One is to put your code into an add-in and
distribute to all involved parties. Another option, that is probably more
appropriate, is to insert the code into all the files that you distribute.

The following example writes the wkbSrc workbook modSpecialModule code
module out to a file (modSpecialModule.bas) and them imports it into the
wkbDst workbook.

Two important notes:
-WriteBinaryFileFromSheet sub creates a file from a sheet where each cell in
the first column has the the binary contents of a file (one byte per cell -
limited to ~65k bytes, obvisously).
-Need to previously have read the code module into the sheet using
ReadBinaryFileToSheet sub. You could modify the code to do a code module
export instead of creating the file byte-wise form a sheet. I had a specific
need to do it this way, so this is the sample code I had readily available.

Regards,
Bill


Public Sub CopyCodeModule(wkbSrc As Workbook, wkbDst As Workbook,
strCodeModule As String)
Dim vbComp As Object 'VBIDE.VBComponent
Dim vbCM As Object 'VBIDE.CodeModule
Dim wks As Worksheet
Dim rngFindFirst As Range
Dim rngAC As Range
Dim strExtension As String
Dim strFilename As String
Dim strDirSource As String

strExtension = ".bas"
strFilename = wkbSrc.Path & "\" & strCodeModule & strExtension
On Error Resume Next

Call Kill(strFilename)

Set wkbSrc = ThisWorkbook

strDirSource = wkbSrc.Path & "\"
Call WriteBinaryFileFromSheet(strDirSource, strCodeModule,
strCodeModule & strExtension)
DoEvents

Call wkbDst.VBProject.VBComponents.Import(strFilename)
DoEvents

Call Kill(strFilename)

Application.Calculate


End Sub


Private Sub ReadBinaryFileToSheet(strFilename As String, strSheet As String)
Dim lngFileLength As Long
Dim lngFnum As Long
Dim bytArray() As Byte
Dim i As Long
Dim rngData As Range
Dim wkb As Workbook

Set wkb = ThisWorkbook
Set rngData = wkb.Worksheets(strSheet).Range("A1")
rngData.Value = Dir$(strFilename)
DoEvents

Set rngData = rngData.Offset(1, 0)

wkb.Worksheets(strSheet).Range(rngData.Address & ":" &
rngData.SpecialCells(xlCellTypeLastCell).Address). EntireRow.Delete

Set rngData = wkb.Worksheets(strSheet).Range("A1")
Set rngData = rngData.Offset(1, 0)

lngFileLength = FileLen(strFilename)

lngFnum = FreeFile
ReDim bytArray(1 To lngFileLength)

Open strFilename For Binary As lngFnum
Get lngFnum, 1, bytArray
Close lngFnum

For i = 1 To lngFileLength
rngData.Offset(i, 0).Value = Format$(bytArray(i))
Next i

End Sub





Public Sub WriteBinaryFileFromSheet(strPath As String, strSheet As String,
strFilename As String)
Dim lngFileLength As Long
Dim lngFnum As Long
Dim bytArray() As Byte
Dim i As Long
Dim rngData As Range
Dim wkb As Workbook
Dim lngCount As Long
Dim lngValue As Long
Dim lngValueCount As Long

Set wkb = ThisWorkbook
Set rngData = wkb.Worksheets(strSheet).Range("A1")
strFilename = rngData.Value
Set rngData = rngData.Offset(1, 0)

Set rngData = wkb.Worksheets(strSheet).Range("A1")

lngCount = rngData.SpecialCells(xlCellTypeLastCell).Row

For i = 2 To lngCount - 1
'If (Len(rngData.Offset(i, 0).Value) 0) Then
lngValue = rngData.Offset(i, 0).Value

lngValueCount = lngValueCount + 1
ReDim Preserve bytArray(1 To lngValueCount)
bytArray(lngValueCount) = lngValue
'End If
Next i

' Delete any existing file

On Error Resume Next
Kill strPath & strFilename
On Error GoTo 0

' Save the file.
lngFnum = FreeFile
Open strPath & strFilename For Binary As lngFnum
Put lngFnum, 1, bytArray
Close lngFnum

End Sub



"Paul" wrote:

Excel 2000
I've got a workbook with 4 worksheets, I have a macro which is setup to copy
two of the worksheets and email them to a different department. I also have
another macro which combines some of the cells so the address is in one line
to make it easier for the other department. The problem I have is when the
first macro is activated, it copies all of the data, as it should, but leaves
the macro information behind, so when the other department try to run their
"quicker magic address button" it doesn't work.

Is there a way to have a macro which will work for all workbooks? Or is
there away that I can get the macros to go over at the same time as the
information on the worksheets?

Thanks in advance.