Exporting to several xls files

Hi ng,

I have a problem, i have 15 different xls files and one master, i want to
create a macro that when you push
it, it exports range A1:A5 to the master xls (example file1.xls exports
range A1:A5 to master.xls range A1:A5, file2.xls
exports range A1:A5 to master.xls range B1:B5 and so on), also the macro
should save the file at the same time.

It could be when you leave the file, but the user should not have a
possibility to say no.

I hope you understand my problem and can help me,

regards,
Ole

You can use the Beforeclose event.

Chip Pearson has general information on Events:

http://www:cpearson.com/excel/vbe.htm

in the event you would have something like

Dim bClose as Boolean
Dim bk as workbooks
On Error Resume Next
set bk = workbooks("Master.xls")
On Error goto 0
if bk is nothing then
bClose = True
set bk = Workbooks.Open("C:\Myfolder\Master.xls")
end if
bk.worksheets(1).Range("C1:C5").Value =
worksheets(1).Range("A1:A5").Value
bk.Save
if bClose then
bk.Close Savechanges:=False
End if
Application.EnableEvents = False
thisworkbook.Save
Application.EnableEvents = True

--
Regards,
Tom Ogilvy


"ole_" <[email protected]> wrote in message
news:%[email protected]...
> Hi ng,
>
> I have a problem, i have 15 different xls files and one master, i want to
> create a macro that when you push
> it, it exports range A1:A5 to the master xls (example file1.xls exports
> range A1:A5 to master.xls range A1:A5, file2.xls
> exports range A1:A5 to master.xls range B1:B5 and so on), also the macro
> should save the file at the same time.
>
> It could be when you leave the file, but the user should not have a
> possibility to say no.
>
> I hope you understand my problem and can help me,
>
> regards,
> Ole
>
>

"Tom Ogilvy" <[email protected]> skrev i en meddelelse
news:#[email protected]...
> You can use the Beforeclose event.
>
> Chip Pearson has general information on Events:
>
> http://www:cpearson.com/excel/vbe.htm
>
> in the event you would have something like
>
> Dim bClose as Boolean
> Dim bk as workbooks
> On Error Resume Next
> set bk = workbooks("Master.xls")
> On Error goto 0
> if bk is nothing then
> bClose = True
> set bk = Workbooks.Open("C:\Myfolder\Master.xls")
> end if
> bk.worksheets(1).Range("C1:C5").Value =
> worksheets(1).Range("A1:A5").Value
> bk.Save
> if bClose then
> bk.Close Savechanges:=False
> End if
> Application.EnableEvents = False
> thisworkbook.Save
> Application.EnableEvents = True
>

Hi Tom,

I have tried to put it in "this workbook", but it failed at:

bk.worksheets(1).Range("C1:C5").Value =

Is it me there is doing anything wrong?

Regards,
Ole

Ole,

The problem is likely cause by line breaks in Tom's otherwise
correct reply. The code

bk.worksheets(1).Range("C1:C5").Value =
worksheets(1).Range("A1:A5").Value

should be on a single line of code in the editor. Or, you can use
the line continuation character to split it in to two lines:

bk.worksheets(1).Range("C1:C5").Value = _
worksheets(1).Range("A1:A5").Value


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"ole_" <[email protected]> wrote in message
news:%[email protected]...
>
> "Tom Ogilvy" <[email protected]> skrev i en meddelelse
> news:#[email protected]...
>> You can use the Beforeclose event.
>>
>> Chip Pearson has general information on Events:
>>
>> http://www:cpearson.com/excel/vbe.htm
>>
>> in the event you would have something like
>>
>> Dim bClose as Boolean
>> Dim bk as workbooks
>> On Error Resume Next
>> set bk = workbooks("Master.xls")
>> On Error goto 0
>> if bk is nothing then
>> bClose = True
>> set bk = Workbooks.Open("C:\Myfolder\Master.xls")
>> end if
>> bk.worksheets(1).Range("C1:C5").Value =
>> worksheets(1).Range("A1:A5").Value
>> bk.Save
>> if bClose then
>> bk.Close Savechanges:=False
>> End if
>> Application.EnableEvents = False
>> thisworkbook.Save
>> Application.EnableEvents = True
>>
>
> Hi Tom,
>
> I have tried to put it in "this workbook", but it failed at:
>
> bk.worksheets(1).Range("C1:C5").Value =
>
> Is it me there is doing anything wrong?
>
> Regards,
> Ole
>
>

"Chip Pearson" <[email protected]> skrev i en meddelelse
news:u8xKG#[email protected]...
> Ole,
>
> The problem is likely cause by line breaks in Tom's otherwise
> correct reply. The code
>
> bk.worksheets(1).Range("C1:C5").Value =
> worksheets(1).Range("A1:A5").Value
>
> should be on a single line of code in the editor. Or, you can use
> the line continuation character to split it in to two lines:
>
> bk.worksheets(1).Range("C1:C5").Value = _
> worksheets(1).Range("A1:A5").Value
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>


Hi Chip,

Now i get another error:

"Compile error: ethod or data member not found" and then ".worksheets" in
bk.worksheets(1).Range("C1:C5").Value = _
is highlighted, here is what i have so far:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim bClose As Boolean
Dim bk As Workbooks
On Error Resume Next
Set bk = Workbooks("Master.xls")
On Error GoTo 0
If bk Is Nothing Then
bClose = True
Set bk = Workbooks.Open("C:\Master.xls")
End If
bk.Worksheets(1).Range("C1:C5").Value = _
Worksheets(1).Range("A1:A5").Value
bk.Save
If bClose Then
bk.Close Savechanges:=False
End If
Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True

End Sub


And something else i have been wondering, shouldent the master.xls open??

Regards,
Ole

Ole,

bk should be declared as Workbook (singular) not Workbooks
(plural).

Dim bk As Workbook

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"ole_" <[email protected]> wrote in message
news:[email protected]...
>
> "Chip Pearson" <[email protected]> skrev i en meddelelse
> news:u8xKG#[email protected]...
>> Ole,
>>
>> The problem is likely cause by line breaks in Tom's otherwise
>> correct reply. The code
>>
>> bk.worksheets(1).Range("C1:C5").Value =
>> worksheets(1).Range("A1:A5").Value
>>
>> should be on a single line of code in the editor. Or, you can
>> use
>> the line continuation character to split it in to two lines:
>>
>> bk.worksheets(1).Range("C1:C5").Value = _
>> worksheets(1).Range("A1:A5").Value
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>
>
> Hi Chip,
>
> Now i get another error:
>
> "Compile error: ethod or data member not found" and then
> ".worksheets" in
> bk.worksheets(1).Range("C1:C5").Value = _
> is highlighted, here is what i have so far:
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
>
> Dim bClose As Boolean
> Dim bk As Workbooks
> On Error Resume Next
> Set bk = Workbooks("Master.xls")
> On Error GoTo 0
> If bk Is Nothing Then
> bClose = True
> Set bk = Workbooks.Open("C:\Master.xls")
> End If
> bk.Worksheets(1).Range("C1:C5").Value = _
> Worksheets(1).Range("A1:A5").Value
> bk.Save
> If bClose Then
> bk.Close Savechanges:=False
> End If
> Application.EnableEvents = False
> ThisWorkbook.Save
> Application.EnableEvents = True
>
> End Sub
>
>
> And something else i have been wondering, shouldent the
> master.xls open??
>
> Regards,
> Ole
>
>

Chip and Tom

Thanks a lot, its working just like i hoped.

Many thanks,
Ole

"Chip Pearson" <[email protected]> skrev i en meddelelse
news:#[email protected]...
> Ole,
>
> bk should be declared as Workbook (singular) not Workbooks
> (plural).
>
> Dim bk As Workbook
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "ole_" <[email protected]> wrote in message
> news:[email protected]...
> >
> > "Chip Pearson" <[email protected]> skrev i en meddelelse
> > news:u8xKG#[email protected]...
> >> Ole,
> >>
> >> The problem is likely cause by line breaks in Tom's otherwise
> >> correct reply. The code
> >>
> >> bk.worksheets(1).Range("C1:C5").Value =
> >> worksheets(1).Range("A1:A5").Value
> >>
> >> should be on a single line of code in the editor. Or, you can
> >> use
> >> the line continuation character to split it in to two lines:
> >>
> >> bk.worksheets(1).Range("C1:C5").Value = _
> >> worksheets(1).Range("A1:A5").Value
> >>
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >>
> >
> >
> > Hi Chip,
> >
> > Now i get another error:
> >
> > "Compile error: ethod or data member not found" and then
> > ".worksheets" in
> > bk.worksheets(1).Range("C1:C5").Value = _
> > is highlighted, here is what i have so far:
> >
> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> >
> > Dim bClose As Boolean
> > Dim bk As Workbooks
> > On Error Resume Next
> > Set bk = Workbooks("Master.xls")
> > On Error GoTo 0
> > If bk Is Nothing Then
> > bClose = True
> > Set bk = Workbooks.Open("C:\Master.xls")
> > End If
> > bk.Worksheets(1).Range("C1:C5").Value = _
> > Worksheets(1).Range("A1:A5").Value
> > bk.Save
> > If bClose Then
> > bk.Close Savechanges:=False
> > End If
> > Application.EnableEvents = False
> > ThisWorkbook.Save
> > Application.EnableEvents = True
> >
> > End Sub
> >
> >
> > And something else i have been wondering, shouldent the
> > master.xls open??
> >
> > Regards,
> > Ole
> >
> >
>
>