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
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