Sheet names

[cmazur71]

I have a routine that copies the contents of one workbook sheet onto another. Sometimes the source workbook sheet is named "Export", sometimes it is named "Data".
How can I code this so that the data from the source workbook sheet is always copied, regardless if the sheet is named "Export" or "Data"

Here is my code...it will copy the data from a sheet named "Export"

Set currentworkbook = ThisWorkbook
Set sourceworkbook = Workbooks.Open(RQTReport)
sourceworkbook.Sheets("Export").Copy before:=currentworkbook.Sheets("QuoteTemplate")

 

[steve the fish]

You havent made it clear why you would use the sheet called Export or the sheet called Data. Are you saying only one or the other will exist?

 

[cmazur71]

You havent made it clear why you would use the sheet called Export or the sheet called Data. Are you saying only one or the other will exist?

Yes, it one or the other. There are 2 different data sources that I need to pull from...one is Export and one is Data. They contain the same info, but the sheet names will be different.

 

[steve the fish]

Consider something like this:

Function SheetExists(sSheet As String) As Boolean

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    If ws.Name = sSheet Then
        SheetExists = True
        Exit Function
    End If
Next
SheetExists = False
    
End Function

Sub whichsheet()

Dim arr, i As Long, sh As Worksheet

arr = Array("Export", "Data")

For i = LBound(arr) To UBound(arr)
    If SheetExists(CStr(arr(i))) Then
        Set sh = Sheets(arr(i))
        Exit For
    End If
Next

If Not sh Is Nothing Then MsgBox sh.Name

End Sub

That will put the sheet into the variable sh

 

[cmazur71]

Consider something like this:

Function SheetExists(sSheet As String) As Boolean

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    If ws.Name = sSheet Then
        SheetExists = True
        Exit Function
    End If
Next
SheetExists = False
   
End Function

Sub whichsheet()

Dim arr, i As Long, sh As Worksheet

arr = Array("Export", "Data")

For i = LBound(arr) To UBound(arr)
    If SheetExists(CStr(arr(i))) Then
        Set sh = Sheets(arr(i))
        Exit For
    End If
Next

If Not sh Is Nothing Then MsgBox sh.Name

End Sub

That will put the sheet into the variable sh

Is there any other way...I cannot figure out how to embed your code into mine. I don't know what to do with this function

 

[steve the fish]

You could do something like this:

For Each sh In sourceworkbook.Worksheets
    If sh.Name = "Export" Or sh.Name = "Data" Then
        sh.Copy before:=currentworkbook.Sheets("QuoteTemplate")
        Exit For
    End If
Next