Printing/Forms

Hi

I've been asked to present a suggestion for an issue we are having here.

There is a spreadsheet- 29 columns, with 188 individual rows.

I have to suggest a method for data entry- easy enough, I'll just show them
how to use the form function.

But more importantly, the data now is presented horizontally. ie if I were
to print it, the data for each row would print across the paper. is there
any way to keep the column headings where they are, but have the record print
the opposite way. (ie in excel there would be column headings, but on paper,
it would print verically and the column heading would now be a row heading.
ONe record per page.

EXCEL
====
NAME ADDRESS PROVINCE
ALLIE 4 LONDON ONTARIO

PRINT
====
NAME ALLIE
ADDRESS 4 LONDON
PROVINCE ONTARIO

Ideally, I would also create buttons so that I either launch a form to add
new data, OR to print a record.


I'm assuming I could probably create a macro to do it, but the question is
1- am I thinking correctly? is there an easier solution?
2-If I do have to do macros, am I capable? I really am a complete beginnner
when it comes to macros.



I hope I've presented that clearly.

Hi, Allie. Try Word Mail merge.
You can print any way you like.
**** Hope it helps! ****

~Dreamboat
Excel VBA Certification Coming Soon!
www.VBAExpress.com/training/
********************************
"Allie" wrote in message
...
Hi

I've been asked to present a suggestion for an issue we are having here.

There is a spreadsheet- 29 columns, with 188 individual rows.

I have to suggest a method for data entry- easy enough, I'll just show
them
how to use the form function.

But more importantly, the data now is presented horizontally. ie if I were
to print it, the data for each row would print across the paper. is there
any way to keep the column headings where they are, but have the record
print
the opposite way. (ie in excel there would be column headings, but on
paper,
it would print verically and the column heading would now be a row
heading.
ONe record per page.

EXCEL
====
NAME ADDRESS PROVINCE
ALLIE 4 LONDON ONTARIO

PRINT
====
NAME ALLIE
ADDRESS 4 LONDON
PROVINCE ONTARIO

Ideally, I would also create buttons so that I either launch a form to add
new data, OR to print a record.


I'm assuming I could probably create a macro to do it, but the question is
1- am I thinking correctly? is there an easier solution?
2-If I do have to do macros, am I capable? I really am a complete
beginnner
when it comes to macros.



I hope I've presented that clearly.

Thank you very much, but a mail merge isn't really an option. Because this
will be changed on an ongoing basis and used by computer novices, having to
do a word merge each time a change is made really isn't a good option.

"Anne Troy" wrote:

Hi, Allie. Try Word Mail merge.
You can print any way you like.
**** Hope it helps! ****

~Dreamboat
Excel VBA Certification Coming Soon!
www.VBAExpress.com/training/
********************************
"Allie" wrote in message
...
Hi

I've been asked to present a suggestion for an issue we are having here.

There is a spreadsheet- 29 columns, with 188 individual rows.

I have to suggest a method for data entry- easy enough, I'll just show
them
how to use the form function.

But more importantly, the data now is presented horizontally. ie if I were
to print it, the data for each row would print across the paper. is there
any way to keep the column headings where they are, but have the record
print
the opposite way. (ie in excel there would be column headings, but on
paper,
it would print verically and the column heading would now be a row
heading.
ONe record per page.

EXCEL
====
NAME ADDRESS PROVINCE
ALLIE 4 LONDON ONTARIO

PRINT
====
NAME ALLIE
ADDRESS 4 LONDON
PROVINCE ONTARIO

Ideally, I would also create buttons so that I either launch a form to add
new data, OR to print a record.


I'm assuming I could probably create a macro to do it, but the question is
1- am I thinking correctly? is there an easier solution?
2-If I do have to do macros, am I capable? I really am a complete
beginnner
when it comes to macros.



I hope I've presented that clearly.

I'd use a macro:

Option Explicit
Sub testme()

Dim curWks As Worksheet
Dim newWks As Worksheet
Dim oRow As Long
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim ColsToCopy As Long

Set curWks = ActiveSheet
Set newWks = Worksheets.Add

With curWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
ColsToCopy = .Cells(1, .Columns.Count).End(xlToLeft).Column
oRow = 1
For iRow = FirstRow To LastRow
newWks.Cells(oRow, "A").Resize(ColsToCopy, 1).Value _
= Application.Transpose(.Range("a1") _
.Resize(1, ColsToCopy).Value)

newWks.Cells(oRow, "B").Resize(ColsToCopy, 1).Value _
= Application.Transpose(.Cells(iRow, "A") _
.Resize(1, ColsToCopy).Value)

'oRow = oRow + ColsToCopy
'or
oRow = oRow + ColsToCopy + 1 'for an empty row between "records"

Next iRow
End With

With newWks
.UsedRange.Columns.AutoFit
End With

End Sub

====
But then I'd pick up the used cells in column A and B and copy them to MSWord.

From there, I could use Format|columns
to print multiple columns on the same piece of paper.

or you could put more records in column B, C, D, E, F, G, H (for instance):

Option Explicit
Sub testme()

Dim curWks As Worksheet
Dim newWks As Worksheet
Dim oRow As Long
Dim iRow As Long
Dim jRow As Long
Dim iCtr As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim ColsToCopy As Long
Dim HowManyPerSheet As Long

Set curWks = ActiveSheet
Set newWks = Worksheets.Add

With curWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
ColsToCopy = .Cells(1, .Columns.Count).End(xlToLeft).Column
HowManyPerSheet = 7 'columns B-H

oRow = 1
For iRow = FirstRow To LastRow Step 6
newWks.Cells(oRow, "A").Resize(ColsToCopy, 1).Value _
= Application.Transpose(.Range("a1") _
.Resize(1, ColsToCopy).Value)

iCtr = 1
For jRow = iRow To iRow + HowManyPerSheet - 1
iCtr = iCtr + 1
newWks.Cells(oRow, iCtr).Resize(ColsToCopy, 1).Value _
= Application.Transpose(.Cells(jRow, "A") _
.Resize(1, ColsToCopy).Value)
Next jRow

'oRow = oRow + ColsToCopy
'or
oRow = oRow + ColsToCopy + 1 'for an empty row between "records"

Next iRow
End With

With newWks
.UsedRange.Columns.AutoFit
End With

End Sub



Allie wrote:

Hi

I've been asked to present a suggestion for an issue we are having here.

There is a spreadsheet- 29 columns, with 188 individual rows.

I have to suggest a method for data entry- easy enough, I'll just show them
how to use the form function.

But more importantly, the data now is presented horizontally. ie if I were
to print it, the data for each row would print across the paper. is there
any way to keep the column headings where they are, but have the record print
the opposite way. (ie in excel there would be column headings, but on paper,
it would print verically and the column heading would now be a row heading.
ONe record per page.

EXCEL
====
NAME ADDRESS PROVINCE
ALLIE 4 LONDON ONTARIO

PRINT
====
NAME ALLIE
ADDRESS 4 LONDON
PROVINCE ONTARIO

Ideally, I would also create buttons so that I either launch a form to add
new data, OR to print a record.

I'm assuming I could probably create a macro to do it, but the question is
1- am I thinking correctly? is there an easier solution?
2-If I do have to do macros, am I capable? I really am a complete beginnner
when it comes to macros.

I hope I've presented that clearly.

--

Dave Peterson

Well I went for the easy and simple route.

Inserted a worksheet. Put in a field to enter an Item Number in.

Put the column headings in. And then did a bunch of vlookups to populate all
the data.

That way the data is presented horizontally, and if they print it, they only
get that data.

Such a simple solution!

"Allie" wrote:

Hi

I've been asked to present a suggestion for an issue we are having here.

There is a spreadsheet- 29 columns, with 188 individual rows.

I have to suggest a method for data entry- easy enough, I'll just show them
how to use the form function.

But more importantly, the data now is presented horizontally. ie if I were
to print it, the data for each row would print across the paper. is there
any way to keep the column headings where they are, but have the record print
the opposite way. (ie in excel there would be column headings, but on paper,
it would print verically and the column heading would now be a row heading.
ONe record per page.

EXCEL
====
NAME ADDRESS PROVINCE
ALLIE 4 LONDON ONTARIO

PRINT
====
NAME ALLIE
ADDRESS 4 LONDON
PROVINCE ONTARIO

Ideally, I would also create buttons so that I either launch a form to add
new data, OR to print a record.


I'm assuming I could probably create a macro to do it, but the question is
1- am I thinking correctly? is there an easier solution?
2-If I do have to do macros, am I capable? I really am a complete beginnner
when it comes to macros.



I hope I've presented that clearly.