Hi All,

I have a series of Case Statements which will write to a report sheet. I
then want to copy the sheet to a new workbook. I have placed the command
Sheets("Report").Copy in various places, but it does nothing. Any suggestions
as to what I am doing wrong?
Private Sub ComboBox1_click()
Dim i As Long, rng As Range
Dim refrange As Range
Dim c As Range

ComboBox2.ListIndex = -1
ComboBox3.ListIndex = -1
ComboBox4.ListIndex = -1
ComboBox5.ListIndex = -1

Select Case ComboBox1.Value
Case "GSOP_0286"
Sheets("Report").Range("A4:I20").Clear
Set refrange = Sheets("Sheet2").Range("A3:A20")
i = 0
For Each c In refrange
If c.Value = "" Then
End
Else
s = Replace(c.Formula, "=", "")
Set rng = Evaluate(s)
rng.EntireRow.Copy
Sheets("Report").Range("A4") _
.Offset(i, 0) _
.PasteSpecial Paste:= _
xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
i = i + 1
End If
Next c
[More Case Statements]

End Select
Sheets (€œReport€�).Select
Sheets (€œReport€�).Copy

Thanks,

--
Brian McCaffery

You can replace the two lines at the end with one line:
Worksheets("Report").Copy after:=Worksheets(Worksheets.Count)
to copy it to the end of the current workbook, or even

Worksheets("Report").Copy befo=Worksheets(1)
to put it at the start of the workbook, or you can even reference itself as:

Worksheets("Report").Copy befo=Worksheets("Report")
to put it just ahead of the sheet copied.

At that point, the copy of the sheet, 'Report (2)' will also become the
active sheet. If you need to 'remain' on the original sheet, put
Worksheets("Report").Activate
right after the copy to get back to the original.


"Brian" wrote:

Hi All,

I have a series of Case Statements which will write to a report sheet. I
then want to copy the sheet to a new workbook. I have placed the command
Sheets("Report").Copy in various places, but it does nothing. Any suggestions
as to what I am doing wrong?
Private Sub ComboBox1_click()
Dim i As Long, rng As Range
Dim refrange As Range
Dim c As Range

ComboBox2.ListIndex = -1
ComboBox3.ListIndex = -1
ComboBox4.ListIndex = -1
ComboBox5.ListIndex = -1

Select Case ComboBox1.Value
Case "GSOP_0286"
Sheets("Report").Range("A4:I20").Clear
Set refrange = Sheets("Sheet2").Range("A3:A20")
i = 0
For Each c In refrange
If c.Value = "" Then
End
Else
s = Replace(c.Formula, "=", "")
Set rng = Evaluate(s)
rng.EntireRow.Copy
Sheets("Report").Range("A4") _
.Offset(i, 0) _
.PasteSpecial Paste:= _
xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
i = i + 1
End If
Next c
[More Case Statements]

End Select
Sheets (€œReport€�).Select
Sheets (€œReport€�).Copy

Thanks,

--
Brian McCaffery

Thanks for the response.

My problem is that using the .copy command, I want to copy to a new
workbook. However at the end of the case when the report is produced, the
command doesn't create a new book or copy.

I have tested the command on a simple worksheet which worked. This leads me
to believe that there is a conflict in my code causing the problem. Possibly
the placement.

Any ideas?

Thanks
--
Brian McCaffery


"JLatham" wrote:

You can replace the two lines at the end with one line:
Worksheets("Report").Copy after:=Worksheets(Worksheets.Count)
to copy it to the end of the current workbook, or even

Worksheets("Report").Copy befo=Worksheets(1)
to put it at the start of the workbook, or you can even reference itself as:

Worksheets("Report").Copy befo=Worksheets("Report")
to put it just ahead of the sheet copied.

At that point, the copy of the sheet, 'Report (2)' will also become the
active sheet. If you need to 'remain' on the original sheet, put
Worksheets("Report").Activate
right after the copy to get back to the original.


"Brian" wrote:

Hi All,

I have a series of Case Statements which will write to a report sheet. I
then want to copy the sheet to a new workbook. I have placed the command
Sheets("Report").Copy in various places, but it does nothing. Any suggestions
as to what I am doing wrong?
Private Sub ComboBox1_click()
Dim i As Long, rng As Range
Dim refrange As Range
Dim c As Range

ComboBox2.ListIndex = -1
ComboBox3.ListIndex = -1
ComboBox4.ListIndex = -1
ComboBox5.ListIndex = -1

Select Case ComboBox1.Value
Case "GSOP_0286"
Sheets("Report").Range("A4:I20").Clear
Set refrange = Sheets("Sheet2").Range("A3:A20")
i = 0
For Each c In refrange
If c.Value = "" Then
End
Else
s = Replace(c.Formula, "=", "")
Set rng = Evaluate(s)
rng.EntireRow.Copy
Sheets("Report").Range("A4") _
.Offset(i, 0) _
.PasteSpecial Paste:= _
xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
i = i + 1
End If
Next c
[More Case Statements]

End Select
Sheets (€œReport€�).Select
Sheets (€œReport€�).Copy

Thanks,

--
Brian McCaffery

I don't see you creating or opening another workbook in your code. Your code
doesn't tell where the worksheet you're copying is to be copied to - either
somewhere else in the existing workbook as my earlier code showed how to do,
or to any other location. Your code basically stops at the "Copy" stage
without completing the 'paste' operation, so to speak.

This code should show you how to get the job done:

Sub CopyToNEWBook()
Dim sourceBook As String
Dim sheetToCopy As String
Dim LC As Integer ' loop counter

sourceBook = ThisWorkbook.Name
sheetToCopy = "Report"

Workbooks.Add ' creates a new workbook (with 3 blank sheets)
'this leaves you in the new workbook,
'with it as the Active Workbook, so
'must reference specific book/sheet to copy
Workbooks(sourceBook).Worksheets(sheetToCopy).Copy _
befo=Worksheets(1)

'delete the default sheets created when
'the new workbook was made for neatness
Application.DisplayAlerts = False
For LC = 1 To 3
Worksheets("Sheet" & LC).Delete
Next
Application.DisplayAlerts = True

'if you need to get back into
'the original workbook now:
Workbooks(sourceBook).Activate

End Sub



"Brian" wrote:

Thanks for the response.

My problem is that using the .copy command, I want to copy to a new
workbook. However at the end of the case when the report is produced, the
command doesn't create a new book or copy.

I have tested the command on a simple worksheet which worked. This leads me
to believe that there is a conflict in my code causing the problem. Possibly
the placement.

Any ideas?

Thanks
--
Brian McCaffery


"JLatham" wrote:

You can replace the two lines at the end with one line:
Worksheets("Report").Copy after:=Worksheets(Worksheets.Count)
to copy it to the end of the current workbook, or even

Worksheets("Report").Copy befo=Worksheets(1)
to put it at the start of the workbook, or you can even reference itself as:

Worksheets("Report").Copy befo=Worksheets("Report")
to put it just ahead of the sheet copied.

At that point, the copy of the sheet, 'Report (2)' will also become the
active sheet. If you need to 'remain' on the original sheet, put
Worksheets("Report").Activate
right after the copy to get back to the original.


"Brian" wrote:

Hi All,

I have a series of Case Statements which will write to a report sheet. I
then want to copy the sheet to a new workbook. I have placed the command
Sheets("Report").Copy in various places, but it does nothing. Any suggestions
as to what I am doing wrong?
Private Sub ComboBox1_click()
Dim i As Long, rng As Range
Dim refrange As Range
Dim c As Range

ComboBox2.ListIndex = -1
ComboBox3.ListIndex = -1
ComboBox4.ListIndex = -1
ComboBox5.ListIndex = -1

Select Case ComboBox1.Value
Case "GSOP_0286"
Sheets("Report").Range("A4:I20").Clear
Set refrange = Sheets("Sheet2").Range("A3:A20")
i = 0
For Each c In refrange
If c.Value = "" Then
End
Else
s = Replace(c.Formula, "=", "")
Set rng = Evaluate(s)
rng.EntireRow.Copy
Sheets("Report").Range("A4") _
.Offset(i, 0) _
.PasteSpecial Paste:= _
xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
i = i + 1
End If
Next c
[More Case Statements]

End Select
Sheets (€œReport€�).Select
Sheets (€œReport€�).Copy

Thanks,

--
Brian McCaffery

You realize that the "End" statement completely ends execution? I wonder if
this is happening. The Sheets().Copy otherwise should work fine to create a
new workbook with just the indicated sheet.

Have you stepped through the code in the debugger? Where does the execution
trail take you?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Brian" wrote in message
...
Hi All,

I have a series of Case Statements which will write to a report sheet. I
then want to copy the sheet to a new workbook. I have placed the command
Sheets("Report").Copy in various places, but it does nothing. Any
suggestions
as to what I am doing wrong?
Private Sub ComboBox1_click()
Dim i As Long, rng As Range
Dim refrange As Range
Dim c As Range

ComboBox2.ListIndex = -1
ComboBox3.ListIndex = -1
ComboBox4.ListIndex = -1
ComboBox5.ListIndex = -1

Select Case ComboBox1.Value
Case "GSOP_0286"
Sheets("Report").Range("A4:I20").Clear
Set refrange = Sheets("Sheet2").Range("A3:A20")
i = 0
For Each c In refrange
If c.Value = "" Then
End
Else
s = Replace(c.Formula, "=", "")
Set rng = Evaluate(s)
rng.EntireRow.Copy
Sheets("Report").Range("A4") _
.Offset(i, 0) _
.PasteSpecial Paste:= _
xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
i = i + 1
End If
Next c
[More Case Statements]

End Select
Sheets ("Report").Select
Sheets ("Report").Copy

Thanks,

--
Brian McCaffery