Basic Data Transfer in VBA

[soelcht]

Hi guys,

please help me, I have an easy question in VBA.

What is the most easiest code for this:
I want to transfer data (66 columns and 100 rows) from sheet1 to sheet2 in the 6th row? A1 will move to A6, A2 will move to A6, B1 will move to B6, etc.

Thank you for your help!

 

[ranman256]

A1 and A2 cant move to the same cell.
A1 will move to A6, A2 will move to A6, B1 will move to B6, etc.

Do you mean A1 to A6, A2 to A7?

 

[soelcht]

Yes. Sheet1 A1 --> Sheet2 A6

 

[ranman256]

Sub CopyBlock()


    ActiveSheet.UsedRange.Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    Range("A6").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

 

[soelcht]

Thanks, awesome! How to do it with Dim or ReDim?

 

[soelcht]

Thanks, awesome! How to do it with Dim or ReDim? I am asking, because I only need the code for A1:BN100 array, so actually this should move to sheet2 A6:BN106