Silver
10-15-2015, 12:47 AM
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 -
When the macro is run it should prompt for Sheet Number
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
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
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
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 -
When the macro is run it should prompt for Sheet Number
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
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
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