Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Advance Filter Reference in Source Sheet
Hi Wise Ones !,
My customer has very specific viewing requirements of a report that is provided me in a CSV format. I have a macro that converts information into required viewing format 1) Open XLS workbook 2) Copy CSV sheet 3) Paste sheet 1 XLS workbook 4) Advance filter into sheet 2 into correct rows specified by customer 5) misc other adjustments made with macro to suite customer It works fine as long as the source range of advanced filter is fixed. Unfortunetly this isn't always case. The rows & columns of source data change regularly I believe problem could be fixed if I could A) pinpoint the location of the "Header Words" at the start of the macro, B) Reference location sheet two ( A1= 'Sheet 1'! + Location found ) C) Then run advance filter But I don't know how to find the reference row and location of the header words. Can someone help? Thanks for any assistance ! Steven |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Advance Filter Reference in Source Sheet
Usually these problems are solved by formating the worksheet before the
importing is donw. For example make format the entire worksheet as textt before you import. Press the gray button between the Column A and Row 1 (entire worksheet is highlighted). Then go to format menu and try diffferent format before you import the file. Probably format as text wil solve the problem (the default is general). I usually skip the standard importing and write my own filters. I open the file as text and then write code the reads the textt file and writes to the spreadsheet. These macro are somtimes very simply and others can be very complex. A simple one I wrote this week only red lines that had the word "Active" in the line. I then ran in VBA TextotColumns which put the data in the correct columns. "SteveT" wrote: Hi Wise Ones !, My customer has very specific viewing requirements of a report that is provided me in a CSV format. I have a macro that converts information into required viewing format 1) Open XLS workbook 2) Copy CSV sheet 3) Paste sheet 1 XLS workbook 4) Advance filter into sheet 2 into correct rows specified by customer 5) misc other adjustments made with macro to suite customer It works fine as long as the source range of advanced filter is fixed. Unfortunetly this isn't always case. The rows & columns of source data change regularly I believe problem could be fixed if I could A) pinpoint the location of the "Header Words" at the start of the macro, B) Reference location sheet two ( A1= 'Sheet 1'! + Location found ) C) Then run advance filter But I don't know how to find the reference row and location of the header words. Can someone help? Thanks for any assistance ! Steven |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Advance Filter Reference in Source Sheet
Thanks Joel for your response .
I won't be doing the coversion and trying to apply macro for user friendlyness appreciate what said below though. Brgds, Steven "Joel" wrote: Usually these problems are solved by formating the worksheet before the importing is donw. For example make format the entire worksheet as textt before you import. Press the gray button between the Column A and Row 1 (entire worksheet is highlighted). Then go to format menu and try diffferent format before you import the file. Probably format as text wil solve the problem (the default is general). I usually skip the standard importing and write my own filters. I open the file as text and then write code the reads the textt file and writes to the spreadsheet. These macro are somtimes very simply and others can be very complex. A simple one I wrote this week only red lines that had the word "Active" in the line. I then ran in VBA TextotColumns which put the data in the correct columns. "SteveT" wrote: Hi Wise Ones !, My customer has very specific viewing requirements of a report that is provided me in a CSV format. I have a macro that converts information into required viewing format 1) Open XLS workbook 2) Copy CSV sheet 3) Paste sheet 1 XLS workbook 4) Advance filter into sheet 2 into correct rows specified by customer 5) misc other adjustments made with macro to suite customer It works fine as long as the source range of advanced filter is fixed. Unfortunetly this isn't always case. The rows & columns of source data change regularly I believe problem could be fixed if I could A) pinpoint the location of the "Header Words" at the start of the macro, B) Reference location sheet two ( A1= 'Sheet 1'! + Location found ) C) Then run advance filter But I don't know how to find the reference row and location of the header words. Can someone help? Thanks for any assistance ! Steven |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Advance Filter Reference in Source Sheet
To Clarrify,
I'm looking for a function ( macro ) that will assist with locating the header row and the columns containing the headers ( some times row is inserted between ) Thanks, Steven "SteveT" wrote: Hi Wise Ones !, My customer has very specific viewing requirements of a report that is provided me in a CSV format. I have a macro that converts information into required viewing format 1) Open XLS workbook 2) Copy CSV sheet 3) Paste sheet 1 XLS workbook 4) Advance filter into sheet 2 into correct rows specified by customer 5) misc other adjustments made with macro to suite customer It works fine as long as the source range of advanced filter is fixed. Unfortunetly this isn't always case. The rows & columns of source data change regularly I believe problem could be fixed if I could A) pinpoint the location of the "Header Words" at the start of the macro, B) Reference location sheet two ( A1= 'Sheet 1'! + Location found ) C) Then run advance filter But I don't know how to find the reference row and location of the header words. Can someone help? Thanks for any assistance ! Steven |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Advance Filter Reference in Source Sheet
You need to know a what is the labels in the header row. My example looked
for the word "Start" as the header in column A. the macro deletes all the rows from row 1 to the Row just before the row where start is located. Sub test() Set c = Columns("A:A").Find(what:="Start", LookIn:=xlValues) If Not c Is Nothing Then Rows("1:" & (c.Row - 1)).Delete End If End Sub "SteveT" wrote: To Clarrify, I'm looking for a function ( macro ) that will assist with locating the header row and the columns containing the headers ( some times row is inserted between ) Thanks, Steven "SteveT" wrote: Hi Wise Ones !, My customer has very specific viewing requirements of a report that is provided me in a CSV format. I have a macro that converts information into required viewing format 1) Open XLS workbook 2) Copy CSV sheet 3) Paste sheet 1 XLS workbook 4) Advance filter into sheet 2 into correct rows specified by customer 5) misc other adjustments made with macro to suite customer It works fine as long as the source range of advanced filter is fixed. Unfortunetly this isn't always case. The rows & columns of source data change regularly I believe problem could be fixed if I could A) pinpoint the location of the "Header Words" at the start of the macro, B) Reference location sheet two ( A1= 'Sheet 1'! + Location found ) C) Then run advance filter But I don't know how to find the reference row and location of the header words. Can someone help? Thanks for any assistance ! Steven |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Advance Filter Reference in Source Sheet
Joel, thanks believe on track ...
I want to look in sheet 1 and transfer to sheet 2 also not sure where column or row could be. Can you help me adjust your macro below to take into account both ? "Joel" wrote: You need to know a what is the labels in the header row. My example looked for the word "Start" as the header in column A. the macro deletes all the rows from row 1 to the Row just before the row where start is located. Sub test() Set c = Columns("A:A").Find(what:="Start", LookIn:=xlValues) If Not c Is Nothing Then Rows("1:" & (c.Row - 1)).Delete End If End Sub "SteveT" wrote: To Clarrify, I'm looking for a function ( macro ) that will assist with locating the header row and the columns containing the headers ( some times row is inserted between ) Thanks, Steven "SteveT" wrote: Hi Wise Ones !, My customer has very specific viewing requirements of a report that is provided me in a CSV format. I have a macro that converts information into required viewing format 1) Open XLS workbook 2) Copy CSV sheet 3) Paste sheet 1 XLS workbook 4) Advance filter into sheet 2 into correct rows specified by customer 5) misc other adjustments made with macro to suite customer It works fine as long as the source range of advanced filter is fixed. Unfortunetly this isn't always case. The rows & columns of source data change regularly I believe problem could be fixed if I could A) pinpoint the location of the "Header Words" at the start of the macro, B) Reference location sheet two ( A1= 'Sheet 1'! + Location found ) C) Then run advance filter But I don't know how to find the reference row and location of the header words. Can someone help? Thanks for any assistance ! Steven |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Advance Filter Reference in Source Sheet
The start column will always be the same because you are using CSV format.
The question is how many extra rows of data are in the file before the data that you need. Rows and Columns are VBA areas in the spreadsheet. They don't need numbers. I made some changges to the code to copy the results to sheet 2. Sub test() With Sheets("Sheet1") Set C = .Columns("A:A"). _ Find(what:="Start", LookIn:=xlValues) If (Not C Is Nothing) And (C.Row 1) Then .Rows("1:" & (C.Row - 1)).Delete End If LastRow = .Cells(Rows.Count, "A").End(xlUp).Row LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column Set CopyRange = .Range("A1", .Cells(LastRow, LastCol)) CopyRange.Copy Destination:=Sheets("Sheet2").Range("A1") End With End Sub "SteveT" wrote: Joel, thanks believe on track ... I want to look in sheet 1 and transfer to sheet 2 also not sure where column or row could be. Can you help me adjust your macro below to take into account both ? "Joel" wrote: You need to know a what is the labels in the header row. My example looked for the word "Start" as the header in column A. the macro deletes all the rows from row 1 to the Row just before the row where start is located. Sub test() Set c = Columns("A:A").Find(what:="Start", LookIn:=xlValues) If Not c Is Nothing Then Rows("1:" & (c.Row - 1)).Delete End If End Sub "SteveT" wrote: To Clarrify, I'm looking for a function ( macro ) that will assist with locating the header row and the columns containing the headers ( some times row is inserted between ) Thanks, Steven "SteveT" wrote: Hi Wise Ones !, My customer has very specific viewing requirements of a report that is provided me in a CSV format. I have a macro that converts information into required viewing format 1) Open XLS workbook 2) Copy CSV sheet 3) Paste sheet 1 XLS workbook 4) Advance filter into sheet 2 into correct rows specified by customer 5) misc other adjustments made with macro to suite customer It works fine as long as the source range of advanced filter is fixed. Unfortunetly this isn't always case. The rows & columns of source data change regularly I believe problem could be fixed if I could A) pinpoint the location of the "Header Words" at the start of the macro, B) Reference location sheet two ( A1= 'Sheet 1'! + Location found ) C) Then run advance filter But I don't know how to find the reference row and location of the header words. Can someone help? Thanks for any assistance ! Steven |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
'Copy to' Advance Filter depend only on sheet ID not start sheet | Excel Worksheet Functions | |||
Advance Filter | Excel Discussion (Misc queries) | |||
Using a relative SHEET reference for source data in a chart | Charts and Charting in Excel | |||
advance filter | Excel Discussion (Misc queries) | |||
about advance filter | New Users to Excel |