I am trying to write a macro that will use one file to update another. I got
some test code to work the way I wanted, but I need to user to select the
target file, and that is causing trouble.

Here is what I have so far:
Option Explicit
Sub auto_open()
Dim FileName As Variant
Application.ScreenUpdating = False
FileName =Application.GetOpenFilename(MultiSelect:=False)
Workbooks.Open FileName
Workbooks(FileName).Worksheets("Sheet1").Unprotect Password:="password"
ThisWorkbook.Worksheets("Sheet1").Activate
ActiveSheet.Cells.Copy
Workbooks(FileName).Worksheets("Sheet1").Range("A1 ").PasteSpecial
Workbooks(FileName).Worksheets("Sheet1").Activate
ActiveSheet.Range("A1").Select
Workbooks(FileName).Worksheets("Sheet1").Protect Password:="password"
Application.CutCopyMode = False
Application.ScreenUpdating = True
ThisWorkbook.Close savechanges:=False
End Sub

Filename will include the path for the workbook - which is OK for opening the
file. After that, Excel dispenses with the path. Use something like this

Sub auto_open()
Dim FileName As Variant
Application.ScreenUpdating = False

FileName =Application.GetOpenFilename(MultiSelect:=False)
if filename = false then exit sub
dim wb as workbook
set wb = Workbooks.Open (FileName)
wb.Worksheets("Sheet1").Unprotect Password:="password"

ThisWorkbook.Worksheets("Sheet1").Activate
ActiveSheet.Cells.Copy
wb.Worksheets("Sheet1").Range("A1").PasteSpecial
wb.Worksheets("Sheet1").Activate
ActiveSheet.Range("A1").Select
wb.Worksheets("Sheet1").Protect Password:="password"
Application.CutCopyMode = False
Application.ScreenUpdating = True
ThisWorkbook.Close savechanges:=False
End Sub



"Horatio J. Bilge, Jr." wrote:

I am trying to write a macro that will use one file to update another. I got
some test code to work the way I wanted, but I need to user to select the
target file, and that is causing trouble.

Here is what I have so far:
Option Explicit
Sub auto_open()
Dim FileName As Variant
Application.ScreenUpdating = False
FileName =Application.GetOpenFilename(MultiSelect:=False)
Workbooks.Open FileName
Workbooks(FileName).Worksheets("Sheet1").Unprotect Password:="password"
ThisWorkbook.Worksheets("Sheet1").Activate
ActiveSheet.Cells.Copy
Workbooks(FileName).Worksheets("Sheet1").Range("A1 ").PasteSpecial
Workbooks(FileName).Worksheets("Sheet1").Activate
ActiveSheet.Range("A1").Select
Workbooks(FileName).Worksheets("Sheet1").Protect Password:="password"
Application.CutCopyMode = False
Application.ScreenUpdating = True
ThisWorkbook.Close savechanges:=False
End Sub

Nice simple fix. Thanks for the help.


"Duke Carey" wrote:

Filename will include the path for the workbook - which is OK for opening the
file. After that, Excel dispenses with the path. Use something like this

Sub auto_open()
Dim FileName As Variant
Application.ScreenUpdating = False

FileName =Application.GetOpenFilename(MultiSelect:=False)
if filename = false then exit sub
dim wb as workbook
set wb = Workbooks.Open (FileName)
wb.Worksheets("Sheet1").Unprotect Password:="password"

ThisWorkbook.Worksheets("Sheet1").Activate
ActiveSheet.Cells.Copy
wb.Worksheets("Sheet1").Range("A1").PasteSpecial
wb.Worksheets("Sheet1").Activate
ActiveSheet.Range("A1").Select
wb.Worksheets("Sheet1").Protect Password:="password"
Application.CutCopyMode = False
Application.ScreenUpdating = True
ThisWorkbook.Close savechanges:=False
End Sub



"Horatio J. Bilge, Jr." wrote:

I am trying to write a macro that will use one file to update another. I got
some test code to work the way I wanted, but I need to user to select the
target file, and that is causing trouble.

Here is what I have so far:
Option Explicit
Sub auto_open()
Dim FileName As Variant
Application.ScreenUpdating = False
FileName =Application.GetOpenFilename(MultiSelect:=False)
Workbooks.Open FileName
Workbooks(FileName).Worksheets("Sheet1").Unprotect Password:="password"
ThisWorkbook.Worksheets("Sheet1").Activate
ActiveSheet.Cells.Copy
Workbooks(FileName).Worksheets("Sheet1").Range("A1 ").PasteSpecial
Workbooks(FileName).Worksheets("Sheet1").Activate
ActiveSheet.Range("A1").Select
Workbooks(FileName).Worksheets("Sheet1").Protect Password:="password"
Application.CutCopyMode = False
Application.ScreenUpdating = True
ThisWorkbook.Close savechanges:=False
End Sub