Repeat copy for indifinate number of worksheets

[ARbitOUR]

Hi all!

I need some code that will copy a range (incl formatting etc.) of sheet
1, to the exact same range of another workbook's sheet 1. This copy
process must repeat for all worksheets in both workbooks even when some
sheets are deleted or added in the future. I'm not sure if it will work
using the 'For / Next' function. I have attached the example of the code
i have so far. The heavily indented mid section is where I need the
'Repeat copy' code.

Any help would help!



115:


+-------------------------------------------------------------------+
|Filename: Example.txt |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=115|
+-------------------------------------------------------------------+

 

[JLatham]

Sorry, not a member of that site, so I can't see your attachment and code.
But perhaps this code will get you going. You need to be working with
workbook, worksheet and range objects and it becomes pretty easy and you can
use the
For Each ... Next loop structure to deal with things.

General code looks like this
Sub ExactCopyBetweenBooks()
'copies specified range from each sheet
'in sourceWB to same range in
'a 2nd workbook (destWB)
'on a worksheet with the same name as in sourceWB
Dim destWB As Workbook
Dim destWS As Worksheet
Dim sourceWS As Worksheet
Dim sourceRange As Range

'you need to know the name of the other workbook,
'or code up a way to find what it is
'so you can Set destWB to the other workbook
Set destWB = Workbooks("DestinationWorkbook.xls")
'simple example, copies the same range
'from all worksheets in this workbook
'to sheets with same name in the other workbook
For Each sourceWS In ThisWorkbook.Worksheets
Set sourceRange = sourceWS.Range("A1:Z99")
For Each destWS In destWB.Worksheets
If destWS.Name = sourceWS.Name Then
sourceRange.Copy
destWS.Range("A1").PasteSpecial xlPasteAll
Exit For ' done with this page
End If
Next ' keep looking for sheet name match
Next ' look at next sheet in this workbook
'cleanup and housekeeping
Set sourceRange = Nothing
Set destWB = Nothing
End Sub

 

[ARbitOUR]

Thanx Latham!

With a bit of tweaking it worked a treat

 

[JLatham]

Thanks for letting us all know that it worked reasonably well, glad I was
able to help a little.