Macro to find, copy, paste data with changing stating points

[scoutzilla]

Greetings all and Happy Holidays.

My problem seems so simple I thought I could come up with a solution on my own, but after working on this for days, I ask for help.

I import .txt data file then need to find where where my data starts (TIME), the number of columns, the number of rows, copy all and paste to Sheet "Clean Data". I can't seem to come up with a Macro that will do this. Here is a sample of what my data might look like:

	Number of Rows
	Above
	TIME
	Changes
	TIME	Range	of	data	would	start	with	TIME	then	end	with	empty	cells
	0	2	2	2	2	2	2	2	2	2	2	2	2
	1	4	4	4	4	4	4	4	4	4	4	4	4
	2	6	6	6	6	6	6	6	6	6	6	6	6
	3	8	8	8	8	8	8	8	8	8	8	8	8
	4	10	10	10	10	10	10	10	10	10	10	10	10
	5	12	12	12	12	12	12	12	12	12	12	12	12

<colgroup><col><col><col><col><col><col><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>

Cheers!

 

[JLGWhiz]

I don't know if you mean the example is for 'Before' or 'After', but on the assumption that it is 'Before' then try this.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, fn As Range
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
lr = sh1.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
Set fn = sh1.Range("A:A").Find("Time", , xlValues, xlWhole, xlByRows, xlPrevious)
    If Not fn Is Nothing Then
        sh1.Range(fn, sh1.Cells(lr, fn.Column)).EntireRow.Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
    End If
End Sub

 

[scoutzilla]

JLGWhiz, Wow 10 lines of code, Neat Slick and works like it should. Thanks so much for the help!
And quick response. I'm amazed, wish I could code like that!!

Cheers Mate!!

 

[JLGWhiz]

JLGWhiz, Wow 10 lines of code, Neat Slick and works like it should. Thanks so much for the help!
And quick response. I'm amazed, wish I could code like that!!

Cheers Mate!!

You can code like that if you do a lot of research, participate in the forums and apply what you know to a personal project. That is essentially how I learned. The research was to understand the technical aspects of Excel parameters and limitations and how VBA operates to use those Excel attributes. I have been doing it for over 20 years and am still learning new stuff daily. So don't be discouraged if you can't write concise code after a couple of weeks. It takes time.
Regards, JLG