Macro to prompt which sheet to open from multiple sheets and then paste data

[Bimmy]

Hello,

I have a code which basically opens specified worksheet in master workbook and copies data into specific ranges in master workbook.

Sub TransferDataV2()
'transfer stuff from this workbook to workbook 2

Dim strPath2 As String
Dim wbkWorkbook1 As Workbook
Dim wbkWorkbook2 As Workbook
Dim strTargetSheet As String

    'define paths and filenames
    strPath2 = "D:\Master.xlsx"

    'open files
    Set wbkWorkbook1 = ThisWorkbook
    Set wbkWorkbook2 = Workbooks.Open(strPath2)

    'getting the target sheet name.
    Do
        strTargetSheet = InputBox("Enter the target sheet name", "Target Sheet ...")
        strTargetSheet = Trim(strTargetSheet)    'remove any leading and trailing spaces
        If (strTargetSheet = vbNullString) Then    'check if there is no data
            MsgBox "No Name entered"
            End    'terminate
        End If

        'check if cell D2 has any data on target sheet. Hopefully you have at least one cell that would be
        'always populated. With this, it is checking is A1 is not null
        If (wbkWorkbook2.Worksheets(strTargetSheet).Range("D2") <> "") Then
            MsgBox "Data Already exists"
            strTargetSheet = vbNullString ' remove the name
        End If
    Loop Until (strTargetSheet <> vbNullString) 'be in a loop till sheet name is not ""

    wbkWorkbook2.Worksheets(strTargetSheet).Range("A1:B4").Value = _
    wbkWorkbook1.Worksheets("Sheet1").Range("A1:b4").Value

    wbkWorkbook2.Worksheets(strTargetSheet).Range("D1:N1500").Value = _
    wbkWorkbook1.Worksheets("Sheet1").Range("D1:N1500").Value

    'close the workbook
    wbkWorkbook2.Close (True)

End Sub

Few additional info -

1) Source workbook is where data is collated and from where the code is run
2) Master workbook contains 50 sheets and where the data is pasted as per the sheets specified.
3) Both workbooks are maintained in different folders
4) Ranges from where the data is copy pasted are the same -
RangeA1:B4 and RangeD1:N1500
5) Sheets in Master workbook are named as 1,2,3 and so on

How the code works -

When the code is run it asks for sheet number. I insert sheet number as 1, code checks if there are any data in Cell D2 in sheet number 1 of master workbook. If there are no data then code paste data from source to master workbook in sheet 1 in ranges specified above.

However, if data exists code prompts " data exist" and input box appears "insert new sheet number". The code will perform this particular task until it does not come across any data in Cell D2 (Continuous Loop) and then paste the data.

Below are the additions I'm looking for -

1) Can the code look for data in RangeD2:N1500 rather than in Cell D2
2) This is the tricky part - When the code is run from second time onwards can it tell the user which sheet to update in master workbook.
Ex : I run the macro and update sheet 1 in master workbook. When the second user runs the macro it should prompt to update sheet 2 so on and so forth until sheet 50.

I have briefly explained what exactly I'm looking. Any assistance will be appreciated.

Have attached source workbook with macro and dummy data

 

[vletm]

Tricky to test without 'Master'.
I added some code and 'hide some Your code.
Could You test and report how do this idea work?
Okay?

 

[Bimmy]

Hi vletm,

It works perfectly.

Need just 1 tiny adjustment. Let me explain -

Currently what happens is after the code is run and when the master workbook is opened it always opens on sheet 1 rather than the sheet on which the data is currently updated. Can the macro open the sheet specified, paste and then close the master workbook. This way when master workbook is opened manually it opens on sheet which was currently saved.

Ex - When macro is run I insert sheet 12, macro opens sheet 12 from master workbook paste the data and close the workbook.

Have attached master workbook

 

[vletm]

I made space for modification.
I added lines 47-50.
... for Your testing

 

[Bimmy]

Hi vletm,

Exactly what I'm looking for.

Thank you very much. Appreciate for your patience and taking time in helping me out.

Keep on Rockin...