Dynamically Specify Sheets from a Cell - VBA

Question

I am not sure if this is possible, but this is the only thing I did not find answered on the Internet.

I created one template workbook Schedule.xlsthat different people will fill in, say personA, personBand personC. I need to extract the same range from each book by copying it and pasting it into the main file Master.xlsso that I can get information from each person in this book.

This Master.xlswill be as many sheets as people fill Schedule.xls.

For example, let's stay with these three people: personA, personBand personC.

As soon as they generate their schedule, I want to get this information and copy it into Master.xls, but in separate sheets with the name personA, personBand personC.

I want to do this by setting the cell to Schedule.xls, say A1, where people can choose a value between personA, personBand personC.

That way I can create a dynamic link for the sheet in Master.xls. in which the macro will insert information.

`Range("B2.D5").Select
Selection.Copy
Workbooks.Open Filename:= _
    "C:\My Documents\Master.xlsx"
Sheets(*REFERENCE*).Select
Range("B2").Select
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close`

What should I write instead of the LINK in order to set the sheet to which I want to write?

Thanks in advance.

Answer 1

. VBA .

, Carl SlaveWB.xlsx Master.slxm, . , ( ) . . A1 . "=", A1 ( ). . A1, - / A1 . . . . , "" Carl.

( ), .

"" . . "1", A1, WSName: ( "SlaveWB" ). ( "1" ). (1,1).Value.

'DeleteWorksheet(WSName)
Public Function DeleteWorksheet(WSName As String)
    'If Not IIf(IsNull(DebugMode), False, DebugMode) Then On Error GoTo FoundError
    If Not Range("DebugMode").Value Then On Error Resume Next

    Dim WorksheetExists As Boolean
    DeleteWorksheet = False

    'if no worksheet name provided, abort
        If Len(WSName) < 1 Then Exit Function

    'if worksheet exists, delete
        WorksheetExists = False
        On Error Resume Next
        WorksheetExists = (Sheets(WSName).Name <> "") 'if worksheet exists, set WorksheetExists = True
        On Error GoTo FoundError
        If WorksheetExists Then Sheets(WSName).Delete 'if worksheet exists, delete
        DeleteWorksheet = True  'function succeeded (deleted worksheet if it existed)

    Exit Function
FoundError:
    On Error Resume Next
    DeleteWorksheet = False
    Debug.Print "Error: DeleteWorksheet(" & WSName & ") failed to delete worksheet.  "
End Function

. srcWBName tgtWBName , tgtWBName. , master. , . .

Sub CopyWSBetweenWBs(srcWBName As String, srcWSName As String, _
                  tgtWBName As String, tgtWSName As String)

    'srcWBName - name of PersonA workbook
    'srcWSName - name of worksheet to copy from Person A workbook
    'tgtWBName - target workbook, the master
    'tgtWSName - what you want to call the worksheet after copying it to the target/master.
    '            If you want this sheetname to be taken from a cell, just pass the cell
    '            reference.  For example, this can be
    '            Workbooks(srcWBName).Sheets(srcWSName).Cells(1,1).Value

    Dim srcWB As Workbook
    Dim srcWS As Worksheet
    Dim tgtWB As Workbook
    Dim tgtWS As Worksheet

    'Create XL objects
    Set srcWB = Workbooks(srcWBName)
    Set srcWS = srcWB.Worksheets(srcWSName)

    Set tgtWB = Workbooks(tgtWBName)
    Set tgtWS = tgtWB.Worksheets(tgtWSName)

    ' Start at the source
    srcWB.Activate
    srcWS.Activate

    ' Copy to target workbook
    srcWS.Copy Before:=tgtWB.Sheets(1) '<~~ copy to beginning of workbook
    ' After copying the worksheet, it is active, so you can rename it now.
    ActiveSheet.Name = tgtWSName


End Sub

. , .