Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting records from an excel list
I have a large spreadsheet with at least 6000 records (rows) and 5 columns.
The columns are as follows: B5 C5 D5 E5 F5 Check# Vendor# Name Date Amount From that first sheet, I need to create separate worksheets that would retrieve all the records (from column B to column F) belonging to a vendor (NAME: D5) For example, I would highlight one of the cells containing the vendor name, launch the macro and it would create the worksheet, give it the vendor's name then list all the records on the worksheet. Any help with this project will be gratefully appreciated. Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting records from an excel list
Hey Leon,
Try this, I beleve this is what your looking for. Select the Vendor Name in Column "D" and run this macro. I use a command button. Hope this helps. Rick , (Fbks, Ak) Option Explicit Sub NewNameSheet() Dim main As Worksheet, wksNew As Worksheet Dim lrc As Long Dim Name As String Dim ishCt As Integer '' sheet count Dim lnewRc As Long '' row postion on new sheet Set main = Worksheets("sheet1") '' main sheet lrc = 6 '' start row Name = ActiveCell.Value ishCt = Sheets.Count '' get number of worksheets Worksheets.Add after:=Sheets(ishCt) '' add new sheet Worksheets.Item(ishCt + 1).Name = Name '' rename sheet Set wksNew = Worksheets(Name) main.Range("B5:F5").Copy wksNew.Range("B5:F5") '' copy header lnewRc = 6 '' beginning row for records '' search for Name of vendor, then Copy data to new sheet Do Until IsEmpty(main.Cells(lrc, "D")) If main.Cells(lrc, "D") = Name Then '' Copy Vendor Data, & paste to new sheet main.Range("B" & lrc & ":F" & lrc).Copy wksNew.Range("B" & lnewRc & ":F" & lnewRc) lnewRc = lnewRc + 1 End If lrc = lrc + 1 '' next row to search Loop End Sub "Leon" wrote in message ... I have a large spreadsheet with at least 6000 records (rows) and 5 columns. The columns are as follows: B5 C5 D5 E5 F5 Check# Vendor# Name Date Amount From that first sheet, I need to create separate worksheets that would retrieve all the records (from column B to column F) belonging to a vendor (NAME: D5) For example, I would highlight one of the cells containing the vendor name, launch the macro and it would create the worksheet, give it the vendor's name then list all the records on the worksheet. Any help with this project will be gratefully appreciated. Thanks in advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting records from an excel list
"Rick Hansen" wrote: Hey Leon, Try this, I beleve this is what your looking for. Select the Vendor Name in Column "D" and run this macro. I use a command button. Hope this helps. Rick , (Fbks, Ak) Option Explicit Sub NewNameSheet() Dim main As Worksheet, wksNew As Worksheet Dim lrc As Long Dim Name As String Dim ishCt As Integer '' sheet count Dim lnewRc As Long '' row postion on new sheet Set main = Worksheets("sheet1") '' main sheet lrc = 6 '' start row Name = ActiveCell.Value ishCt = Sheets.Count '' get number of worksheets Worksheets.Add after:=Sheets(ishCt) '' add new sheet Worksheets.Item(ishCt + 1).Name = Name '' rename sheet Set wksNew = Worksheets(Name) main.Range("B5:F5").Copy wksNew.Range("B5:F5") '' copy header lnewRc = 6 '' beginning row for records '' search for Name of vendor, then Copy data to new sheet Do Until IsEmpty(main.Cells(lrc, "D")) If main.Cells(lrc, "D") = Name Then '' Copy Vendor Data, & paste to new sheet main.Range("B" & lrc & ":F" & lrc).Copy wksNew.Range("B" & lnewRc & ":F" & lnewRc) lnewRc = lnewRc + 1 End If lrc = lrc + 1 '' next row to search Loop End Sub "Leon" wrote in message ... I have a large spreadsheet with at least 6000 records (rows) and 5 columns. The columns are as follows: B5 C5 D5 E5 F5 Check# Vendor# Name Date Amount From that first sheet, I need to create separate worksheets that would retrieve all the records (from column B to column F) belonging to a vendor (NAME: D5) For example, I would highlight one of the cells containing the vendor name, launch the macro and it would create the worksheet, give it the vendor's name then list all the records on the worksheet. Any help with this project will be gratefully appreciated. Thanks in advance Hi Rick. I'll try this right away and keep you posted Thanks anyway. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting records from an excel list
"Rick Hansen" wrote: Hey Leon, Try this, I beleve this is what your looking for. Select the Vendor Name in Column "D" and run this macro. I use a command button. Hope this helps. Rick , (Fbks, Ak) Option Explicit Sub NewNameSheet() Dim main As Worksheet, wksNew As Worksheet Dim lrc As Long Dim Name As String Dim ishCt As Integer '' sheet count Dim lnewRc As Long '' row postion on new sheet Set main = Worksheets("sheet1") '' main sheet lrc = 6 '' start row Name = ActiveCell.Value ishCt = Sheets.Count '' get number of worksheets Worksheets.Add after:=Sheets(ishCt) '' add new sheet Worksheets.Item(ishCt + 1).Name = Name '' rename sheet Set wksNew = Worksheets(Name) main.Range("B5:F5").Copy wksNew.Range("B5:F5") '' copy header lnewRc = 6 '' beginning row for records '' search for Name of vendor, then Copy data to new sheet Do Until IsEmpty(main.Cells(lrc, "D")) If main.Cells(lrc, "D") = Name Then '' Copy Vendor Data, & paste to new sheet main.Range("B" & lrc & ":F" & lrc).Copy wksNew.Range("B" & lnewRc & ":F" & lnewRc) lnewRc = lnewRc + 1 End If lrc = lrc + 1 '' next row to search Loop End Sub "Leon" wrote in message ... I have a large spreadsheet with at least 6000 records (rows) and 5 columns. The columns are as follows: B5 C5 D5 E5 F5 Check# Vendor# Name Date Amount From that first sheet, I need to create separate worksheets that would retrieve all the records (from column B to column F) belonging to a vendor (NAME: D5) For example, I would highlight one of the cells containing the vendor name, launch the macro and it would create the worksheet, give it the vendor's name then list all the records on the worksheet. Any help with this project will be gratefully appreciated. Thanks in advance Rick, I get a "subscript out of range" on this line: Set main = Worksheets("sheet1") '' main sheet ?? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting records from an excel list
It worked !
Thanks a lot. By the way, I should have known to change "sheets1" into the name of my source worksheet. Thanks anyway I really appreciate your help "Rick Hansen" wrote: Hey Leon, Replace "Sheet1" with the name of your spreadsheet name that has all of yours records ons. Also don't have any blanks rows between your first and last record on your main spreadsheet. If you do it'll stop the search for names. The "Isempty function is look for the empty cell after the last record. If you would like I'll email you my test spreadsheet program, here is my email address Let me know if you need anymore help, Good Luck Rick "Leon" wrote in message ... "Rick Hansen" wrote: Hey Leon, Try this, I beleve this is what your looking for. Select the Vendor Name in Column "D" and run this macro. I use a command button. Hope this helps. Rick , (Fbks, Ak) Option Explicit Sub NewNameSheet() Dim main As Worksheet, wksNew As Worksheet Dim lrc As Long Dim Name As String Dim ishCt As Integer '' sheet count Dim lnewRc As Long '' row postion on new sheet Set main = Worksheets("sheet1") '' main sheet lrc = 6 '' start row Name = ActiveCell.Value ishCt = Sheets.Count '' get number of worksheets Worksheets.Add after:=Sheets(ishCt) '' add new sheet Worksheets.Item(ishCt + 1).Name = Name '' rename sheet Set wksNew = Worksheets(Name) main.Range("B5:F5").Copy wksNew.Range("B5:F5") '' copy header lnewRc = 6 '' beginning row for records '' search for Name of vendor, then Copy data to new sheet Do Until IsEmpty(main.Cells(lrc, "D")) If main.Cells(lrc, "D") = Name Then '' Copy Vendor Data, & paste to new sheet main.Range("B" & lrc & ":F" & lrc).Copy wksNew.Range("B" & lnewRc & ":F" & lnewRc) lnewRc = lnewRc + 1 End If lrc = lrc + 1 '' next row to search Loop End Sub "Leon" wrote in message ... I have a large spreadsheet with at least 6000 records (rows) and 5 columns. The columns are as follows: B5 C5 D5 E5 F5 Check# Vendor# Name Date Amount From that first sheet, I need to create separate worksheets that would retrieve all the records (from column B to column F) belonging to a vendor (NAME: D5) For example, I would highlight one of the cells containing the vendor name, launch the macro and it would create the worksheet, give it the vendor's name then list all the records on the worksheet. Any help with this project will be gratefully appreciated. Thanks in advance Rick, I get a "subscript out of range" on this line: Set main = Worksheets("sheet1") '' main sheet ?? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting records from an excel list
I'm glad I could help....
Rick "Leon" wrote in message ... It worked ! Thanks a lot. By the way, I should have known to change "sheets1" into the name of my source worksheet. Thanks anyway I really appreciate your help "Rick Hansen" wrote: Hey Leon, Replace "Sheet1" with the name of your spreadsheet name that has all of yours records ons. Also don't have any blanks rows between your first and last record on your main spreadsheet. If you do it'll stop the search for names. The "Isempty function is look for the empty cell after the last record. If you would like I'll email you my test spreadsheet program, here is my email address Let me know if you need anymore help, Good Luck Rick "Leon" wrote in message ... "Rick Hansen" wrote: Hey Leon, Try this, I beleve this is what your looking for. Select the Vendor Name in Column "D" and run this macro. I use a command button. Hope this helps. Rick , (Fbks, Ak) Option Explicit Sub NewNameSheet() Dim main As Worksheet, wksNew As Worksheet Dim lrc As Long Dim Name As String Dim ishCt As Integer '' sheet count Dim lnewRc As Long '' row postion on new sheet Set main = Worksheets("sheet1") '' main sheet lrc = 6 '' start row Name = ActiveCell.Value ishCt = Sheets.Count '' get number of worksheets Worksheets.Add after:=Sheets(ishCt) '' add new sheet Worksheets.Item(ishCt + 1).Name = Name '' rename sheet Set wksNew = Worksheets(Name) main.Range("B5:F5").Copy wksNew.Range("B5:F5") '' copy header lnewRc = 6 '' beginning row for records '' search for Name of vendor, then Copy data to new sheet Do Until IsEmpty(main.Cells(lrc, "D")) If main.Cells(lrc, "D") = Name Then '' Copy Vendor Data, & paste to new sheet main.Range("B" & lrc & ":F" & lrc).Copy wksNew.Range("B" & lnewRc & ":F" & lnewRc) lnewRc = lnewRc + 1 End If lrc = lrc + 1 '' next row to search Loop End Sub "Leon" wrote in message ... I have a large spreadsheet with at least 6000 records (rows) and 5 columns. The columns are as follows: B5 C5 D5 E5 F5 Check# Vendor# Name Date Amount From that first sheet, I need to create separate worksheets that would retrieve all the records (from column B to column F) belonging to a vendor (NAME: D5) For example, I would highlight one of the cells containing the vendor name, launch the macro and it would create the worksheet, give it the vendor's name then list all the records on the worksheet. Any help with this project will be gratefully appreciated. Thanks in advance Rick, I get a "subscript out of range" on this line: Set main = Worksheets("sheet1") '' main sheet ?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting records via SQL in Excel | Excel Discussion (Misc queries) | |||
extracting unique records | Excel Discussion (Misc queries) | |||
Extracting selected records from longer list | Excel Discussion (Misc queries) | |||
Extracting the Duplicate Records | Excel Discussion (Misc queries) | |||
Extracting Records From Excel Database | Excel Discussion (Misc queries) |