automatically move data 1

[ruthcali]

HI,
I'm using Access 2000.

I have 13 columns of data and about 400 rows.

We have a big plotter where all the data can fit on one large sheet of paper, but only if the data is divided into 2 separate sets of columns.

So i need my data divided into: A1 thru M200 and then O2 thru AA200.

In other words, any data below row 200 should be moved and placed in columns O thru AA.

I found some code that works for data in one column (A) only. i'm trying to tweak it to work with more than one column, but since i don't understand the code, it hasn't been successful yet.

The webpage where i found it is:

http://www.ozgrid.com/VBA/MiscVBA.htm

"Here's a handy little bit of code if you have a long list of data in 1 Column ("A" in this case). It will automatically move the range of data at each horizontal page break to adjacent columns."

Sub RowsToColumns()
Dim rCol As Range
Dim rCell1 As Range, rCell2 As Range
Dim i As Integer, iPBcount As Integer
Dim Sht As Worksheet

Application.StatusBar = "Converting, please wait....!"
Application.ScreenUpdating = False

'Set range variable to Selection
Set Sht = ActiveSheet
Set rCol = Sht.UsedRange.Columns(1)
'Insert page breaks
Sht.PageSetup.PrintArea = ""
Sht.PageSetup.Zoom = 100
ActiveWindow.View = xlPageBreakPreview

'Count only horizontal page breaks and pass to an Integer
iPBcount = Sht.HPageBreaks.Count

On Error Resume Next
'Loop as many times as there horizontal page breaks.
For i = 1 To iPBcount
'Set variable 1 to page break X
Set rCell1 = Sht.HPageBreaks(i).Location
'Set variable 2 to X page break
Set rCell2 = Sht.HPageBreaks(i + 1).Location.Offset(-1, 0)
If rCell2 Is Nothing Then 'Last page break
Range(rCell1, rCol.Cells(65536, 1).End(xlUp)).Cut _
Destination:=Sht.Cells(1, i + 1)
Else
Range(rCell1, rCell2).Cut Destination:=Sht.Cells(1, i + 1)
End If
Set rCell1 = Nothing
Set rCell2 = Nothing
Next i
On Error GoTo 0

ActiveWindow.View = xlNormalView
Application.ScreenUpdating = True
Sht.DisplayPageBreaks = False
Application.Goto rCol.Cells(1, 1), True
Set rCol = Nothing
Application.StatusBar = ""
End Sub

 

[dawnd3]

Are you talking about Excel or Access. You said Access but mentioned cell references, like Excel. However, are you trying to do this in a report? If so, reports do have an ability to create columns. If this is not what you mean, please explain further. Thanks

Dawn

http://www.calliopeconsulting.net

 

[xlbo]

Well, if you just wanna put the data below row 200 in the next set of columns, all you need is:

sub moveit()
lRow = range("A65536&quot.end(xlup).row
Range("A201:M"&lRow).Cut
Range("O1&quot.Select
ActiveSheet.Paste
end sub Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[ruthcali]

Dawn,
You're right, i wrote Access, but meant to write Excel. sorry. But i'm actually using both. The data comes from Access and then gets dumped into Excel. And then in Excel i was trying to find a way to move the data.


Geoff,
Wow, that works great. Thanks!! it's amazing. it's just a few lines of codes, but it's so powerful and it's what i need. Thanks again!