Update Macro

[fabio.geraci]

Morning Everyone,

I amde the following macro, as you can see the only thing it does is to open and close a few files, in order to update (populate) a worksheet. But as soon as the macro closes the file all the data get deleted (#REF).

How can I change this macro in order to avoid this secondary effect? :doh:

Thanks


Sub update_botton()
'
' update_botton Macro
' Macro recorded 16/08/2010 by fgeraci
'
Dim Filter As String
Dim Title As Variant
Dim MyPath As String
Dim DestWb As Workbook
Dim DestWs As Worksheet
Dim Tempbook As Workbook
Dim sText As Variant
Dim i As Integer
Dim icount As Integer
'
Set DestWs = ActiveSheet
icount = Range("k2")
' Cells(11, 2)
' Set DestWb = Workbooks(InputBox("INSERT LoadCase Name:", "FileName", "RF_CUTOUT_.xls"))
MyPath = Range("b1")

For i = 1 To icount
Title = Cells(i + 1, 8)
sText = MyPath + "\" + Title
Workbooks.Open Filename:=sText
ActiveWindow.Close
Next i
End Sub

[Bob Phillips]

I can't see where the data is being copied.

[fabio.geraci]

I modified to for some other reasons, BSC is the worksheet where the data should be stored.

Sub update_botton()
'
' update_botton Macro
' Macro recorded 16/08/2010 by fgeraci
'
Dim Title As Variant
Dim MyPath As String
Dim DestWb As Workbook
Dim DestWs As Worksheet
Dim sText As Variant
Dim i As Integer
Dim icount As Integer
'
Set DestWs = ActiveSheet
icount = Cells(2, 11)
MyPath = Cells(1, 2)

For i = 1 To icount
If Cells(i + 1, 8) = "CUTOUT" Then
Sheets("BSC").Select
Cells(i + 9, 4) = "CUTOUT"
Else
Sheets("file_setup").Select
Title = Cells(i + 1, 8)
sText = MyPath + "\" + Title
Workbooks.Open Filename:=sText
ActiveWindow.Close
End If
Next i
End Sub

[fabio.geraci]

I attached the workbook where the data get copied to, also the following code manages (I guess very un-efficiently) to open and close all the reference workbooks.

My question now is:

When I close the RESUME_1.xls and I reopen it it asks me to UPDATE or DO NOT UPDATE, whichever I choose it changes all the values to #REF.

How can I stop that to happen?

Cheers

Sub update_botton()
'
' update_botton Macro
' Macro recorded 16/08/2010 by fgeraci
'
Dim Title As Variant
Dim MyPath As String
Dim DestWb As String
Dim DestWs As Worksheet
Dim sText As Variant
Dim i As Integer
Dim icount As Integer
'
icount = Cells(2, 11)
MyPath = Cells(1, 2)
DestWb = Cells(1, 8)

For i = 1 To icount
If Cells(i + 1, 8) = "CUTOUT" Then
Sheets("BSC").Select
Cells(i + 9, 4) = "CUTOUT"
Sheets("OCF").Select
Cells(i + 9, 4) = "CUTOUT"
Sheets("Bolting").Select
Cells(i + 9, 4) = "CUTOUT"
Sheets("Nett").Select
Cells(i + 20, 4) = "CUTOUT"
Else
Sheets("file_setup").Select
Title = Cells(i + 1, 8)
sText = MyPath + "\" + Title
Workbooks.Open Filename:=sText
Windows(DestWb).Activate
End If
Next i
i = 0
For i = 1 To icount
If Cells(i + 1, 8) = "CUTOUT" Then
'do nothing
Else
Title = Cells(i + 1, 8)
Application.Windows(Title).Close
End If
Next i
End Sub


4330