Archiving data horizontally

[sobrien]

I want to create a macro to archive data from a sheet into another sheet and create a continuous horizontal list. As follows:
I want to copy columns C:I in sheet "Accounts" and then paste into sheet "Account history" but so it pastes onto the next column at the end of the existing data.


Thanks

 

[al_b_cnu]

Hi,

do you want the entire contents of C:Icopying, or just he last row?

 

[sobrien]

entire contents thanks

 

[al_b_cnu]

Try this:

Option Explicit

Sub TransposeData()
Dim iToCol As Integer
Dim rFrom As Range
Dim wsFr As Worksheet, wsTo As Worksheet

Set wsFr = Sheets("Accounts")
Set wsTo = Sheets("Accounts History")
iToCol = wsTo.Cells(1, Columns.Count).End(xlToLeft).Column + 1

Set rFrom = Intersect(wsFr.Columns("C:I"), wsFr.UsedRange)

rFrom.Copy
wsTo.Range(Cells(1, iToCol).Address).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False

End Sub

 

[sobrien]

Thanks that should do it