paste in range from column B to column A

zakynthos

Board Regular
Joined
Mar 28, 2011
Messages
169
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.:confused:

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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Code:
    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 #.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top