Looping copy paste question

[gemoon]

Im trying to make a macro that will reorganize data from a a comma delimited text file. Ive set up the macro to import the file into excel.

Then I get a worksheet with many similarly shaped blocks of data. Ive set up the macro to grab the data I want out of the top block and place it in a second worksheet. But then comes the problem. I dont quite understand how to loop this macro to move down the page and grab data.

I have many block with a shape similar to this:

x
x
y1
x
x
a1 b1 c1
d1 e1 f1
g1 h1 i1

There are many more blocks like this. They all have one y value and 21 rows of abc values. Each block is separated by 1 empty row.



Im trying to get the data into this format:

y1 a1 b1 c1
y1 d1 e1 f1
y1 g1 h1 i1
y2 a2 b2 c2
y2 d2 e2 f2
y2 g2 h2 i2
.
.
.


Here is the copy/paste portion for the first block. Im not sure how to make this a loop.

Sheets("Text File&quot.Select
ActiveSheet.Range("A4&quot.Select
Selection.Copy
Sheets("Results&quot.Select
ActiveSheet.Range("A1:A21&quot.Select
ActiveSheet.Paste
Sheets("Text File&quot.Select
Range("A18:H38&quot.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Results&quot.Select
Range("B1&quot.Select
ActiveSheet.Paste


Thanks for any advice.
Ed.

 

[Mikeb1970]

Hi gemoon,

Maybe there is a reason why you can't use this, but i wanna point it out to you annyway, maybe you don't know this excel feature.

Try importing your .csv file using the import external data feature.

On the menu select data, import external data, look for your file and look at the options you have there.

I'm sure it would be allot easier when you import the file this way, it will allow all normal things that can be done with normal excel data

Hope this will help you

Mike

 

[gemoon]

Mike,
Thanks for the reply. Import does a good job of parsing out my data into columns, but I needed to then select info that I needed and rearange it into a particular format.

I've spent the last couple of days researching this forum and others. This is the solution I came up with. I know its probably not the best way to do this, but the solution seems to work fine.

Sub CopySampleData()

' Define variables
Dim ICAPLines As Integer
Dim NumberOfRows As Integer
Dim ICAPSamples As Integer

' Count # of ICAP data lines
Sheets("Text File&quot.Select
Range("A18&quot.Select
Range(Selection, Selection.End(xlDown)).Select
ICAPLines = Selection.Rows.Count
Range("A1&quot.Select

' Count # of rows & calculate # of ICAP samples
Sheets("Text File&quot.Select
NumberOfRows = Sheets("Text File&quot.Range("A65536&quot.End(xlUp).Row
ICAPSamples = NumberOfRows / (ICAPLines + 20)
Sheets("ICAP Results&quot.Select
Range("A1&quot.Select

' Loop through all samples
Sheets("Text File&quot.Select
Range("A1&quot.Select
For Number0fSamples = 1 To ICAPSamples Step 1
' Copy sample name from sheet "text file"
Sheets("Text File&quot.Select
ActiveCell.Offset(rowOffset:=3, columnOffset:=0).Activate
Selection.Copy
Sheets("ICAP Results&quot.Select
Range(Selection, Selection(ICAPLines)).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Activate

' Copy line, Avg, Stddev, RSD, Rep1 and Rep2 from "text file"
Sheets("Text File&quot.Select
ActiveCell.Offset(14, 0).Activate
Range(Selection, Selection(ICAPLines, 8)).Select
Selection.Copy
ActiveCell.Offset((ICAPLines + 3), 0).Activate
Sheets("ICAP Results&quot.Select
ActiveSheet.Paste
ActiveCell.Offset(ICAPLines, -1).Activate
Next Number0fSamples
Range("A1&quot.Select
Sheets("Text File&quot.Select
Range("A1&quot.Select


End Sub