Copying worksheets to remainder of workbook

[Paul987]

I am trying to copy a range of cells (columns E:O) from sheet2 to all remaining sheets in my workbook, the number and names of which will vary. Sheet 1 needs to remain the same. I am able to do this for all sheets, but am having difficulty forcing the macro to skip Sheet 1. Any help would be appreciated. Thanks

[David Lloyd]

Paul:

Since I only have a general idea of what you are doing, my comments are
general.

In general, you can look at the Name property of each sheet as you iterate
through all the sheets in the workbook to make sure the current sheet is not
Sheet1. For example:

Function Test()

Dim wks As Worksheet

For Each wks In ActiveWorkbook.Sheets
If wks.Name <> "Sheet1" Then
'Code hear to paste cell range

End If
Next wks

End Function

If the name of Sheet1 is variable, you can examine the name of the sheet as
follows:

sSheet1 = ActiveWorkbook.Sheets(1).Name

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


"Paul987" <[email protected]> wrote in
message news:[email protected]...

I am trying to copy a range of cells (columns E:O) from sheet2 to all
remaining sheets in my workbook, the number and names of which will
vary. Sheet 1 needs to remain the same. I am able to do this for
*all * sheets, but am having difficulty forcing the macro to skip Sheet
1. Any help would be appreciated. Thanks


--
Paul987
------------------------------------------------------------------------
Paul987's Profile:
http://www.excelforum.com/member.php...o&userid=24850
View this thread: http://www.excelforum.com/showthread...hreadid=383977

[Paul987]

David - thanks for the help.

It seems to be on the right track, but I am missing something. I will try to give more detail. I have a workbook with many sheets, the number of which will vary. The first two worksheets ("sheet1" and "sheet2") will remain constant. I need to copy sheet2.columns(E:O) to all remaining sheets in columns(E:O). The code seems to select and copy the correct data, but returns an error regarding the size and shape of paste area being different. I am very new to this, and have been proceeding through trial and error. Any additional help would be great.

This is where I now stand:

Sub Copyandpaste()

Dim wks As Worksheet

Sheets(2).Select
Columns("E:O").Select
Selection.Copy

For Each wks In ActiveWorkbook.Sheets

If wks.Name <> "Sheet1" And wks.Name <> "Sheet2" Then
Columns("E:O").Select
wks.Paste
End If

Next wks

End Sub