Excel Macro Copying Information From Many Folders

[CRzis]

Hello all! I know very little about VBA, and while I'm trying to learn, I currently have a problem that's above my current ability....

I have a folder on my desktop with a bunch of different workbooks which are all the same format, but with different information. I'm needing to create a macro which will help me create a master list which opens each workbook in my folder, copies the information from Sheet1, range A2:Q2, and then copies it to the bottom of my master list in the range of Ax:Qx. Ideally, I'd love this to only copy the data from files I haven't yet copied over, but I have no idea if that's possible....

Thank you!

[jolivanes]

Copy this code into your "Master", save the "Master" in the same Folder where all the other Workbooks are saved and run this code.
It will copy from all Workbooks, except the Workbook with the code in it, so if you copied some data previously you can delete it if you want.

Sub Copy_Data_Of_First_Sheets_Only()
    Dim wb As String


    Application.ScreenUpdating = False


    wb = Dir(ThisWorkbook.Path & "\*")
    Do Until wb = ""
        If wb <> ThisWorkbook.Name Then
            Workbooks.Open ThisWorkbook.Path & "\" & wb
            With Workbooks(wb).Sheets("Sheet1")
                .Range("A2:Q2").Copy ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
            End With
            Workbooks(wb).Close False
        End If
        wb = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

[CRzis]

OMG you are a lifesaver Jolivanes. Thank you <3

[jolivanes]

Thank you for the kind words and for letting us know that it worked to your satisfaction.

[CRzis]

Hello! While the code was working, now that I have time to work with I'm noticing an error in which the information being copied is being offset for each entry. For example, if A2 was originally the persons name, B2 their address, and C2 their phone number, while it all works for the first entry, on the second entry it will say their address in their name, and then for the third entry, it will say their phone number is there name. Is there a way to fix this error?

[jolivanes]

The layout of the sheets you're copying from must be different. Did you have a look at that?
All the code does is copy A2:Q2 and paste it in the first free cell in Column A

[tanmay2501]

Can anyone help me out with XLSX to XML macro that converts the sheet 1 data to XML file
For eg :-
File contains
Col 1 Col 2
Row 1 School\ Name School\City
Row 2 Xavier. Delhi

XML file after conversion


<School>
<Name>Xavier</Name>
<City>Delhi>/City>
<\School>


I would be very very greatful to you

[CRzis]

Hi Jolivanes,
I appreciate your help so much.
The layout of the sheets are all identical, I think I've figured out the problem, which is that it's copying the reference addresses that I got the first sheets from, rather than just the text content. Is there a way to change the code to be a special paste so it pastes the literal text in the box, rather than the reference equation?

Thank you!

[jolivanes]

If you change this

.Range("A2:Q2").Copy ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1)

to this

ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1),Resize(, 17).Value = .Range("A2:Q2").Value

is that better?

@tanmay2501
Please start your own thread.

[CRzis]

Yes! Thank you <3