You helped me yesterday extracting information from a consolidated file to retail files. It works great but the numbers I am extracting from the consolidated file are formulas. It causes errors in the retail file. If I change the values in the consolidated file to values (by cutting and pasting on top and using paste special - that's the only way I know how) and then run the macro it works perfect. I am just afraid the file might be saved that way and I will lose all formulas. Do you have any suggetions on how I can have the macro pick up the values and not the formulas?
The active file is the consolidated file and it is pulling into the retail file.
Your help is really appreciated!
This is the code you wrote:
Sub AAB()
Dim sh As Worksheet
Dim wkbk As Workbook
Sheets("Graph Data").Select
Set sh = ActiveSheet
Set wkbk = Workbooks.Open(FileName:= _
"C:\Retail\blmdir.xls")
sh.Range("b4:G128").Copy _
Destination:=wkbk.Worksheets( _
"Graph Data").Range("B134:G258")
wkbk.Close SaveChanges:=True
End Sub

Marie,

I believe you can replace the lines:

sh.Range("b4:G128").Copy _
Destination:=wkbk.Worksheets( _
"Graph Data").Range("B134:G258")

with:

Worksheets("Graph Data").Range("B134:G258") = _
sh.Range("B4:G128").value

to accomplish what you want. I learned this from one of Tom's posts a while
back, unless of course I'm wrong, then I'd take the credit ;-).

hth,

Doug Glancy


"Marie" wrote in message
...
You helped me yesterday extracting information from a consolidated file to
retail files. It works great but the numbers I am extracting from the
consolidated file are formulas. It causes errors in the retail file. If I
change the values in the consolidated file to values (by cutting and pasting
on top and using paste special - that's the only way I know how) and then
run the macro it works perfect. I am just afraid the file might be saved
that way and I will lose all formulas. Do you have any suggetions on how I
can have the macro pick up the values and not the formulas?
The active file is the consolidated file and it is pulling into the retail
file.
Your help is really appreciated!
This is the code you wrote:
Sub AAB()
Dim sh As Worksheet
Dim wkbk As Workbook
Sheets("Graph Data").Select
Set sh = ActiveSheet
Set wkbk = Workbooks.Open(FileName:= _
"C:\Retail\blmdir.xls")
sh.Range("b4:G128").Copy _
Destination:=wkbk.Worksheets( _
"Graph Data").Range("B134:G258")
wkbk.Close SaveChanges:=True
End Sub

You can do that - that is similar to PasteSpecial xlValues

--
Regards,
Tom Ogilvy

"Doug Glancy" wrote in message
...
Marie,

I believe you can replace the lines:

sh.Range("b4:G128").Copy _
Destination:=wkbk.Worksheets( _
"Graph Data").Range("B134:G258")

with:

Worksheets("Graph Data").Range("B134:G258") = _
sh.Range("B4:G128").value

to accomplish what you want. I learned this from one of Tom's posts a
while
back, unless of course I'm wrong, then I'd take the credit ;-).

hth,

Doug Glancy


"Marie" wrote in message
...
You helped me yesterday extracting information from a consolidated file
to
retail files. It works great but the numbers I am extracting from the
consolidated file are formulas. It causes errors in the retail file. If I
change the values in the consolidated file to values (by cutting and
pasting
on top and using paste special - that's the only way I know how) and then
run the macro it works perfect. I am just afraid the file might be saved
that way and I will lose all formulas. Do you have any suggetions on how
I
can have the macro pick up the values and not the formulas?
The active file is the consolidated file and it is pulling into the
retail
file.
Your help is really appreciated!
This is the code you wrote:
Sub AAB()
Dim sh As Worksheet
Dim wkbk As Workbook
Sheets("Graph Data").Select
Set sh = ActiveSheet
Set wkbk = Workbooks.Open(FileName:= _
"C:\Retail\blmdir.xls")
sh.Range("b4:G128").Copy _
Destination:=wkbk.Worksheets( _
"Graph Data").Range("B134:G258")
wkbk.Close SaveChanges:=True
End Sub

Thanks!

Doug

"Marie" wrote in message
...
You guys are the greatest! It worked great! Thank you so much!
Will I ever be this knowledgeable in VB??

"Marie" wrote:

You helped me yesterday extracting information from a consolidated file
to retail files. It works great but the numbers I am extracting from the
consolidated file are formulas. It causes errors in the retail file. If I
change the values in the consolidated file to values (by cutting and pasting
on top and using paste special - that's the only way I know how) and then
run the macro it works perfect. I am just afraid the file might be saved
that way and I will lose all formulas. Do you have any suggetions on how I
can have the macro pick up the values and not the formulas?
The active file is the consolidated file and it is pulling into the
retail file.
Your help is really appreciated!
This is the code you wrote:
Sub AAB()
Dim sh As Worksheet
Dim wkbk As Workbook
Sheets("Graph Data").Select
Set sh = ActiveSheet
Set wkbk = Workbooks.Open(FileName:= _
"C:\Retail\blmdir.xls")
sh.Range("b4:G128").Copy _
Destination:=wkbk.Worksheets( _
"Graph Data").Range("B134:G258")
wkbk.Close SaveChanges:=True
End Sub