Copy & Paste without the formulas
 

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

Copy & Paste without the formulas
 

I think it's a typo:

xlvalue should be xlvalues or xlpastevalues

From the immediate window:
?xlvalues
-4163
?xlpastevalues
-4163
?xlvalue
2


Yeah, it took awhile to notice it. I actually changed it to xlpastevalue and
that's when it blew up.

Steven Cheng wrote:

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

--

Dave Peterson

Copy & Paste without the formulas
 

Thanks Dave..how stupid of me.
-----Original Message-----
I think it's a typo:

xlvalue should be xlvalues or xlpastevalues

From the immediate window:
?xlvalues
-4163
?xlpastevalues
-4163
?xlvalue
2


Yeah, it took awhile to notice it. I actually changed it
to xlpastevalue and
that's when it blew up.

Steven Cheng wrote:

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

--

Dave Peterson

.