Sheets("Sheet").Copy problem

G

Guest

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,
 
G

Guest

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 before:=Worksheets(1)
to put it at the start of the workbook, or you can even reference itself as:

Worksheets("Report").Copy before:=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.
 
G

Guest

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 said:
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 before:=Worksheets(1)
to put it at the start of the workbook, or you can even reference itself as:

Worksheets("Report").Copy before:=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 said:
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,
Click to expand...
Click to expand...
 
G

Guest

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 _
before:=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 said:
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 said:
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 before:=Worksheets(1)
to put it at the start of the workbook, or you can even reference itself as:

Worksheets("Report").Copy before:=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 said:
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,
Click to expand...
Click to expand...
Click to expand...
 
J

Jon Peltier

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Application.DoubleClick question 9
Application.GoTo.Reference query 1
evalute error 1
Select Case in ComboBox 5
Run time 9 error for some users but not everyone 10
Copy Array to Array, how to and should I? 13
Unhide sheet, copy and rename new sheets from list, rehide sheet 8
Create Sheets from a Range and Copy Data on copy Data on newlycreated Sheets with the Sheet Names 2

Top