Previous Topic Next Topic

Copy data from 1 workbook to another based on sheet tabs

Solved/Closed
bimmy80 Posts 6 Registration date Saturday October 17, 2015 Status Member Last seen November 19, 2015 - Oct 18, 2015 at 03:53 AM
bimmy80 Posts 6 Registration date Saturday October 17, 2015 Status Member Last seen November 19, 2015 - 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.
Related:

5 responses

Answer 1 / 5
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Oct 20, 2015 at 04:24 PM
do you have any thing done yet ?
0
Answer 2 / 5
bimmy80 Posts 6 Registration date Saturday October 17, 2015 Status Member Last seen November 19, 2015
Oct 21, 2015 at 01:50 AM
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.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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
0
Answer 3 / 5
bimmy80 Posts 6 Registration date Saturday October 17, 2015 Status Member Last seen November 19, 2015
Oct 21, 2015 at 02:36 PM
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.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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.
0
Answer 4 / 5
bimmy80 Posts 6 Registration date Saturday October 17, 2015 Status Member Last seen November 19, 2015
Oct 22, 2015 at 02:29 AM
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.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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

0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 5
bimmy80 Posts 6 Registration date Saturday October 17, 2015 Status Member Last seen November 19, 2015
Oct 23, 2015 at 10:11 PM
Hi rizvisa1,

Perfect.

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

Thanks a tonne.
0

Similar discussions

Unlock safe folder without losing any data
Mintu -
HelpiOS -

1 response