Modify VBA Macro

[GCLIFTON]

Is there a way to Modify this Macro to read a list of Number in Sheet 2, by Cell A1 then A2, etc (loop) for example the table below would reflect what is in Sheet 2.Then take that number in Sheet 2 A1 and place it in Column U with all the Data and then copy and Duplicate "A1:"U189" beside me having to put the total number or repetitions in the Input Box. So now basically the Macro is now labeling all that is being Copied by Sheet 2 A1, A2,. So in my example 101 would have 189 lines labeled as 101 and 102 will have 189 labeled as 102



Dim rng As Range
Dim i As Long

Set rng = Range("A1:T189")
For i = 1 To InputBox("Enter repetitions")
rng.Copy rng.Offset(rng.Rows.Count * i)
Next i

101

102

103

104

105

<tbody>
</tbody>

 

[iconiuss]

Hey GCLIFTON,

I got a little confused with what your asking.
Your code is copying the code downwards, but it seems your asking to copy it across.

Here's some code that copies across based on your criteria.

Dim cpyRng As Range
Dim repRng As Range
Dim i As Long

Set cpyRng = Sheets("Sheet1").Range("A1:T189")
Set repRng = Sheets("Sheet2").Range("A:A").CurrentRegion

For i = 1 To repRng.Count
    cpyRng.Offset(, (cpyRng.Columns.Count + 1) * i) = cpyRng.Value
    Range("a1").Offset(, ((cpyRng.Columns.Count + 1) * i) - 1) = repRng(i)
Next

Let me know if this is what you had intended.

 

[GCLIFTON]

No i wish for the code to copy across. What i currently want to do.
1. Not to use you the Input Box criteria but use a list.
So beside seeing lines just duplicated over and over without a number or label. There will now be number or a label to apply with them that will be located in Sheet 2.. Starting with A1 going downward. In Sheet 2 there will be a list of Unit Number (label). Which will tell the macro not only how many times to create the copy data but also label the data that was copied by Unit Number. I was thinking to just put that Label aka Unit Number in Column U after it is copied. So now the Copied function Copies Everything from A to U in one spreadsheet. Sheet 1 to be exact showing all of the data labeled.

 

[iconiuss]

Modified so that it copies down instead of across.

Dim cpyRng As RangeDim repRng As Range
Dim i As Long


Set cpyRng = Sheets("Sheet1").Range("A1:T189")
Set repRng = Sheets("Sheet2").Range("A:A").CurrentRegion


For i = 1 To repRng.Count
    cpyRng.Offset((cpyRng.Rows.Count + 1) * i) = cpyRng.Value
    cpyRng(1).Offset((cpyRng.Rows.Count + 1) * i, cpyRng.Columns.Count) = repRng(i)
Next

The code leaves a gap between the copies. If you don't want that, modify by removing the "+ 1" next to each "rows.count"

    cpyRng.Offset([COLOR=#ff0000][U]cpyRng.Rows.Count[/U] * i[/COLOR]) = cpyRng.Value
    cpyRng(1).Offset([COLOR=#ff0000][U]cpyRng.Rows.Count[/U] * i[/COLOR], cpyRng.Columns.Count) = repRng(i)

Let me know how this works for you.

 

[GCLIFTON]

This works but how can i have the Sheet 2 information follow with all of the data. Currently it is only labeling the first line of the 189 lines.How would the marco read to write to all of the 189 lines.

 

[iconiuss]

Try,

[COLOR=#574123]Dim cpyRng As RangeDim repRng As Range[/COLOR]Dim i As Long


Set cpyRng = Sheets("Sheet1").Range("A1:T189")
Set repRng = Sheets("Sheet2").Range("A:A").CurrentRegion


For i = 1 To repRng.Count
    cpyRng.Offset((cpyRng.Rows.Count + 1) * i) = cpyRng.Value
    cpyRng[COLOR=#ff0000].columns[/COLOR](1).Offset((cpyRng.Rows.Count + 1) * i, cpyRng.Columns.Count) = repRng(i)
[COLOR=#574123]Next[/COLOR]

 

[GCLIFTON]

One last question where and i cant find it but where does it say in this Marco to place the Sheet 2 information in Column U in Sheet 1 in Column U. If i want to change this to P or Q how can i. Just a question

 

[iconiuss]

On the line

[COLOR=#333333]cpyRng[/COLOR].columns[COLOR=#333333](1).Offset((cpyRng.Rows.Count + 1) * i, cpyRng.[/COLOR][COLOR=#ff0000]Columns.[COLOR=#333333][/COLOR]Count[/COLOR][COLOR=#333333]) = repRng(i)[/COLOR]

After the columns.count put "+ (number of columns across)"

eg.

[COLOR=#333333]cpyRng[/COLOR].columns[COLOR=#333333](1).Offset((cpyRng.Rows.Count + 1) * i, cpyRng.[/COLOR][COLOR=#FF0000]Columns.[COLOR=#333333][/COLOR]Count + 1[/COLOR][COLOR=#333333]) = repRng(i)[/COLOR]

would put it in column "V"

use negative to move inward( to P, Q etc.), but that will overwrite what you are copying.

If you want to make it dynamic based on whats in the copy range, it will need different code altogether.

 

[GCLIFTON]

Would that Over right the Header Lines as well

 

[iconiuss]

It's going to make a column of 101s etc for each block. If you want it in column P all data in the P column of each block will be overwritten.
If you make up a dummy sample with smaller blocks showing what you need I may b able to see what your after.