Thread: Copying a dataset multiple times - and selecting titles for this dataset

Hi guys,
I was wondering if anyone could help me.

I have a dataset/array (A1:BZ199) that I need copied around 400 times, with a gap of one row between each dataset. I need the title from a different spreadsheet (sheet2) to appear in the cells A1, A201, A401.... ETC (The titles in sheet2 are from a1:a400)


If anyones wondering. This is what my code looks like so far;

Sub q
Dim myRange As Range
Dim last As Long, x As Long
Set myRange = Range("a8:bz200") 
For x = 1 To 400         
last = Cells(Rows.Count, "a").End(xlUp).Row
myRange.Copy Cells(last + 2, 1)

Next
End Sub

So this copies the dataset, but doesn't solve the title problem. Is this the way I should do it?

-thanks in advance.

Hi

how about

  Dim myRange As Range
  Dim x As Long
  Set myRange = Range("a8:bz200")
  For x = 1 To 400
    Sheets("Sheet2").Cells(x, 1).EntireRow.Copy Cells(Rows.Count, "a").End(xlUp).Offset(1, 0)
    myRange.Copy Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
  Next x

rylo

Hi rylo, thanks for the reply - the code I posted was wrong I realized array is meant to be (a1:bz199).
Your code posts the title in the gap between the two arrays, Is it possible to put the title in the array. (So in the array from a201:bz399 the title would appear in cell a201?)

Hi

Sorry, not following. Can you attach an example workbook showing both a before and after situation. I'm not sure if you want to overwrite some of the existing array before it is copied.

rylo

I've attached what I mean. (This isn't my actual document as it contains formula which requires an external program in order to see)

So in this document I have four datasets. I'd like to mimic this but with many more datasets. Cell A1 needs to be changed, but the other cells in this row need to remain constant. (Unless you need to paste the whole row, then I can try and find away to work around it)

-Oh and the numbers just show what should be data.

Does this make sense?
-Thanks

Hi

I'm assuming that sheet1 is actually what you want the final output to look like, and that initially it only has data in the range A1:BZ199. If so then try

Sub a()
  Dim myRange As Range, TitleRng As Range
  Dim x As Long, ce As Variant
  With Sheets("Sheet2")
    Set TitleRng = .Range(.Range("A2"), .Range("A" & .Cells(Rows.Count, 1).End(xlUp).Row))
  End With
  Sheets("Sheet1").Activate
  Set myRange = Range("a1:bz199")
  Range("A1").Value = Sheets("Sheet2").Range("A1").Value
  For Each ce In TitleRng
    outrow = Cells(Rows.Count, 1).End(xlUp).Offset(2, 0).Row
    myRange.Copy Cells(outrow, 1)
    Cells(outrow, 1).Value = ce
  Next ce
End Sub

rylo

Brilliant - works perfectly!!!!
I can't thank you enough - this has saved me hours of copying and pasting!