Macro to Copy Paste Data from Source WB to Master WB in different folder sheet wise

[Silver]

Hi,

Below is a code that pastes data from Source workbook to Master workbook (both stored in different folders)

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


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


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


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


    
    
    wbkWorkbook2.Worksheets("Sheet1").Range("A1:B3").Value = _
        wbkWorkbook1.Worksheets("Sheet1").Range("A1:B3").Value


    'close the workbook
    wbkWorkbook2.Close (True)


End Sub

Above code opens the Master workbook, pastes the data and closes it again.

Short explanation about what I'm looking for

Master workbook has 40 worksheets and I want the macro to paste data from Source workbook to Master workbook sheet wise.

Below is what the Macro should do -

1. When the macro is run it should prompt for Sheet Number
2. After the Sheet Number is entered it should check if data already exists in the respective sheet in Master workbook, Range is A2:Q2000. If data exists macro should prompt - Data exists enter new sheet
3. If no data exists then macro should copy data from range A2:Q2000 of Source workbook and paste it to respective sheet of Master workbook
4. Since Source workbook contains Data and Formula, macro should paste the data as it is (No paste special)

Have attached Source sheet with the macro

[Silver]

Hello All,

Basically what I'm looking for is copy data from one workbook to another in specific worksheet.

Few things to note -

1) Source and master workbook are maintained in different folders.
2) Source workbook is where the data is collated and from where the macro will be run.
3) Master workbook is where the data will be pasted. It contains more than 40 sheets and will be named as 1,2,3 and so on.
4) Ranges to copy and paste are the same - Range A1:C8 and E1:N1500.
(I have changed ranges from the original post)

Since the ranges have changed I would revise my points again

Looking for additions to the above code as below -

1) When the macro is run an input box should ask for worksheet name.
2) After the sheet number is entered, it should check if data already exists in the respective sheet in the master workbook, in ranges specified above. If data exists macro should prompt - Data exists enter new sheet.
3) If no data exists then macro should copy data from source workbook and paste it to respective sheet of master workbook in ranges specified above.
4) Since source workbook contains blank cells, formulas and formats macro should copy and paste the data as it is (No paste special).
5) Option should be provided within the macro to change path of both workbooks.
6) After the data is pasted macro should open the master workbook. Option should be provided to add or delete this part of the code.

I will be grateful if anyone can help me out.