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