Extracting data from excel workbooks to single excel spreadsheet

[detailstx]

Hello,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I am a complete novice with writing macros and am hoping someone here can help me out.
<o></o>
I have a working folder (named “Tracking”) that contains a “New” folder, “Archived” folder and Master.xlsx workbook. Occasionally, randomly named workbooks will be dropped into the “new” folder. My goal is to get a macro that will extract data from sheet1 of the randomly named xlsx workbooks, and paste it into Master.xlsx workbook, allowing me to accumulate the data over time. Once the data is stored into the Master.xlsx workbook, rename the randomly named workbook (with “_archived” following name) and throw it in the “archived” folder.<o></o>


Master.xlsx is formatted (text and numbers) the same as Sheet1 (rows/columns) of the randomly name workbooks in the “new” folder. Problem is that the data varies in all of the randomly named workbooks (Sheet1), from A1 to dozens of columns and rows.<o></o>


Is this possible or am I dreaming? <o></o>


Appreciate any help.<o></o>

 

[jbeaucaire]

Not dreaming at all. I already have macros that do most of this.

Here's a macro for collecting data from all files in a specific folder.

Workbooks to 1 Sheet​

The parts of the code that need to be edited are colored to draw your attention.


The part that always needs tweaking is the "collect random data" part. If you'll describe the methodology for selecting the data to copy, and the methodology for selecting where to put it in the master, I can help tweak this macro for specific scenario. The rest is mostly done.

 

[jbeaucaire]

What really might help is if you record a macro of you doing all this work on a couple of your files. Turn on the recorder, open a file, copy data into your master, close and move it, repeat with a second file. Then post that code, should be very informative.

 

[detailstx]

I tried recording it and failed. Your macro worked like a charm. Thanks for the help...I threw in a few bucks for your insight.

Enjoy the weekend.

 

[detailstx]

I spoke too soon…got a slight issue.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Your macro worked perfectly, but when I started to create my workbook for importation…it no longer worked properly.
<o></o>
The randomly named workbook has many worksheets but it is the “Data” worksheet that is formatted, within its cells, to extract the data from within its workbook into nicely configured columns and rows. My goal is to use the same theory as the macro you created, but instead of importing all into my Master.xlsx workbook, I’d like to import from the “Data” worksheet and only the values, not complete formulas.
<o></o>
Is that possible? Do I make sense?<o></o>
<o></o>

 

[jbeaucaire]

Yeah, that's always the section that has to be customized. I imagine something like this for that section:

            Set wbData = Workbooks.Open(fPath & fName)  'Open file

        'This is the section to customize, replace with your own action code as needed
            With wbData.Sheets("Data")
                LR = .Range("A" & .Rows.Count).End(xlUp).Row  'Find last row
                If NR = 1 Then                                'copy the data AND titles
                    .Range("A1:A" & LR).EntireRow.Copy
                Else                                          'copy the data only
                    .Range("A2:A" & LR).EntireRow.Copy
                End If
            End With
            .Range("A" & NR).PasteSpecial xlPasteValues       'paste values into master
            
            wbData.Close False                                'close file

 

[detailstx]

Nice going Jerry...I sure do appreciate this.

One last request, and I won't be a problem again: When the data is imported into Master.xlsx sheet, there are numberous blank rows between each set of values from the imports. Is there a way to add a final step shifting all the data up, or deleting blank rows in my Master.xlsx Data worksheet?

 

[jbeaucaire]

Let's assume that any row that is "blank" in column A is blank in the whole row and can be deleted. Add this to the "cleanup" section at the bottom.

ErrorExit:    'Cleanup
    wsMaster.Columns.AutoFit
    wsMaster.Range("A:A").SpecialCells(xlBlanks).EntireRow.Delete xlShiftUp
    Application.DisplayAlerts = True         'turn system alerts back on
    Application.EnableEvents = True          'turn other macros back on
    Application.ScreenUpdating = True        'refreshes the screen
End Sub

 

[detailstx]

Thanks for your help.

 

[md7899]

Hi,

This was really helpful. How would you modify the code if the worksheets you want to copy are all protected (no password)?

Also do the filenames for each file to be copied need to be listed in the code?

Thanks in advance.