VBA loop

[Summerso1]

Hi I'm trying to do a VBA loop for copying B6 pasting it to L4 and then copying K2 to C6, then doing the same again but copying from B7 pasting it to L4 and then copying K2 to C7

the bold numbers are the ones that I want to copy/paste next to after these would be copy from B8............C7

I have this so far put I really need it to loop untill there are no more values in column B

thank you!!

Range("B6").Select
Selection.Copy
Range("L4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Calculate
Range("K2").Select
Selection.Copy
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B7").Select
Application.CutCopyMode = False
Selection.Copy
Range("L4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Calculate
Range("K2").Select
Selection.Copy
Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[NeonRedSharpie]

I have a question for you. Are you sure you have those paste to and paste from correct? What's the benefit of overwriting L4 every time? And then you're autopopulating column C with the value from K2 over and over? Just double checking.

 

[Norie]

Perhaps.

Set rng = Range("B6")

Do
    rng.Copy Range("L4")

    Calculate

    Range("K2").Copy rng.Offset(,1)

    Set rng = Rng.Offset(1)

Loop Until rng.Value = ""

 

[Summerso1]

yes basically the K2 changes with every input to L4 if that makes sense so i need to recalculate every time I paste in to L4 before copying K2 sorry for the confusion!

 

[Summerso1]

This worked but needs to paste just the value not the formula from K2

 

[Norie]

You didn't actually mention that.

Range("K2").Copy

rng.Offset(,1).PasteSpecial xlPasteValues

 

[Summerso1]

haha Super start thank you