inserting a worksheet using visual basic
 

Inserting a worksheet from another workbook the links remain.

I would like to inset a worksheet and bring accross the values rather than
the formulas.
This is the macro:
Sub Balance_Sheet_Data()

Application.ScreenUpdating = False
Workbooks.Open Filename:="G:\Balance Sheet 2004.xls"
Cells.Select
Selection.Copy
Windows("DATA SOURCE - GOLF # 3-01.xls").Activate
Sheets("Balance Sheet Recon Data").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Windows("Balance Sheet 2004.xls").Activate
ActiveWorkbook.Close
Sheets("Command Page").Select
Range("A1").Select
Application.ScreenUpdating = True
End Subs

What visual basic command do I change?

inserting a worksheet using visual basic
 

Hi F.C

You can use this to make values of all formulas after you paste it

With ActiveSheet.UsedRange
.Value = .Value
End With



--
Regards Ron de Bruin
http://www.rondebruin.nl


"F.C" wrote in message ...
Inserting a worksheet from another workbook the links remain.

I would like to inset a worksheet and bring accross the values rather than
the formulas.
This is the macro:
Sub Balance_Sheet_Data()

Application.ScreenUpdating = False
Workbooks.Open Filename:="G:\Balance Sheet 2004.xls"
Cells.Select
Selection.Copy
Windows("DATA SOURCE - GOLF # 3-01.xls").Activate
Sheets("Balance Sheet Recon Data").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Windows("Balance Sheet 2004.xls").Activate
ActiveWorkbook.Close
Sheets("Command Page").Select
Range("A1").Select
Application.ScreenUpdating = True
End Subs

What visual basic command do I change?

inserting a worksheet using visual basic
 

F.C Wrote:
Inserting a worksheet from another workbook the links remain.

I would like to inset a worksheet and bring accross the values rathe
than
the formulas.
This is the macro:
Sub Balance_Sheet_Data()

Application.ScreenUpdating = False
Workbooks.Open Filename:="G:\Balance Sheet 2004.xls"
Cells.Select
Selection.Copy
Windows("DATA SOURCE - GOLF # 3-01.xls").Activate
Sheets("Balance Sheet Recon Data").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Windows("Balance Sheet 2004.xls").Activate
ActiveWorkbook.Close
Sheets("Command Page").Select
Range("A1").Select
Application.ScreenUpdating = True
End Subs

What visual basic command do I change?

Hi F.C

Try changing Paste to

PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=Fals

--
Paul Sheppar

-----------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...fo&userid=2478
View this thread: http://www.excelforum.com/showthread.php?threadid=39980

inserting a worksheet using visual basic
 

Thank You Ron
Greatly appreciated

Ended up using the following
ActiveSheet.Paste
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value


"Ron de Bruin" wrote:

Hi F.C

You can use this to make values of all formulas after you paste it

With ActiveSheet.UsedRange
.Value = .Value
End With



--
Regards Ron de Bruin
http://www.rondebruin.nl


"F.C" wrote in message ...
Inserting a worksheet from another workbook the links remain.

I would like to inset a worksheet and bring accross the values rather than
the formulas.
This is the macro:
Sub Balance_Sheet_Data()

Application.ScreenUpdating = False
Workbooks.Open Filename:="G:\Balance Sheet 2004.xls"
Cells.Select
Selection.Copy
Windows("DATA SOURCE - GOLF # 3-01.xls").Activate
Sheets("Balance Sheet Recon Data").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Windows("Balance Sheet 2004.xls").Activate
ActiveWorkbook.Close
Sheets("Command Page").Select
Range("A1").Select
Application.ScreenUpdating = True
End Subs

What visual basic command do I change?

inserting a worksheet using visual basic
 

Thank you Paul

I had a number of replies ended up using the following:


ActiveSheet.Paste
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

Thank You
Frank

"Paul Sheppard" wrote:


F.C Wrote:
Inserting a worksheet from another workbook the links remain.

I would like to inset a worksheet and bring accross the values rather
than
the formulas.
This is the macro:
Sub Balance_Sheet_Data()

Application.ScreenUpdating = False
Workbooks.Open Filename:="G:\Balance Sheet 2004.xls"
Cells.Select
Selection.Copy
Windows("DATA SOURCE - GOLF # 3-01.xls").Activate
Sheets("Balance Sheet Recon Data").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Windows("Balance Sheet 2004.xls").Activate
ActiveWorkbook.Close
Sheets("Command Page").Select
Range("A1").Select
Application.ScreenUpdating = True
End Subs

What visual basic command do I change?

Hi F.C

Try changing Paste to

PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=399805