Related:
- Exporting data
- How to export data from excel to excel - How-To - Excel
- MySQL - Importing and exporting data - How-To - MySQL
- How to export data from one sheet to another in excel - How-To - Excel
- Userform to export data to another sheet ✓ - Forum - Excel
- How to export data from database to excel sheet in asp.net c# - Forum - Programming
4 replies
vcoolio
- Posts
- 1316
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- August 21, 2021
Hello Chidazy,
I think I follow what you would like to do so the following VBA code, placed in a standard module, should do the trick for you:-
Following is the link to my test work book (based on the sample that you supplied):-
https://www.dropbox.com/s/gvduypwwumhr5dr/Chidazy%28multi%20shts%20to%20master%20sht%29.xlsm?dl=0
You'll see in the sample that I have created a "Main" sheet (sheet3) where all the required data will be consolidated.
The code filters Column G in all sheets (except the "Main" sheet) for the values 365 or 366 and transfer the relevant rows of data to the "Main" sheet. The "Main" sheet is refreshed each time that the code is executed so that you will not have duplication.
Click on the "RUN" button to see it work.
In all the source sheets, you will need to format Column G to "General" as at present there is a variety of formats which could interfere with the proper execution of the code.
I hope that this helps.
Cheerio,
vcoolio.
I think I follow what you would like to do so the following VBA code, placed in a standard module, should do the trick for you:-
Sub Transfer()
Dim ws As Worksheet
Application.ScreenUpdating = False
Sheet3.UsedRange.Offset(2).ClearContents
For Each ws In Worksheets
If ws.Name <> "Main" Then
With ws.Range("G5", ws.Range("G" & ws.Rows.Count).End(xlUp))
.AutoFilter 1, 365, xlOr, 366
On Error Resume Next
.Offset(1).EntireRow.Copy
Sheet3.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
Sheet3.Columns.AutoFit
Sheet3.Range("A1").Select
End With
ws.AutoFilterMode = False
End If
Next ws
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Following is the link to my test work book (based on the sample that you supplied):-
https://www.dropbox.com/s/gvduypwwumhr5dr/Chidazy%28multi%20shts%20to%20master%20sht%29.xlsm?dl=0
You'll see in the sample that I have created a "Main" sheet (sheet3) where all the required data will be consolidated.
The code filters Column G in all sheets (except the "Main" sheet) for the values 365 or 366 and transfer the relevant rows of data to the "Main" sheet. The "Main" sheet is refreshed each time that the code is executed so that you will not have duplication.
Click on the "RUN" button to see it work.
In all the source sheets, you will need to format Column G to "General" as at present there is a variety of formats which could interfere with the proper execution of the code.
I hope that this helps.
Cheerio,
vcoolio.
vcoolio
- Posts
- 1316
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- August 21, 2021
Hello Chidazy,
Yes, please upload a sample of your work book to a free file sharing site such as DropBbox, ge.tt or SpeedyShare then post the link to your file back here. Please use dummy data.
We can then try to sort it out for you. Please include any coding or formulae that you may have tried.
Thank you.
Cheerio,
vcoolio.
Yes, please upload a sample of your work book to a free file sharing site such as DropBbox, ge.tt or SpeedyShare then post the link to your file back here. Please use dummy data.
We can then try to sort it out for you. Please include any coding or formulae that you may have tried.
Thank you.
Cheerio,
vcoolio.
I haven't stopped crying for joy. You are amazing. Thank u sooooooooooooooooooo much. This is unbelievable, Thanks, thanks and thanks again.!!!!!! Yeepeeeeeeeeeeeeeeeeeeeeeeee