VBA to copy data from multiple workbooks into master sheet

[excel_vba_1]

Hello Everyone!

I have to copy data from 10+ workbooks and paste it into a master workbook.
All the workbooks are located in a folder on my desktop: C:\Users\xbv\Desktop\group1

All the workbooks contain a sheet named 'appendix B', I have to open each workbook, go to sheet 'appendix B’, select columns range C to F starting from row 6 to row ‘x'(the last row can vary in each workbook), cntrl+v (copy), and paste the data range into master worksheet. In the master worksheet, I paste the data in Columns A to D and continue pasting/appending the data as I copy data from more workbooks. Eventually, the master workbook has the data in columns A to D from every workbook in one sheet.

The columns range C to F and starting from row 6 always remains constant in all the sheets (appendix B ) of every workbook. Each workbook contains 7 sheets, but I am only interested in sheet ‘appendix B’

I have to repeat the same steps for 10-30 workbooks and continue pasting/appending the data into master sheet. So, I was wondering if someone could please help me to create a VBA code for this? I'm really new to VBA and would really appreciate your help!

Please let me know if you require any clarification.

Many thanks! =)

 

[mumps]

@Fatman003
Welcome to the Forum. According to Forum rules, you should not post your question in another member's thread. Please start your own thread. If you find this thread useful, you can post a link to this thread in yours. If you send me a private message with a link to your new thread. I'll be happy to have a look.

 

[Fatman003]

@Fatman003
Welcome to the Forum. According to Forum rules, you should not post your question in another member's thread. Please start your own thread. If you find this thread useful, you can post a link to this thread in yours. If you send me a private message with a link to your new thread. I'll be happy to have a look.

@mumps Thanks for your reply. The reason I posted my question under this thread was because it's very much related. The only difference being that there is a minor change in my formatting. Should I still start a new thread?

 

[nurulainifr]

Hi mumps, i want to ask you about that code. If the code was perfectly okay withno error. But it can't showed if i press the macro button. What should i do? Thank you.

 

[mumps]

Have you placed the macro in a regular module? If not, do the following:
Save the workbook as a macro-enabled file. This will change its extension to "xlsm". Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key.

 

[nurulainifr]

i've done follow your rules.. but the code still can't work. i'm using excel 2016, and I want to Copy one specified sheets of multiple workbooks within a folder into another single workbook. and i want to paste special only values to avoid unnecessary formatting. can you help me? thankyou

 

[mumps]

What is the full path to the folder containing the workbooks? What is the extension (xlsx, xlsm) of those workbooks? Are they the only files in that folder? What is the name of the sheet you want to copy from each workbook?

 

[nurulainifr]

Answe for your question :
1. the full path to the folder containing the workbook is "E:\CPBSA\Database\"
2. the extension of those workbooks were .xlsx for the source workbook and for the destination workbook i changed into xlsm
3. there're only 2 files source workbooks and one destination workbook on the folder
4. the sheet from source workbooks that i want to copy from source workbook is "Sheet1" and the sheet that use to paste on destination work only sheet "database"
hope you find the code answer for helping me thankyou

 

[mumps]

Try:

Sub CopySheet()
    Application.ScreenUpdating = False
    Dim srcWB As Workbook, desWB As Workbook
    Set desWB = ThisWorkbook
    strExtension = Dir("*.xlsx")
    Do While strExtension <> ""
        Set srcWB = Workbooks.Open(strExtension)
        With desWB
            Sheets("Sheet1").Copy after:=.Sheets(.Sheets.Count)
            ActiveSheet.UsedRange.Cells.Value = ActiveSheet.UsedRange.Cells.Value
        End With
        srcWB.Close False
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[nurulainifr]

thankyou mumps

thankyou mumps, the code wasn't error but there's a notice like this. And the notice tell me like down below. Thankyou

 

[mumps]

Macros must be enabled in Excel before they will run. Follow the instructions in the blue link in the screen shot you posted to enable macros.