I need a macro that can extract data
Closed
excelmwangi
Posts
16
Registration date
Friday February 6, 2015
Status
Member
Last seen
June 5, 2015
-
Feb 7, 2015 at 06:47 PM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Mar 26, 2015 at 09:46 PM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Mar 26, 2015 at 09:46 PM
Related:
- Macro to extract specific data from excel
- Transfer data from one excel worksheet to another automatically - Guide
- Excel conditional formatting if another cell contains specific text ✓ - Excel Forum
- Spell number in excel without macro - Guide
- Macro excel download - Download - Spreadsheets
- Excel conditional formatting if cell contains specific text - Excel Forum
4 responses
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Feb 8, 2015 at 06:26 AM
Feb 8, 2015 at 06:26 AM
Hello excelmwangi,
The easiest way to do this would be to add a selection criteria to your dataset. So, in the following code, I have added an asterisk "*" in Column A (for each sheet) as the criteria.
With this code, whichever row you place an asterisk by (ensure this is in Column A), in whichever sheet, the row of data will be copied to the "Master" sheet. The criteria is left behind.
You can have a look at my Test work book here:-
https://www.dropbox.com/s/dfy344w9u1w0509/Excelmwangi.xlsm?dl=0
to see if it works for you.
We can add an additional row of code if you want the transferred data cleared from the worksheets supplying the "Master" sheet. As the code is now, it clears the "Master" sheet prior to the data transfer to prevent duplicates while the data remains in each of the supplying sheets.
Kind regards,
vcoolio.
The easiest way to do this would be to add a selection criteria to your dataset. So, in the following code, I have added an asterisk "*" in Column A (for each sheet) as the criteria.
Sub CopyData() Dim ws As Worksheet Dim lRow As Long Dim lCol As Integer Application.ScreenUpdating = False For Each ws In Worksheets If ws.Name = "Master" Then GoTo NextSheet ws.Select lRow = Range("A" & Rows.Count).End(xlUp).Row lCol = Cells(1, Columns.Count).End(xlToLeft).Column For Each cell In Range("A2:A" & lRow) If cell.Value = "*" Then Range(Cells(cell.Row, "A"), Cells(cell.Row, lCol)).Copy Sheets("Master").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues End If Next cell NextSheet: Next ws Sheets("Master").Range("A1:A" & Rows.Count).ClearContents Sheets("Master").Select Application.ScreenUpdating = True Application.CutCopyMode = False MsgBox "Data transfer complete", vbExclamation End Sub
With this code, whichever row you place an asterisk by (ensure this is in Column A), in whichever sheet, the row of data will be copied to the "Master" sheet. The criteria is left behind.
You can have a look at my Test work book here:-
https://www.dropbox.com/s/dfy344w9u1w0509/Excelmwangi.xlsm?dl=0
to see if it works for you.
We can add an additional row of code if you want the transferred data cleared from the worksheets supplying the "Master" sheet. As the code is now, it clears the "Master" sheet prior to the data transfer to prevent duplicates while the data remains in each of the supplying sheets.
Kind regards,
vcoolio.
Feb 9, 2015 at 12:55 PM
Feb 9, 2015 at 01:03 PM
Feb 9, 2015 at 01:35 PM
https://www.dropbox.com/s/azbbvcnp4e8flsp/Planning%20Worksheet%20Western%20Vicariate%20-%20Copy.xlsm?dl=0