Transfer CELL value and spread it among 24 ROWs on another sheet

[rhhince]

I am trying to transfer a cell value and paste it on 24 rows on
another worksheet. The next day I take another cell value and paste it
on the next 24 rows on the other worksheet. Automatically, is the
intention. I have a sheet with 40000 rows and need to transfer the
info from one sheet with over 1600 rows. I am not sure what formula
would have to be used to do so. Any help would be appreciated.

 

[Roger Govier]

Hi

The following code will deal with copying your 1600 rows of data to
another sheet and repeating it 24 times
In the code wss is set as the Source sheet and wsd as the Destination
sheet. I have used Sheet1 and Sheet2 in my code, but you will need to
alter this to suit the sheet names you have.

Sub CopyData()
Dim wss As Worksheet, wsd As Worksheet
Dim i As Long, j As Long, k As Long, lr As Long

Set wss = Sheets("Sheet1") '<---- Change sheet names
Set wsd = Sheets("Sheet2")

lr = wss.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lr
wss.Cells(i, 2) = i
Next i

k = 1
For j = 1 To 24
wss.Range(Cells(1, 1), Cells(lr, 2)).Copy wsd.Cells(k, 1)
k = k + lr
Next j

wsd.Activate
wsd.Columns("A:B").Select
Selection.Sort Key1:=Range("B1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

wsd.Columns("B:B").EntireColumn.Delete

End Sub

You can copy the code and paste it into your Visual Basic Editor (VBE)
in a Standard Module located in your file.

To do this,

Alt + F11 (open VBE)
Ctrl + R (open Project Explorer)
Select the file name on the left
Insert > Module
Paste code in Module

To run the macro, Tools>Macros>Macro>click on CopyData>Run

For more help on inserting code into workbooks, David McRitchie has lots
of useful help on his site at
http://www.mvps.org/dmcritchie/excel/install.htm
http://www.mvps.org/dmcritchie/excel/getstarted.htm