VBA code to copy/paste the formatting of the entire workbook.

Hello Reader,

I am trying to write a VBA code, which copy/pastes the formatting (just the cell background) of the used range of one workbook to the another. It should just copy/paste the cell background and nothing else formats say, fonts, cell styles etc.

I have tried my best, but not yet reached the solution. If you have done something like this in the past, your help is much appreciated !

PS: Both the worksheets from where the cell background is copied and where it is been pasted has the same sheet names and the same sheet structure.

Thanks,
Sourav

Perhaps something along these lines (untested):

Code:

---

Sub blah()
Set SceWkBk = ThisWorkbook  'or whatever the source workbook is.
Set DestWkBk = Workbooks("something.xls")  'or whatever the estination workbook is.

For Each SceSheet In SceWkBk.Sheets
  Set DestSht = DestWkBk.Sheets(SceSht.Name)
  For Each cll In SceSht.UsedRange.Cells
    DestSht.Range(cll.Address).Interior.ColorIndex = cll.Interior.ColorIndex
  Next cll
Next SceSht
End Sub

---

I'm using Excel 2003 here right now and it might be you want to copy more/different cell properties across. Record a macro of your making some cell format changes of the ilk that you want to copy across, look at the code, and you can usually delete 90% of the properties therein as they're the default values anyway. If you're not sure, post a copy of the recorded macro here and say what aspects of the cell formatting you want to transfer.