Finding Advance Filter Reference in Source Sheet

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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 ?

 

[Guest]

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