paste in range from column B to column A

[zakynthos]

How would I amend this to copy all values from b4 to the last row with data in column B to A4 to the last row in column A. At the moment I'm getting the value in B4 copied to all rows in Column A, whereas I want B4 copied to A4, B5 to A5 etc..... It's the code in blue where I've obviously made a mistake.

Sheets("Briefings").Select
Range("b4").Select

Selection.Copy

ActiveCell.Formula = "='[Peoplesoft_Data_CS_and_Cons_Sales(1).xlsm]Briefings'!$a4"

Selection.Copy

Range("b4").Copy Range("b5:b" & Cells(Rows.Count, 1).End(xlUp).Row - 0)


'paste in values to all cells in column:

Range("b4").Copy Range("b5:b" & Cells(Rows.Count, 1).End(xlUp).Row)
With Range("b5:b" & Cells(Rows.Count, 1).End(xlUp).Row)
.Value = .Value


Range("b4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'now copy column b to a:



Selection.Copy

Range("b4").Copy Range("a4:a" & Cells(Rows.Count, 1).End(xlUp).Row - 0)



'paste in values to all cells in column:

Range("a:a").Copy Range("a5:a" & Cells(Rows.Count, 1).End(xlUp).Row)
With Range("a5:a" & Cells(Rows.Count, 1).End(xlUp).Row)
.Value = .Value


Range("a4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False



End With


End With

 

[AlphaFrog]

    Dim Lastrow As Long
    
    With Sheets("Briefings")
        Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("B4:B" & Lastrow).FormulaR1C1 = "='[Peoplesoft_Data_CS_and_Cons_Sales(1).xlsm]Briefings'!RC1"
        .Range("B4:B" & Lastrow).Value = .Range("B4:B" & Lastrow).Value

        ' copy column B to A:
        .Range("A4:A" & Lastrow).Value = .Range("B4:B" & Lastrow).Value
    End With

Forum Tip: Pasting VBA code in the forum editor
It would be best if you surround your VBA code with code tags e.g [CODE]your VBA code here[/CODE]
It makes reading your VBA code much easier. When you're in the forum editor, highlight your pasted VBA code and then click on the icon with the pound or number sign #.

 

[zakynthos]

Thanks very much for your help!

I've noted the part about putting code tags around the code to make for easier reading.

As I've almost completed a VBA project that reduces a 3 hours-to-prepare report to 30 seconds, I hope, by next week, to feedback the results and sample code as to all those who have kindly offered me help and advice on this site in the hope it will be of some use to others with similar problems working with extremely large data sets, slow running, refreshing of vlookups etc