Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Are there any NON-Visual Basic solutions for inserting a picture based a derived or called filename? | Excel Worksheet Functions | |||
Worksheet naming using visual basic | Excel Worksheet Functions | |||
visual basic command identify a worksheet in a workbook | Excel Programming | |||
inserting subtotal() function in spreadsheet via Visual Basic/macro | Excel Programming | |||
Saving Workbook in Visual Basic Hides the Worksheet, Why | Excel Programming |