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.

 

[Special-K99]

I won't be able to solve this but I'll throw a few questions at you that are bound to be asked and will help anyone that decides to solve it.

1) It sounds like this is not a one-off job, ("It changes")is that correct?
2) What row does the data start on?
3) Does it ALWAYS start on that row?

 

[Caleeco]

Hello,

This is fairly staright forward with VBA. A couple of quetions for you:
1. What are the sheet names you are working with?
2. Are the datasets a consitent length? i.e. 15 rows each? (in your example you mention it could be 50 rows total, which wouldnt match a 15row batch size)
3. What type of data are you copying? dates? general numbers? text?
4. Does your header row sit in Row 1?

Thanks
Caleeco

 

[RareNasturtium]

I'm using Excel 2010. I've attached a sample of what will hopefully explain what I want.

Column A/WorksheetA
DATA
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

Desired result
Column A/WorksheetB Column B/WorksheetB
DATA DATA
1 16
2 17
3 18
4 19
5 20
6 21
7 22
8 23
9 24
10 25
11 26
12 27
13 28
14 29
15 30

 

[RareNasturtium]

Wow...thanks for the speedy responses! Didn't expect it so quickly.

So to answer a few questions.

• 1. What are the sheet names you are working with?
  
  There are several sheet names in the workbook. I'm working with worksheet 4 Level NAICS LQ>1.2 and NAICS
  
  2. Are the datasets a consistent length? i.e. 15 rows each? (in your example you mention it could be 50 rows total, which wouldnt match a 15row batch size)
  
  The datasets are not consistent. They can range from 10 to 100 or more. It varies. I'm pulling information from an economic database with county data. I need to do county spreadsheets for all the counties. So the data changes and varies a lot.
  
  3. What type of data are you copying? dates? general numbers? text?
  
  The data is mostly numbers with one column of text.
  
  4. Does your header row sit in Row 1?
  
  My header row is always in row 1.​

 

[Caleeco]

Hello,

Sorry for the delayed reply, had a long drive home! Thanks for the additional information. I think I understand your requirement. Please try this code on a COPY of your dataset:

Sub BatchSplit()
Application.ScreenUpdating = False
Dim lr As Long
Dim data()
Dim i As Long, x As Long, y As Long
Dim col As Long


lr = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row


If lr <= 16 Then Exit Sub


x = 0
y = 0
data = Range("A2:A" & lr).Value


For i = LBound(data) To UBound(data)
    Range("B2").Offset(x, y).Value = data(i, 1)
    x = x + 1
    If i Mod 15 = 0 Then
        y = y + 1
        x = 0
    End If
Next i


Range("A1", Range("A1").Offset(0, y + 1)).Value = Range("A1").Value
Columns(1).EntireColumn.Delete
Application.ScreenUpdating = True
End Sub

Probably not the cleanest method, but it worked when I tested it

Let me know how you get on with it. Thanks,
Caleeco

 

[Rick Rothstein]

Here is another macro for you to try (run it from the sheet with your data, change the highlighted output sheet name as required)...

[table="width: 500"]
[tr]
	[td]Sub MoveDataFifteenRowsAtATime()
  Dim R As Long, Data As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  For R = 1 To UBound(Data) Step 15
    Sheets("[B][COLOR="#0000FF"]WorksheetB[/COLOR][/B]").Range("A1").Offset(, (R - 1) / 15).Resize(15) = Application.Index(Data, Evaluate("ROW(" & R & ":" & R + 14 & ")"), 0)
  Next
  Sheets("[B][COLOR="#0000FF"]WorksheetB[/COLOR][/B]").Range("A1").CurrentRegion.Replace "#REF!", "", xlWhole
End Sub[/td]
[/tr]
[/table]

 

[Caleeco]

Rick, your ability to achieve ultimate brevity in your VBA code always astounds me! Maybe I'll be that good one day hehe :D

 

[RareNasturtium]

Thanks Rick and Caleeco for your help! I will give it a try and report back!

 

[RareNasturtium]

Thanks Caleeco! It did exactly what I asked. Now I'm wondering if I could ask for more? I know right?!?! Give the guy what he wants and he asks for more!

Instead of just column A could we set up the macro for Columns K-O? Will it matter if the values in the range are linked from a filtered range? Will there be an issue with only taking visible cells? Hopefully that didn't complicate it too much. Thanks again for all your help.

Are we able to move the all the sorted data to a new worksheet called NAICS?

Is there a place online where I could learn VBA? Maybe some YouTube videos? I'd love to learn more about this process so I can do a little of this myself.

Thanks again for the assist. Nicely done!

Nast