Excel VBA - Copying Sheets to Closed Workbooks

  • Thread starter Thread starter ajlove20
  • Start date Start date
A

ajlove20

Hi,

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

a
 
Hi Ron,

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

=+ESTIMATE!C4

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

=+[example2.xls]ESTIMATE!C4

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

Thank you
 
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
 
Hi Ron,

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

a
 
Back
Top