CODEWITHSUNDEEP
excelvbacopyworksheet
Michael Riley
Michael Riley

Reputation: 1

Copying a worksheet to a new worksheet

The code executes and doesn't generate an error message, but no new worksheet is generated. All the other edits to the worksheet proceed just fine.

The section of code which performs this operation:

Function CheckCurrSheet() As Boolean
    Dim NamePath As String
    Dim CurrSheet As String
    Dim JohnWB As Workbook
    Dim ws As Worksheet

    NamePath = "C:\Data Files\2018\"  'Directory path for files.
    Set JohnWB = Workbooks.Open(Filename:=NamePath & "John\John Monthly Stats.xlsx")

    If Not WorksheetExists(CurrSheet, JohnWB) Then
        CheckCurrSheet = False

        With JohnWB
            Sheets(Sheets.Count).Copy After:=Sheets(Sheets.Count)
        End With

    Else
        CheckCurrSheet = True
    End If
End Function

Function WorksheetExists(shtName As String, Optional ByVal wb As Workbook) As Boolean
    Dim sht As Worksheet

    If wb Is Nothing Then Set wb = ThisWorkbook
    On Error Resume Next
    Set sht = wb.Sheets(shtName)
    On Error GoTo 0
    WorksheetExists = Not sht Is Nothing
End Function

Update: I found that the command to copy the worksheet is happening to another open workbook not JohnWB. I specifically reference it by the With JohnWB statement.

The other workbook needs to be open. How do I make the COPY command go to the JohnWB workbook?

Update: I managed to get it working.

' Old way of trying, which didn't work.
'With JohnWB
'    Sheets(Sheets.Count).Copy After:=Sheets(Sheets.Count)
'End With

' New way of trying, which works!
JohnWB.Sheets(Sheets.Count).Copy After:=JohnWB.Sheets(Sheets.Count)

Upvotes: 0

Views: 67

Answers (3)

niton
niton

Reputation: 9199

Missing a period preceding Sheets(Sheets.Count).Copy After:=Sheets(Sheets.Count).

With JohnWB
    .Sheets(Sheets.Count).Copy After:=Sheets(Sheets.Count)
End With

Upvotes: 0

TinMan
TinMan

Reputation: 7759

The value of CurrSheet is never initiated.

This should fix it

CurrSheet = ActiveSheet.Name
Set JohnWB = Workbooks.Open(Filename:=NamePath & "John\John Monthly Stats.xlsx")

Upvotes: 1

mpwbs92
mpwbs92

Reputation: 19

Try watching this person. If this doesn't help I'm sure if you go look around YouTube you'll find lots of videos like this one.

https://youtu.be/G-KlCkMt9Fw

Upvotes: 1

Related Questions