Exporting to several xls files

[ole_]

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]

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

 

[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

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]

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_]

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 Pearson]

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_]

Chip and Tom

Thanks a lot, its working just like i hoped.

Many thanks,
Ole