Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and Paste formulas | Excel Discussion (Misc queries) | |||
Copy and paste a formulas | Excel Worksheet Functions | |||
copy/paste vaules, not formulas | New Users to Excel | |||
Copy/Paste formulas | Excel Discussion (Misc queries) | |||
copy and paste formulas | Excel Worksheet Functions |