Copy fixed range of multiple sheets into new workbook

[A. Karatas]

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

 

[Norman Jones]

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
'<<=============

 

[A. Karatas]

Hi Norman,

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

 

[A. Karatas]

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

 

[Norman Jones]

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
'<<=============

 

[A. Karatas]

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.

 

[A. Karatas]

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

 

[Norman Jones]

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.

 

[Norman Jones]

Hi A,

See my immediately preceding response.

 

[A. Karatas]

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