I am working with a workbook that has over 40 tabs (worksheets). Each
worksheet is a department report and the last report is the one I need to
select for each department and copy it into a new workbook. The name of the
worksheets to be selected copied and pasted is "xxxxx-RSS Upload" where x is
the department number. I was trying to find a property that could only select
the "Upload" part of the worksheet name as a common denominator to select
multiple worksheets that end with that text string. Can somebody provide a
code that will do this??? I started of like this but cannot come up with a
condition:

Sub macro1_()
Dim ws As Worksheet
For Each ws In Worksheets
If ws. 'contains Upload at the end of the text string
ws.Activate
ws.Select
ws.copy After:= workbooks("NewBook").'how do I paste it as the last
worksheet?
End If
Next ws
End Sub
Thanks in advance for your help

Hi,
Try
'''----------------------------------------------------------------------------
Option Compare Text '''<<<< put this row at the top of the module
''' It enables 'A'='a' for text comparison
Sub macro1_()
Dim ws As Worksheet
For Each ws In Worksheets
If ws like "*Upload" then
'''ws.Activate <-- no need
'''ws.Select <-- no need
ws.copy After:= ws.parent.worksheets(ws.parent.worksheets.count)
End If
Next ws
End Sub
''' ------------------------------------------------------
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"FLORERO" wrote:

I am working with a workbook that has over 40 tabs (worksheets). Each
worksheet is a department report and the last report is the one I need to
select for each department and copy it into a new workbook. The name of the
worksheets to be selected copied and pasted is "xxxxx-RSS Upload" where x is
the department number. I was trying to find a property that could only select
the "Upload" part of the worksheet name as a common denominator to select
multiple worksheets that end with that text string. Can somebody provide a
code that will do this??? I started of like this but cannot come up with a
condition:

Sub macro1_()
Dim ws As Worksheet
For Each ws In Worksheets
If ws. 'contains Upload at the end of the text string
ws.Activate
ws.Select
ws.copy After:= workbooks("NewBook").'how do I paste it as the last
worksheet?
End If
Next ws
End Sub
Thanks in advance for your help

.... just realized my code copies the sheets at the end of the same book, but
seems like you try to copy to another book. In that case, say the new book is
"NewBook", replace:
ws.copy After:= ws.parent.worksheets(ws.parent.worksheets.count)
by
ws.copy After:= workbooks("newbook"). _
worksheets(workbooks("newbook").worksheets.count)
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"sebastienm" wrote:

Hi,
Try
'''----------------------------------------------------------------------------
Option Compare Text '''<<<< put this row at the top of the module
''' It enables 'A'='a' for text comparison
Sub macro1_()
Dim ws As Worksheet
For Each ws In Worksheets
If ws like "*Upload" then
'''ws.Activate <-- no need
'''ws.Select <-- no need
ws.copy After:= ws.parent.worksheets(ws.parent.worksheets.count)
End If
Next ws
End Sub
''' ------------------------------------------------------
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"FLORERO" wrote:

I am working with a workbook that has over 40 tabs (worksheets). Each
worksheet is a department report and the last report is the one I need to
select for each department and copy it into a new workbook. The name of the
worksheets to be selected copied and pasted is "xxxxx-RSS Upload" where x is
the department number. I was trying to find a property that could only select
the "Upload" part of the worksheet name as a common denominator to select
multiple worksheets that end with that text string. Can somebody provide a
code that will do this??? I started of like this but cannot come up with a
condition:

Sub macro1_()
Dim ws As Worksheet
For Each ws In Worksheets
If ws. 'contains Upload at the end of the text string
ws.Activate
ws.Select
ws.copy After:= workbooks("NewBook").'how do I paste it as the last
worksheet?
End If
Next ws
End Sub
Thanks in advance for your help

I tried running the code but I get Run Time error 438 'Object does not
support this property or method' for the following line:

If ws like "*Upload" then

I am using Excel 2003, is that the reason??
"sebastienm" wrote:

... just realized my code copies the sheets at the end of the same book, but
seems like you try to copy to another book. In that case, say the new book is
"NewBook", replace:
ws.copy After:= ws.parent.worksheets(ws.parent.worksheets.count)
by
ws.copy After:= workbooks("newbook"). _
worksheets(workbooks("newbook").worksheets.count)
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"sebastienm" wrote:

Hi,
Try
'''----------------------------------------------------------------------------
Option Compare Text '''<<<< put this row at the top of the module
''' It enables 'A'='a' for text comparison
Sub macro1_()
Dim ws As Worksheet
For Each ws In Worksheets
If ws like "*Upload" then
'''ws.Activate <-- no need
'''ws.Select <-- no need
ws.copy After:= ws.parent.worksheets(ws.parent.worksheets.count)
End If
Next ws
End Sub
''' ------------------------------------------------------
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"FLORERO" wrote:

I am working with a workbook that has over 40 tabs (worksheets). Each
worksheet is a department report and the last report is the one I need to
select for each department and copy it into a new workbook. The name of the
worksheets to be selected copied and pasted is "xxxxx-RSS Upload" where x is
the department number. I was trying to find a property that could only select
the "Upload" part of the worksheet name as a common denominator to select
multiple worksheets that end with that text string. Can somebody provide a
code that will do this??? I started of like this but cannot come up with a
condition:

Sub macro1_()
Dim ws As Worksheet
For Each ws In Worksheets
If ws. 'contains Upload at the end of the text string
ws.Activate
ws.Select
ws.copy After:= workbooks("NewBook").'how do I paste it as the last
worksheet?
End If
Next ws
End Sub
Thanks in advance for your help

For i = 1 To Sheets.Count
If UCase(Right(Trim(Sheets(i).Name), 6)) = "UPLOAD" Then
Sheets(i).Select
End If
Next

try adapting this
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"FLORERO" wrote:

I am working with a workbook that has over 40 tabs (worksheets). Each
worksheet is a department report and the last report is the one I need to
select for each department and copy it into a new workbook. The name of the
worksheets to be selected copied and pasted is "xxxxx-RSS Upload" where x is
the department number. I was trying to find a property that could only select
the "Upload" part of the worksheet name as a common denominator to select
multiple worksheets that end with that text string. Can somebody provide a
code that will do this??? I started of like this but cannot come up with a
condition:

Sub macro1_()
Dim ws As Worksheet
For Each ws In Worksheets
If ws. 'contains Upload at the end of the text string
ws.Activate
ws.Select
ws.copy After:= workbooks("NewBook").'how do I paste it as the last
worksheet?
End If
Next ws
End Sub
Thanks in advance for your help

Your code only goes through all the worksheets but does not copy into new
workbook. Can you provide details?? Thanks

"John Bundy" wrote:

For i = 1 To Sheets.Count
If UCase(Right(Trim(Sheets(i).Name), 6)) = "UPLOAD" Then
Sheets(i).Select
End If
Next

try adapting this
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"FLORERO" wrote:

I am working with a workbook that has over 40 tabs (worksheets). Each
worksheet is a department report and the last report is the one I need to
select for each department and copy it into a new workbook. The name of the
worksheets to be selected copied and pasted is "xxxxx-RSS Upload" where x is
the department number. I was trying to find a property that could only select
the "Upload" part of the worksheet name as a common denominator to select
multiple worksheets that end with that text string. Can somebody provide a
code that will do this??? I started of like this but cannot come up with a
condition:

Sub macro1_()
Dim ws As Worksheet
For Each ws In Worksheets
If ws. 'contains Upload at the end of the text string
ws.Activate
ws.Select
ws.copy After:= workbooks("NewBook").'how do I paste it as the last
worksheet?
End If
Next ws
End Sub
Thanks in advance for your help