Copy sheet from one workbook to another workbook problem
 

Hi VJ

You can do this

Select all cells
Use replace "=" to " =" (see the space before =)
Without quotes

Copy the sheet

And in the new workbook
Use replace " =" to "=" Without quotes

If you need help to do this in code post your macro
and I will try to help you


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


"VJ" wrote in message ...
I am copying a sheet from one workbook (wb1) to another workbook(wb2) through
a VBA macro.

I can copy the sheet but there is one problem in copying process. After
copying I can see the following code in my cell.

=+'[wb1.xls]Sheet2'!C11

Is there any way to get rid of '[wb1.xls]' portion which is name of the
workbook? I need to keep Sheet2 but dont want to keep the name of the
workbook.

Help would really be appreciated.

Thanks and rgds,

Copy sheet from one workbook to another workbook problem
 

Try this basic example
I don't use your code because it is not easy to test for me

I use Book1.xls and Book2.xls and a sheet named "Sheet1"

Sub test()
With Workbooks("Book1.xls").Sheets("Sheet1")
.Cells.Replace What:="=", Replacement:=" =", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

.Cells.Copy Workbooks("Book2.xls").Sheets("Sheet1").Range("A1" )

.Cells.Replace What:=" =", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With

With Workbooks("Book2.xls").Sheets("Sheet1")
.Cells.Replace What:=" =", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With

End Sub



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


"VJ" wrote in message ...
Hi Ron,

Following is my code which basically copy from one workbook to another

***********Code Starts*******************
'Get the name of the destination workbook.
str_Destination_WBName = ActiveWorkbook.Name
'Activate the Destination workbook.
Windows(str_Destination_WBName).Activate
'Activate the Destination worksheet

Workbooks(str_Destination_WBName).SheetsCOPY_SHEET _NAME).Activate
'Delete all the shapes in the destination worksheet
DeleteAllShapes
'Select all the cells and unmerge them for formatting.
Cells.Select
Selection.UnMerge
'Activate the source copy worksheet as well as select and copy
all the cells.
Workbooks(str_Source_WBName).Sheets(COPY_SHEET_NAM E).Activate
Cells.Select
Selection.Copy
'Activate the Destination workbook.
Windows(str_Destination_WBName).Activate
'Activate the destination worksheet paste the contents from the
source sheet.
Workbooks(str_Destination_WBName).Sheets(COPY_SHEE T_NAME).Activate
ActiveSheet.Paste
Range("A1").Select
'Close the activeworkbook
ActiveWorkbook.Close savechanges:=True

******************Code Ends********************
Pls help me out.

Thanks Ron


"Ron de Bruin" wrote:

Hi VJ

You can do this

Select all cells
Use replace "=" to " =" (see the space before =)
Without quotes

Copy the sheet

And in the new workbook
Use replace " =" to "=" Without quotes

If you need help to do this in code post your macro
and I will try to help you


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


"VJ" wrote in message ...
I am copying a sheet from one workbook (wb1) to another workbook(wb2) through
a VBA macro.

I can copy the sheet but there is one problem in copying process. After
copying I can see the following code in my cell.

=+'[wb1.xls]Sheet2'!C11

Is there any way to get rid of '[wb1.xls]' portion which is name of the
workbook? I need to keep Sheet2 but dont want to keep the name of the
workbook.

Help would really be appreciated.

Thanks and rgds,

Copy sheet from one workbook to another workbook problem
 

It works Ron.

Thanks alot and regards,

VJ

"Ron de Bruin" wrote:

Try this basic example
I don't use your code because it is not easy to test for me

I use Book1.xls and Book2.xls and a sheet named "Sheet1"

Sub test()
With Workbooks("Book1.xls").Sheets("Sheet1")
.Cells.Replace What:="=", Replacement:=" =", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

.Cells.Copy Workbooks("Book2.xls").Sheets("Sheet1").Range("A1" )

.Cells.Replace What:=" =", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With

With Workbooks("Book2.xls").Sheets("Sheet1")
.Cells.Replace What:=" =", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With

End Sub



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


"VJ" wrote in message ...
Hi Ron,

Following is my code which basically copy from one workbook to another

***********Code Starts*******************
'Get the name of the destination workbook.
str_Destination_WBName = ActiveWorkbook.Name
'Activate the Destination workbook.
Windows(str_Destination_WBName).Activate
'Activate the Destination worksheet

Workbooks(str_Destination_WBName).SheetsCOPY_SHEET _NAME).Activate
'Delete all the shapes in the destination worksheet
DeleteAllShapes
'Select all the cells and unmerge them for formatting.
Cells.Select
Selection.UnMerge
'Activate the source copy worksheet as well as select and copy
all the cells.
Workbooks(str_Source_WBName).Sheets(COPY_SHEET_NAM E).Activate
Cells.Select
Selection.Copy
'Activate the Destination workbook.
Windows(str_Destination_WBName).Activate
'Activate the destination worksheet paste the contents from the
source sheet.
Workbooks(str_Destination_WBName).Sheets(COPY_SHEE T_NAME).Activate
ActiveSheet.Paste
Range("A1").Select
'Close the activeworkbook
ActiveWorkbook.Close savechanges:=True

******************Code Ends********************
Pls help me out.

Thanks Ron


"Ron de Bruin" wrote:

Hi VJ

You can do this

Select all cells
Use replace "=" to " =" (see the space before =)
Without quotes

Copy the sheet

And in the new workbook
Use replace " =" to "=" Without quotes

If you need help to do this in code post your macro
and I will try to help you


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


"VJ" wrote in message ...
I am copying a sheet from one workbook (wb1) to another workbook(wb2) through
a VBA macro.

I can copy the sheet but there is one problem in copying process. After
copying I can see the following code in my cell.

=+'[wb1.xls]Sheet2'!C11

Is there any way to get rid of '[wb1.xls]' portion which is name of the
workbook? I need to keep Sheet2 but dont want to keep the name of the
workbook.

Help would really be appreciated.

Thanks and rgds,

Copy sheet from one workbook to another workbook problem
 

You are welcome

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


"VJ" wrote in message ...
It works Ron.

Thanks alot and regards,

VJ

"Ron de Bruin" wrote:

Try this basic example
I don't use your code because it is not easy to test for me

I use Book1.xls and Book2.xls and a sheet named "Sheet1"

Sub test()
With Workbooks("Book1.xls").Sheets("Sheet1")
.Cells.Replace What:="=", Replacement:=" =", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

.Cells.Copy Workbooks("Book2.xls").Sheets("Sheet1").Range("A1" )

.Cells.Replace What:=" =", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With

With Workbooks("Book2.xls").Sheets("Sheet1")
.Cells.Replace What:=" =", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With

End Sub



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


"VJ" wrote in message ...
Hi Ron,

Following is my code which basically copy from one workbook to another

***********Code Starts*******************
'Get the name of the destination workbook.
str_Destination_WBName = ActiveWorkbook.Name
'Activate the Destination workbook.
Windows(str_Destination_WBName).Activate
'Activate the Destination worksheet

Workbooks(str_Destination_WBName).SheetsCOPY_SHEET _NAME).Activate
'Delete all the shapes in the destination worksheet
DeleteAllShapes
'Select all the cells and unmerge them for formatting.
Cells.Select
Selection.UnMerge
'Activate the source copy worksheet as well as select and copy
all the cells.
Workbooks(str_Source_WBName).Sheets(COPY_SHEET_NAM E).Activate
Cells.Select
Selection.Copy
'Activate the Destination workbook.
Windows(str_Destination_WBName).Activate
'Activate the destination worksheet paste the contents from the
source sheet.
Workbooks(str_Destination_WBName).Sheets(COPY_SHEE T_NAME).Activate
ActiveSheet.Paste
Range("A1").Select
'Close the activeworkbook
ActiveWorkbook.Close savechanges:=True

******************Code Ends********************
Pls help me out.

Thanks Ron


"Ron de Bruin" wrote:

Hi VJ

You can do this

Select all cells
Use replace "=" to " =" (see the space before =)
Without quotes

Copy the sheet

And in the new workbook
Use replace " =" to "=" Without quotes

If you need help to do this in code post your macro
and I will try to help you


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


"VJ" wrote in message ...
I am copying a sheet from one workbook (wb1) to another workbook(wb2) through
a VBA macro.

I can copy the sheet but there is one problem in copying process. After
copying I can see the following code in my cell.

=+'[wb1.xls]Sheet2'!C11

Is there any way to get rid of '[wb1.xls]' portion which is name of the
workbook? I need to keep Sheet2 but dont want to keep the name of the
workbook.

Help would really be appreciated.

Thanks and rgds,