Copy data from 1 workbook to another based on sheet tabs [Solved/Closed]

bimmy80 9 Posts Saturday October 17, 2015Registration date November 19, 2015 Last seen - Oct 18, 2015 at 03:53 AM - Latest reply: bimmy80 9 Posts Saturday October 17, 2015Registration date November 19, 2015 Last seen
- Oct 23, 2015 at 10:11 PM
Good day to all,

I'm looking for a macro that can copy data from source workbook and paste it to master workbook based on specified sheet tabs.

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.

What the macro should do -

1) When the macro is run a message 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.
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 have tried to briefly explain what exactly I'm looking for. I will be grateful if anyone can help me out.
See more 

8 replies

rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Oct 20, 2015 at 04:24 PM
0
Thank you
do you have any thing done yet ?
bimmy80 9 Posts Saturday October 17, 2015Registration date November 19, 2015 Last seen - Oct 21, 2015 at 01:50 AM
0
Thank you
Hello,

I came across below code

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


What the code does is open up the master workbook, paste the data in one particular range and close the master workbook.

I'm looking for few additions to the above code, which I have mentioned above under - what the macro should do.

Any assistance will be greatly appreciated.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Oct 21, 2015 at 11:25 AM
Here is some thing build on top on your code

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

'getting the target sheet name.
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
msgbbox "No Name entered"
End 'terminate
End If

Set wbkWorkbook2 = Workbooks.Open(strPath2)

'check if cell A1 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("A1") <> "") Then
MsgBox "Data Already exists"
End
End If

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

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

'close the workbook
wbkWorkbook2.Close (True)

End Sub
bimmy80 9 Posts Saturday October 17, 2015Registration date November 19, 2015 Last seen - Oct 21, 2015 at 02:36 PM
0
Thank you
Greetings rizvisa1,

First of all a big thank you for taking time to come up with the code.

I request your assistance with regards to below 2 points -

1) If data exists macro should prompt for a message box stating "data exists insert new sheet" and then opens the sheet specified and paste data in respective ranges.

2)Above code does not open specified sheet. I want macro to open the specified sheet and then paste the data

Note :
(if this makes any sense)
Point 1 is applicable when data already exists and point 2 is applicable when there are no data.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Oct 21, 2015 at 08:56 PM
If data exists, still paste ? I think you meant to say if data exists show a message. There is a message that shows up, if there is data in cell a1 of the target sheet. (read the comments and make adjustment as necessary)

for have sheet open, i think u meant that target sheet should be activate
in that case, activate the workbook, and select the sheet via code.
bimmy80 9 Posts Saturday October 17, 2015Registration date November 19, 2015 Last seen - Oct 22, 2015 at 02:29 AM
0
Thank you
Greetings rizvisa1,

Let me explain.

Currently what the code does is if data exists in cell a1 then it shows the message, which is fine.

After the message is shown the code should display a message box to input new sheet.

It will then again check for data in the specified sheet in cell a1.

Macro will continue to do this (I think its called a continuous loop) until it does not come across any data in the specified sheet in cell a1.

I humbly request for your patience in getting this part correct.

Below are 3 examples which will help you in understanding how the macro should work

1st example :

When the macro is run it asks for sheet name, i insert 2, macro pastes data in 2.

2nd example :

When the macro is run it asks for sheet name, i insert 2, message appears data exist, input box appears to insert new sheet, i insert 3, macro pastes data in 3.

3rd example (continuous loop) :

When the macro is run it asks for sheet name, i insert 2, message appears data exist, input box appears to insert new sheet, i insert 3, message appears data exist, input box appears to insert new sheet, i insert 4, macro pastes data in 4.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Oct 22, 2015 at 07:23 AM
here is with your loop

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 A1 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("A1") <> "") 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:C8").Value = _
wbkWorkbook1.Worksheets("Sheet1").Range("A1:C8").Value

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

'close the workbook
wbkWorkbook2.Close (True)

End Sub

bimmy80 9 Posts Saturday October 17, 2015Registration date November 19, 2015 Last seen - Oct 23, 2015 at 10:11 PM
0
Thank you
Hi rizvisa1,

Perfect.

Appreciate you for giving your time and patience in solving my query.

Thanks a tonne.