PDA

View Full Version : Excel Macro Copying Information From Many Folders



CRzis
06-30-2019, 09:19 AM
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
06-30-2019, 12:10 PM
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
06-30-2019, 02:42 PM
OMG you are a lifesaver Jolivanes. Thank you <3

jolivanes
06-30-2019, 05:21 PM
Thank you for the kind words and for letting us know that it worked to your satisfaction.

CRzis
07-01-2019, 08:16 AM
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
07-01-2019, 08:27 AM
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
07-01-2019, 10:42 AM
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
07-01-2019, 11:00 AM
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
07-01-2019, 12:08 PM
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
07-01-2019, 12:20 PM
Yes! Thank you <3