Excel VBA - Copying Sheets to Closed Workbooks

Try this aj
http://www.rondebruin.nl/copy4.htm



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


"ajlove20 " wrote in message ...
Hi,

I just created a new sheet called "Review" on a workbook that I am
working on and I would like to copy the complete sheet to my other
workbooks. There are about 40 to 50 other workbooks and I don't want
to have to open each of them to create a new sheet. Is there a VBA
code I can use that will copy this sheet into each of the other
workbooks? Thank you.

aj


---
Message posted from http://www.ExcelForum.com/

Hi

Does the sheet ESTIMATE exist in all your workbooks?

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


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

Thank you for your help. It worked fine, but there was one thing. I
had formulas in the sheet i wanted to copy that are related to that its
own workbook. When I copied it to the other files, the formula
reference the original workbook. Here is an example of what I am
talking about. The original workbook (example2.xls) had a formula that
looked like this:

=+ESTIMATE!C4

One of the workbooks that the sheet copied to had a formula that looked
like this:

=+[example2.xls]ESTIMATE!C4

I just wanted the new sheet to reference its own workbook to complete
the formula.

Thank you.


---
Message posted from http://www.ExcelForum.com/

If the sheet exist in all your workbook try this
(untested)

Run the macro Test on the sheet you want to copy
All formulas are text now.

Sub test()
For Each cell In Cells.SpecialCells(xlCellTypeFormulas)
cell.Value = "'" & cell.Formula
Next
End Sub


Run the example macro on my site that you used
Only use the code that is comented also now

With ActiveSheet.UsedRange
.Value = .Value
End With


Run this macro to restore your original worksheet(Sheet that you copy) when you ready.

Sub test2()
With ActiveSheet.UsedRange
..Value = .Value
End With
End Sub


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


"ajlove20 " wrote in message ...
yeah, ESTIMATE exists in all the workbooks


---
Message posted from http://www.ExcelForum.com/

You are welcome



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


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

Thank you so much. It worked perfectly. I appreciate you taking time
to help me. Thanks again.

aj


---
Message posted from http://www.ExcelForum.com/