S
Steven Cheng
I have the following code which copies the a certain range
from several files (that only differs slightly by period
as handled by the variable r which runs through 1 to 7)
and pastes it into a workbook.
However, I only want to paste by value and format but it
is still holding the formulas from the other files
creating external links. Can someone please help to
explain why.
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 07/30/2003 by Steven Cheng
'
Dim w As Workbook, cwkb As Workbook
Dim fname As String
Dim r As Integer, t As Integer
Set cwkb = ActiveWorkbook
For r = 1 To 7
fname
= "H:\ACCOUNT\AUDIT\SPREADS\DAILY\PAST_DLY\2003 Period " &
Format(r, "00") & ".xls"
Set w = Workbooks.Open(Filename:=fname)
w.Worksheets("Rooms").Range("B28:AD46").Copy
cwkb.Worksheets(1).Range("a1").Offset(19 * (r -
1), 0).PasteSpecial Paste:=xlValue
cwkb.Worksheets(1).Range("a1").Offset(19 * (r -
1), 0).PasteSpecial Paste:=xlFormats
w.Close savechanges:=False
End Sub
from several files (that only differs slightly by period
as handled by the variable r which runs through 1 to 7)
and pastes it into a workbook.
However, I only want to paste by value and format but it
is still holding the formulas from the other files
creating external links. Can someone please help to
explain why.
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 07/30/2003 by Steven Cheng
'
Dim w As Workbook, cwkb As Workbook
Dim fname As String
Dim r As Integer, t As Integer
Set cwkb = ActiveWorkbook
For r = 1 To 7
fname
= "H:\ACCOUNT\AUDIT\SPREADS\DAILY\PAST_DLY\2003 Period " &
Format(r, "00") & ".xls"
Set w = Workbooks.Open(Filename:=fname)
w.Worksheets("Rooms").Range("B28:AD46").Copy
cwkb.Worksheets(1).Range("a1").Offset(19 * (r -
1), 0).PasteSpecial Paste:=xlValue
cwkb.Worksheets(1).Range("a1").Offset(19 * (r -
1), 0).PasteSpecial Paste:=xlFormats
w.Close savechanges:=False
End Sub