Copy fixed range of multiple sheets into new workbook
 

I have a workbook with multiple sheets in them. I want to copy a
particular fixed range of each sheet, in which the sheetname ends on
PY) into a new workbook. For example sheetname= Emuls(actual vs PY)
range b4:s40.

Each range has to be copied into a (seperate) new sheet with the
sheetname it had on the original workbook.

I have no clou how to start the procedure????

thankx in advance

Copy fixed range of multiple sheets into new workbook
 

Hi A,

Try something like:

Public Sub aTester001()
Dim srcWB As Workbook
Dim destWB As Workbook
Dim SH As Worksheet
Dim destSH As Worksheet
Dim srcRng As Range
Dim blOK As Boolean
Const sStr As String = "PY" '<<=== CHANGE

Set srcWB = Workbooks("MyBokk.xls") '<<=== CHANGE

blOK = True
For Each SH In srcWB.Worksheets
If UCase(SH.Name) Like UCase("*" & sStr) Then
If blOK Then
Set destWB = Workbooks.Add
End If
blOK = False
With destWB
Set destSH = .Sheets.Add( _
After:=.Sheets(.Sheets.Count))
End With
With destSH
.Name = SH.Name
SH.Range("B4:S40").Copy Destination:=.Range("A1")
End With
End If
Next SH

End Sub
'<<=============


---
Regards,
Norman


"A. Karatas" wrote in message
ups.com...
I have a workbook with multiple sheets in them. I want to copy a
particular fixed range of each sheet, in which the sheetname ends on
PY) into a new workbook. For example sheetname= Emuls(actual vs PY)
range b4:s40.

Each range has to be copied into a (seperate) new sheet with the
sheetname it had on the original workbook.

I have no clou how to start the procedure????

thankx in advance

Copy fixed range of multiple sheets into new workbook
 

Hi Norman,

It works great, but what if I want the copied figures to be in "paste
special" format and values???

Copy fixed range of multiple sheets into new workbook
 

Hi Norman,

It works great, but what if I want the copied sheets to be in "paste
special" format and values???

Also the workbookname changes each month. Is there a way of
incorporating a procedure in which the target workbook is defined
like:
Dim TargetWB As Workbook
Set TargetWB = ActiveWorkbook


Thankx

Copy fixed range of multiple sheets into new workbook
 

Hi A,

Try something like:

'<<=============
Public Sub aTester()
Dim srcWB As Workbook
Dim destWB As Workbook
Dim SH As Worksheet
Dim destSH As Worksheet
Dim srcRng As Range
Const sStr As String = "PY" '<<=== CHANGE

Set srcWB = Workbooks("MyBook.xls") '<<=== CHANGE
Set destWB = ActiveWorkbook

For Each SH In srcWB.Worksheets
If UCase(SH.Name) Like UCase("*" & sStr) Then
With destWB
Set destSH = .Sheets.Add( _
After:=.Sheets(.Sheets.Count))
End With
destSH.Name = SH.Name
With SH.Range("B4:S40")
destSH.Range("A1").Resize(.Rows.Count, _
.Columns.Count).Value = .Value
End With

End If
Next SH
End Sub
'<<=============


---
Regards,
Norman


"A. Karatas" wrote in message
ups.com...
Hi Norman,

It works great, but what if I want the copied sheets to be in "paste
special" format and values???

Also the workbookname changes each month. Is there a way of
incorporating a procedure in which the target workbook is defined
like:
Dim TargetWB As Workbook
Set TargetWB = ActiveWorkbook


Thankx

Copy fixed range of multiple sheets into new workbook
 

Hi Norman

The line ' destSH.Name = SH.Name ' gives an error.

Cannot rename a sheet to the same name as another sheet, a referenced
object library or a workbook referenced by visual basic.

Copy fixed range of multiple sheets into new workbook
 

Hi

The line ' destSH.Name = SH.Name ' gives an error.


Cannot rename a sheet to the same name as another sheet, a referenced
object library or a workbook referenced by visual basic.

It looks like the destination of the sheets is in the mastersheet
itself

Copy fixed range of multiple sheets into new workbook
 

Hi A,

The code works for me without problem.

I could, however, reproduce your error if I
inadvertently trised to re-run the procedure;
that is not surprising as it is not possible to
create 2 sets of identically named sheets in the
same workbook.

BTW, srcWb should be the workbook which
contains the sheets to be copied; destWb should
be the active workbook (in accordance with your
request) and is the workbook which should receive
the copied sheets.


---
Regards,
Norman

"A. Karatas" wrote in message
ups.com...
Hi Norman

The line ' destSH.Name = SH.Name ' gives an error.

Cannot rename a sheet to the same name as another sheet, a referenced
object library or a workbook referenced by visual basic.

Copy fixed range of multiple sheets into new workbook
 

Hi A,

See my immediately preceding response.


---
Regards,
Norman

"A. Karatas" wrote in message
ups.com...
Hi

The line ' destSH.Name = SH.Name ' gives an error.


Cannot rename a sheet to the same name as another sheet, a referenced
object library or a workbook referenced by visual basic.

It looks like the destination of the sheets is in the mastersheet
itself

Copy fixed range of multiple sheets into new workbook
 

Norman,

The first macro you wrote, works perfectly each time. It copies the
sheets and puts it in the destWB. The second still gives an error and
in my opinion tries to put the copied sheets into the srcWB

Perhaps it's an idea to work out the first macro in which the sheets
are copied in fixed value and format.

I know I maybe asking much, but as I am not that familiar with VB,
your a hope for me :)

thankx