Shift Columns Down

[djp203]

Hello all,

probably a very quick one for the well versed.

I don't believe I need anything clever as such, have tried doing it by recording macros however:

Range("A1:B1").Select
Selection.insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Does not appear to work.

Essentially I have values in columns A1:B4000 (the 4000 can change over time but is always going to be somewhere between 4000-5000). All I want to do is have a piece of code replicate:

Select cells A1:B1
Hit Ctrl+Shift+Plus Sign,
Select "Shift Cells Down".

Which would then mean that the data starts in A2 and B2 rather than A1 and B1.....

I have tried searching all over the internet but the ones I see always have loads of conditions attached to them with inputting formula and lots of loops etc.

Any help would be much appreciated.

Kind Regards.

 

[craig.penny]

Try this

Sub InsertTopRow()
Range(Cells(1, 1), Cells(1, 2)).Insert
End Sub

I may not be understanding exactly what you want though

 

[djp203]

Hi Craig,

thank you for kicking this off.

I tried your code on a new work book and it worked great, popped the code into mine and it brought up a run time error 1004 once it got to the portion of your code I used. So I'm guessing I haven't explained myself well enough or I have not placed your code correctly.

As I failed to mention there is other code around it would this have an effect?

Sheets("Sheet2").Select

Sheets("Sheet1").Columns("B:B").Copy Sheets("Sheet2").Range("A1")
Sheets("Sheet1").Columns("C:C").Copy Sheets("Sheet2").Range("B1")


[B]Range(Cells(1, 1), Cells(1, 2)).insert[/B]
    
ActiveSheet.Range("$A$1:$B$1042143").RemoveDuplicates Columns:=Array(1, 2), _
        Header:=xlNo

Application.Calculation = xlCalculationAutomatic

This is a sub set of the code for the workbook which is relevant to the sheet I am having trouble with (Sheet2)

I copy in two columns into A1 and B1 then I want to bump them down one. There is already static formula in Columns C onwards which I do not wish to change, so I need just to move down A and B rather than inserting a complete new row - I need the copied in columns to align correctly.

The other alternative would be I suppose for Column B for instance to be copied from range B1:B10000 or something like that to destination cell A2, rather than A1 - but I don't know how to copy ranges yet!

If you need something more specific let me know

Kind Regards

 

[craig.penny]

Would this work?

Change

Sheets("Sheet1").Columns("B:B").Copy Sheets("Sheet2").Range("A1")
Sheets("Sheet1").Columns("C:C").Copy Sheets("Sheet2").Range("B1")


[B]Range(Cells(1, 1), Cells(1, 2)).insert[/B]

To

Sheets("Sheet1").Columns("B:B").Copy Sheets("Sheet2").Range("A2")
Sheets("Sheet1").Columns("C:C").Copy Sheets("Sheet2").Range("B2")

 

[djp203]

Unfortunately not - had tried previously but the copied area is then one larger than the area it is being pasted into - so it fails.

 

[craig.penny]

Try this

Sub abc()

Dim LastRow As Long, s1 As Worksheet, s2 As Worksheet
Set s1 = ActiveWorkbook.Sheets("Sheet1")
Set s2 = ActiveWorkbook.Sheets("Sheet2")
LastRow = s1.UsedRange.Rows.Count
s1.Range(s1.Cells(1, 2), s1.Cells(LastRow, 2)).Copy s2.Range("A2")
s1.Range(s1.Cells(1, 3), s1.Cells(LastRow, 3)).Copy s2.Range("B2")

End Sub

 

[craig.penny]

Any luck?

 

[djp203]

Hi Craig,

apologies for slow reply have been away from internet for a couple days.

That worked great thank you, have now used that logic in other things that I have done as well.

Thank you for taking the time.

 

[craig.penny]

You're quite welcome!