Macro worts in workbook but not from Personal.xls
 

Hi there

With help from community I have a great macro. Trouble is that this section
will run in its own workbook but not when its in Personal.xls

Sub Copy_to_new_Workbook()
Dim SourceRange As Range
Dim DestRange As Range
Dim DestWB As Workbook
Dim DestSh As Worksheet
Dim Lr As Long

Macro then runs fine until after the option to save from here on

Application.Dialogs(xlDialogSaveAs).Show

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Change the file name (2*) and the path/file name to your file
If bIsBookOpen_RB("Master Copy.xls") Then
Set DestWB = Workbooks("Master Copy.xls")
Else
Set DestWB = Workbooks.Open("c:\block management\Master Copy.xls")
End If

'Change the Source Sheet and range
Set SourceRange = ThisWorkbook.Sheets("addresses").Range("b1:t500")
'Change the sheet name of the database workbook
Set DestSh = DestWB.Worksheets("addresses")

Set DestRange = DestSh.Range("b1:t500")

On Error Resume Next
For Each wks In Worksheets
wks.Unprotect Password:="nev"
Next wks

Then it works OK. It just will not copy the data from Source to Destination
sheets

Can anyone out there help? I have 80 workbooks to run from Personal!

Thanks in advance

Nev

Macro worts in workbook but not from Personal.xls
 

Most likely cuplrit is the use of "Thisworkbook" which now refers to your
personal.xls and which probably doesn't have a sheet named "Addresses"!
Change thisworkbook to Activeworkbook and all should be OK
Bob Umlas
Excel MVP

"Nev" wrote in message
...
Hi there

With help from community I have a great macro. Trouble is that this
section
will run in its own workbook but not when its in Personal.xls

Sub Copy_to_new_Workbook()
Dim SourceRange As Range
Dim DestRange As Range
Dim DestWB As Workbook
Dim DestSh As Worksheet
Dim Lr As Long

Macro then runs fine until after the option to save from here on

Application.Dialogs(xlDialogSaveAs).Show

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Change the file name (2*) and the path/file name to your file
If bIsBookOpen_RB("Master Copy.xls") Then
Set DestWB = Workbooks("Master Copy.xls")
Else
Set DestWB = Workbooks.Open("c:\block management\Master Copy.xls")
End If

'Change the Source Sheet and range
Set SourceRange = ThisWorkbook.Sheets("addresses").Range("b1:t500")
'Change the sheet name of the database workbook
Set DestSh = DestWB.Worksheets("addresses")

Set DestRange = DestSh.Range("b1:t500")

On Error Resume Next
For Each wks In Worksheets
wks.Unprotect Password:="nev"
Next wks

Then it works OK. It just will not copy the data from Source to
Destination
sheets

Can anyone out there help? I have 80 workbooks to run from Personal!

Thanks in advance

Nev

Macro worts in workbook but not from Personal.xls
 

And to add to Bob's advice...

This line (after Bob's suggested change)
'Change the Source Sheet and range
Set SourceRange = Activeworkbook.Sheets("addresses").Range("b1:t500" )

Will refer to the activeworkbook. And if you just opened that "Master Copy.xls"
file, there's a good chance that "Master Copy.xls" is the activeworkbook.

I'd move that line before the bisbookopen() line:

....

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Change the Source Sheet and range
Set SourceRange = Activeworkbook.Sheets("addresses").Range("b1:t500" )

'Change the file name (2*) and the path/file name to your file
If bIsBookOpen_RB("Master Copy.xls") Then
Set DestWB = Workbooks("Master Copy.xls")
Else
Set DestWB = Workbooks.Open("c:\block management\Master Copy.xls")
End If

'Change the sheet name of the database workbook
Set DestSh = DestWB.Worksheets("addresses")

Set DestRange = DestSh.Range("b1:t500")

.....


Nev wrote:

Hi there

With help from community I have a great macro. Trouble is that this section
will run in its own workbook but not when its in Personal.xls

Sub Copy_to_new_Workbook()
Dim SourceRange As Range
Dim DestRange As Range
Dim DestWB As Workbook
Dim DestSh As Worksheet
Dim Lr As Long

Macro then runs fine until after the option to save from here on

Application.Dialogs(xlDialogSaveAs).Show

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Change the file name (2*) and the path/file name to your file
If bIsBookOpen_RB("Master Copy.xls") Then
Set DestWB = Workbooks("Master Copy.xls")
Else
Set DestWB = Workbooks.Open("c:\block management\Master Copy.xls")
End If

'Change the Source Sheet and range
Set SourceRange = ThisWorkbook.Sheets("addresses").Range("b1:t500")
'Change the sheet name of the database workbook
Set DestSh = DestWB.Worksheets("addresses")

Set DestRange = DestSh.Range("b1:t500")

On Error Resume Next
For Each wks In Worksheets
wks.Unprotect Password:="nev"
Next wks

Then it works OK. It just will not copy the data from Source to Destination
sheets

Can anyone out there help? I have 80 workbooks to run from Personal!

Thanks in advance

Nev

--

Dave Peterson