Error When Running a 'Before Save' Event Using 'ThisWorkbook.save" Command Button

[uga924]

Hello all,

I am using Excel 2010. I am attempting to run a macro as a 'Before Save' event in a workbook named "FirstWorkbook.xlsm." Among other things, the macro opens up a separate workbook ("SecondWorkbook.xlsm"), copies a range of data from the first workbook, pastes the data into the second workbook, and then closes the second workbook. Here is the segment of code in question:

x = Dir("C:\DocumentLog\SecondWorkbook.xlsm") Set Srcfile = ActiveWorkbook Workbooks.Open "C:\DocumentLog\" & x Set DstFile = ActiveWorkbook Set Dstsht = DstFile.Worksheets("SubLog") With Srcfile.Sheets("Register") .Range("A1:G300").Copy Dstsht.Range("A1:G300") End With DstFile.Save DstFile.Close

<tbody>
</tbody>

The macro works fine when I run it as a 'Before Save' event by clicking the standard Excel save button. It also works fine when I run it directly from a command button (i.e. a button that is assigned to a macro containing the above-cited code). However, when I try to run it from a command button assigned to a macro that only contains the code "ThisWorkbook.Save"), it gives me a Run Time Error 9 (Subscript Out of Range). When I go to Debug, the following line of code is highlighted:

Set Dstsht = DstFile.Worksheets("SubLog")

<tbody>
</tbody>

I have searched this website and others to find an answer to this problem, with no luck. I cannot understand why it will work when I run it from the Excel save button but not when I run it from a 'ThisWorkbook.save' command. I'm not expert on VBA and am trying to piece things together a simply as possible, so any help would be appreciated. Please let me know if I need to provide more information to assist you in understanding the problem.

Thanks!
Jake

 

[venkat1926]

"before save" code is an EVENT CODE
the codes will be fired only in the case of an event (file save) other wise it will not work as a standard macro . is this clarification you want?
If you want..........
you can enter the codes as a standard macro(with a macro name) in a module
and
open vb editor if necessary control R
goto "thisworkbook"
right click
click "view code" at the top
copy this event code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
<name of="" the="" macro="">
End Sub

when you save the file the macro will be automatically run

when you are not saving you can run the code in the standard macro</name>