Need help with macro Dynamic Range??

[pennbowl]

This is a smaller version of my macro which goes up to "BI100"of which when run would paste whatever is in Range ("D1:D31") onto range "BJ100"
The row from "BI2" downwards could vary in length my hard code macro would run all the way to line 100
I would like the macro to stop when column "BI" isn't populated and End Sub
Many Thanks Steve

Sub Macro8()
Sheets("LAYWIN").Select
Range("BI2").Select
Selection.COPY
Range("C20").Select
ActiveSheet.Paste
Range("D1:D31").Select
Application.CutCopyMode = False
Selection.COPY
Range("BJ2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Range("BI3").Select
Application.CutCopyMode = False
Selection.COPY
Range("C20").Select
ActiveSheet.Paste
Range("D1:D31").Select
Application.CutCopyMode = False
Selection.COPY
Range("BJ3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True

 

[DanteAmor]

Hi and welcome to MrExcel

Replace your macro with the following:

Sub Macro_2()
  Dim i As Long
  Sheets("LAYWIN").Select
 
  For i = 2 To Range("BI" & Rows.Count).End(3).Row
    Range("C20").Value = Range("BI" & i).Value
    Range("BJ" & i).Resize(1, 31).Value = Application.Transpose(Range("D1:D31").Value)
  Next
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[pennbowl]

Sub Macro_2() Dim i As Long Sheets("LAYWIN").Select For i = 2 To Range("BI" & Rows.Count).End(3).Row Range("C20").Value = Range("BI" & i).Value Range("BJ" & i).Resize(1, 31).Value = Application.Transpose(Range("D1:D31").Value) Next End Sub

Thank you so much does the trick well the only thing that is different is that when it fills in BJ it leaves green flags of which it is text how do i alter your code to number format but you've saved my day ✔

 

[DanteAmor]

Thank you so much does the trick well the only thing that is different is that when it fills in BJ it leaves green flags of which it is text how do i alter your code to number format but you've saved my day

I do not understand what you mean.

The macro sets values, it does not set formats.

Before running the macro, remove the formats.

Or try this (This is how you have it in your macro, paste special, values, transpose.)

Sub Macro_2()
  Dim i As Long
  Sheets("LAYWIN").Select
  
  For i = 2 To Range("BI" & Rows.Count).End(3).row
    Range("C20").Value = Range("BI" & i).Value
    Range("D1:D31").Copy
    Range("BJ" & i).PasteSpecial xlPasteValues, , , True
  Next
End Sub

 

[DanteAmor]

Check that the format of cells D1:D31 is "Number"

I assume that cell D1 has a formula, you can put that formula here to review it.

 

[pennbowl]

Sub Macro_2() Dim i As Long Sheets("LAYWIN").Select For i = 2 To Range("BI" & Rows.Count).End(3).row Range("C20").Value = Range("BI" & i).Value Range("D1:D31").Copy Range("BJ" & i).PasteSpecial xlPasteValue

Great and thanks, just to let you know your first solution is a lot quicker so I will stick to that one /took 2mins 39 sec as apposed to 3mins 09 sec
Many tanks again may God look over you Dante Amor cheers.

 

[pennbowl]

Great and thanks, just to let you know your first solution is a lot quicker so I will stick to that one /took 2mins 39 sec as apposed to 3mins 09 sec
Many thanks again may God look over you Dante Amor cheers.