Copy Row X Times down Macro

Need some help ?

Getting error Run-time error '1004': on execution of macro to copy contents on row 8 Columns B,C,D,E

When I click on debug it shows hanging up on this row

For Each rngCell In ThisWorkbook.Worksheets("Sheet1").Range("B11:D9" & Cells(Rows.Count, 4).End(x1Up).Row)

The "5" in cell (B:8) is the number of times I want it copied


B:8 5
C:8 Semi
D:8 CM-13
E:8 RS-Allied


Sub CopyRowsXTimes()

Dim rngCell As Range

This Workbook.Worksheets("Sheet1").Cells.ClearContents

For Each rngCell In ThisWorkbook.Worksheets ("Sheet1").Range("B11:D9" & Cells(Rows.Count, 4).End (x1Up).Row)

With ThisWorkbook.Worksheets("Sheet1")

.Cells(.UsedRange.SpecialCells(x1CellTypeLastCell).Row, 1).Resize(rngCell.Value, 4).Value = rngCell.Offset(, -3).Resize(1, 4).Value


(code is below)

Sub CopyRowsXTimes()

Dim rngCell As Range

'This Workbook.Worksheets("Sheet1").Cells.ClearContents
For Each rngCell In ThisWorkbook.Worksheets("Sheet1").Range("B11:D9" & Cells(Rows.Count, 4).End(x1Up).Row)

With ThisWorkbook.Worksheets("Sheet1")

.Cells(.UsedRange.SpecialCells(x1CellTypeLastCell).Row, 1).Resize(rngCell.Value, 4).Value = rngCell.Offset(, -3).Resize(1, 4).Value

End With
Next rngCell

Set rngCell = Nothing

End Sub



Thanks in advance for your help.
Dennis

Try changing this:

Please Login or Register  to view this content.

To this:

Please Login or Register  to view this content.

Jersey,

I changed the code, same thing happens ?

Dennis

When I try to edit the code and enter xLUp it changes it back to x1Up ??

Here is it copied and pasted

[For Each rngCell In ThisWorkbook.Worksheets("Sheet1").Range("B11:D" & Cells(Rows.Count, 4).End(xlUp).Row)]

Can you attach a sample copy of the file which produces the error?

Yes, how do I attach a file in my reply ?

Here it is ---- Nothing critical in file, just building it

On Sheet 1


Copy of Trucking Orders 3-28-2015.xlsm

There's no data in that range in Sheet1??

Really ??

B:8 , C:8, D:8, & E:8

There is in the file sent. Maybe I am doing something wrong ??

Your code references "B9:D" & Lastrow.

Please Login or Register  to view this content.

What is the code attempting to do?

Does this do what you want?

Please Login or Register  to view this content.

I am trying to use the code to look at a cell and if there is say number 5, to copy another cell 5 times down in rows

Thanks

OK, Sorry, I guess what I was trying to say and do is this:

I was saying if I had the data where it is and the number 5 is in the cell I wish the other 3 cells to be copied to a given range. If the code is wrong, I am looking for help correcting the code. I am just trying to copy X times, select a range and copy to any range I select.

Thanks for any help