How to copy "X" rows of data then the next "X" rows of data until I'm at the bottom of my data

[RareNasturtium]

Hey Everyone,

I have a little experience with Excel but I'm getting into bigger projects and I need to copy information from one worksheet to another.

Worksheet A has information in Column A and the information could be anywhere from 50 to 450 rows. It changes. I want to copy 15 rows of data at a time along with the header...if possible. I'm going to copy these 15 rows at a time to a separate worksheet where I will eventually copy and link it to a Powerpoint spreadsheet. I'm guessing there is a formula or VBA that will do what I want but I can't find it. Any advice would be welcome. Thanks.

 

[RareNasturtium]

Thanks Rick. It did what I asked except it didn't give me header on each column. Is there a way to put the "title" or "header" from the column at the top of each sorted column? I did have a follow up question also that I posed regarding further manipulation. I would love your input!

Thanks again.

Nast

 

[Caleeco]

Hello Nast,

I'm glad my original code did what you needed it to do I can have a go at your follow up request, but I have some follow up questions of my own

1. So you need it to run on columns A, K-O
2. Is the batch size for all still 15?
3. If a column is filtered, do you wish to include or exclude non-visible cells
4. What structure do they need to be pasted into sheet NAICS as? i.e. the split batch for column A first, then underneath that would be the split batch for column K etc etc?

In terms of learning, Google is your friend I also listed a few recommendations here... explaining the resources I used to get started!
http://www.excelwtf.com/viewtopic.php?f=8&t=6&p=6#p6

Many Thanks
Caleeco

 

[Rick Rothstein]

Thanks Rick. It did what I asked except it didn't give me header on each column. Is there a way to put the "title" or "header" from the column at the top of each sorted column?

Sorry, I missed the header part, give this a try...

Sub MoveDataFifteenRowsAtATime()
  Dim R As Long, Data As Variant
  Data = Range("A2", Cells(Rows.Count, "A").End(xlUp))
  For R = 1 To UBound(Data) Step 15
    Sheets("WorksheetB").Range("A2").Offset(, (R - 1) / 15).Resize(15) = Application.Index(Data, Evaluate("ROW(" & R & ":" & R + 14 & ")"), 0)
  Next
  With Sheets("WorksheetB").Range("A2")
    .CurrentRegion.Replace "#REF!", "", xlWhole
    .Offset(-1).Resize(, .CurrentRegion.Columns.Count) = Range("A1").Value
  End With
End Sub

I did have a follow up question also that I posed regarding further manipulation. I would love your input!

I have some questions about what you posted in Message #10...

1) What exactly did you mean when you said "Instead of just column A could we set up the macro for Columns K-O"? How would that work... the groups of 15 from Column K spread out across whatever number of columns were required followed by the groups of 15 from Column L spread out next to it and so on for Columns M, N and O?

2) What exactly did you mean by "linked" when you said "Will it matter if the values in the range are linked from a filtered range?"

3) This statement "Hopefully that didn't complicate it too much" raises the question, is this additional request separate from the request about Column A or is the output from the new request to be placed next to the output for the Column A request?

 

[RareNasturtium]

Thanks Caleeco. First let me say you're fast and thorough! Wow!

1. Sorry for the confusion. My follow up request would be just for columns K-O. The first request was more of a can this be done and how request. This request is the actual spreadsheet that I'm using.
2. Yes. I'm still looking for 15 rows of data plus the header in each split batch.
3. I would like to include visible data only.
4. Not sure which structure works best pasting to NAICS? Maybe one set of split batch under the other and so on? Hope that makes sense.

Thanks for the direction on training. Much appreciated. Look forward to hearing from you.

Nast

 

[RareNasturtium]

Thanks Rick! First off let me say I'm impressed! You are fast and through!

1. I don't need column A. It was just more of a can this be done question. Columns K-O are my actual spreadsheet. Sorry for the extra step.

2. My first worksheet is the raw data. I've copied and link pasted that range to several sheets that follow. I've filtered each of those ranges on each sheet. Each sheet needs a specific filter. Number filtered out that are >1.2 or <0.8. Hopefully that makes sense.

3. This request requires the same parameters as the initial request but column A is not a part of the request as explained in 1.

Thanks again for posting so quickly and the thoroughness of your responses. I did not realize how complex it was to explain a job!

Look forward to hearing from you.

Nast

 

[Rick Rothstein]

The first request was more of a can this be done and how request. This request is the actual spreadsheet that I'm using.

You should ask the actual question you want answered (and don't simplify it)... we do not need the practice developing code for question no one actually cares about (we could spend that time answering other people's question, you know, people like you who come here for help, instead of wasting that time on an irrelevant question).

 

[RareNasturtium]

My apologies Rick. I understand that now. I'm sorry for the extra step.

 

[RareNasturtium]

Hey Caleeco,

I checked out your site. Good stuff. Is there more coming? It was layed out nicely.

Gonna invest in that VBA for Dummies book and have a go. Thanks again for the tips!

Once again my apologies to everyone for not being more concise in my original message. I do appreciate the time and effort. Look forward to hearing from you.