I am trying to create a macro that will print out a row of information. This
macro will be embedded into an autoshape at the end of each row.

The Column Titles are in Row 2 from A2:O2
The information that I would like to print out is in A3:O3

The print would have the column title followed by the information from the
cell below it. (i.e.: A2 with A3's info to the right of the title, B2 with
B3's info to the right of it, etc. etc. up to O2 and O3) The macro would grab
the column titles and the information from the assigned row and print out to
the default printer.

in Cell P3 I have placed the clickable autoshape for the macro to be
embedded into.
I want to use this macro in every row there after still keeping Row A2:O2 as
the titles. Is this possible?

Hello Murph

Try using this code

Sub Print1()

Range("A2:O3").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

Cheers


--
robmeister
------------------------------------------------------------------------
robmeister's Profile: http://www.excelforum.com/member.php...o&userid=24364
View this thread: http://www.excelforum.com/showthread...hreadid=380230

In this solution the user will prompted for the row they wish to print.

Sub Transpose()
Dim Ans As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
Ans = InputBox("Enter row number to print.")
Columns("A:B").Insert
For Iloop = 1 To 15
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(Ans, Iloop + 2)
Next Iloop
Range("A1:B15").PrintOut Copies:=1, Collate:=True
Columns("A:B").Delete
Application.ScreenUpdating = False
End Sub

--
Ken Hudson


"Murph" wrote:

I am trying to create a macro that will print out a row of information. This
macro will be embedded into an autoshape at the end of each row.

The Column Titles are in Row 2 from A2:O2
The information that I would like to print out is in A3:O3

The print would have the column title followed by the information from the
cell below it. (i.e.: A2 with A3's info to the right of the title, B2 with
B3's info to the right of it, etc. etc. up to O2 and O3) The macro would grab
the column titles and the information from the assigned row and print out to
the default printer.

in Cell P3 I have placed the clickable autoshape for the macro to be
embedded into.
I want to use this macro in every row there after still keeping Row A2:O2 as
the titles. Is this possible?

Ken this is perfect - thanks.

Follow up question - I only want up to row F to print in the group; so A:F
and then row L:O to print can I eliminate the rows I do not want and maintain
the same look?



"Ken Hudson" wrote:

In this solution the user will prompted for the row they wish to print.

Sub Transpose()
Dim Ans As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
Ans = InputBox("Enter row number to print.")
Columns("A:B").Insert
For Iloop = 1 To 15
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(Ans, Iloop + 2)
Next Iloop
Range("A1:B15").PrintOut Copies:=1, Collate:=True
Columns("A:B").Delete
Application.ScreenUpdating = False
End Sub

--
Ken Hudson


"Murph" wrote:

I am trying to create a macro that will print out a row of information. This
macro will be embedded into an autoshape at the end of each row.

The Column Titles are in Row 2 from A2:O2
The information that I would like to print out is in A3:O3

The print would have the column title followed by the information from the
cell below it. (i.e.: A2 with A3's info to the right of the title, B2 with
B3's info to the right of it, etc. etc. up to O2 and O3) The macro would grab
the column titles and the information from the assigned row and print out to
the default printer.

in Cell P3 I have placed the clickable autoshape for the macro to be
embedded into.
I want to use this macro in every row there after still keeping Row A2:O2 as
the titles. Is this possible?

Are you asking to print only columns A:F? If so, change:

For ILoop = 1 to 15
to
For ILoop = 1 to 6

and change:

Range("A1:B15").PrintOut Copies:=1, Collate:=True
to
Range("A1:B6").PrintOut Copies:=1, Collate:=True

--
Ken Hudson


"Murph" wrote:

Ken this is perfect - thanks.

Follow up question - I only want up to row F to print in the group; so A:F
and then row L:O to print can I eliminate the rows I do not want and maintain
the same look?



"Ken Hudson" wrote:

In this solution the user will prompted for the row they wish to print.

Sub Transpose()
Dim Ans As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
Ans = InputBox("Enter row number to print.")
Columns("A:B").Insert
For Iloop = 1 To 15
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(Ans, Iloop + 2)
Next Iloop
Range("A1:B15").PrintOut Copies:=1, Collate:=True
Columns("A:B").Delete
Application.ScreenUpdating = False
End Sub

--
Ken Hudson


"Murph" wrote:

I am trying to create a macro that will print out a row of information. This
macro will be embedded into an autoshape at the end of each row.

The Column Titles are in Row 2 from A2:O2
The information that I would like to print out is in A3:O3

The print would have the column title followed by the information from the
cell below it. (i.e.: A2 with A3's info to the right of the title, B2 with
B3's info to the right of it, etc. etc. up to O2 and O3) The macro would grab
the column titles and the information from the assigned row and print out to
the default printer.

in Cell P3 I have placed the clickable autoshape for the macro to be
embedded into.
I want to use this macro in every row there after still keeping Row A2:O2 as
the titles. Is this possible?

Thanks for the response -

I'm asking to print columns A:F and then columns L:O; skipping the
information from G:K

So I was wondering how to do that,
"For ILoop = 1 to 6:12 to 15" ??

Thanks for your help I really appreciate it




"Ken Hudson" wrote:

Are you asking to print only columns A:F? If so, change:

For ILoop = 1 to 15
to
For ILoop = 1 to 6

and change:

Range("A1:B15").PrintOut Copies:=1, Collate:=True
to
Range("A1:B6").PrintOut Copies:=1, Collate:=True

--
Ken Hudson


"Murph" wrote:

Ken this is perfect - thanks.

Follow up question - I only want up to row F to print in the group; so A:F
and then row L:O to print can I eliminate the rows I do not want and maintain
the same look?



"Ken Hudson" wrote:

In this solution the user will prompted for the row they wish to print.

Sub Transpose()
Dim Ans As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
Ans = InputBox("Enter row number to print.")
Columns("A:B").Insert
For Iloop = 1 To 15
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(Ans, Iloop + 2)
Next Iloop
Range("A1:B15").PrintOut Copies:=1, Collate:=True
Columns("A:B").Delete
Application.ScreenUpdating = False
End Sub

--
Ken Hudson


"Murph" wrote:

I am trying to create a macro that will print out a row of information. This
macro will be embedded into an autoshape at the end of each row.

The Column Titles are in Row 2 from A2:O2
The information that I would like to print out is in A3:O3

The print would have the column title followed by the information from the
cell below it. (i.e.: A2 with A3's info to the right of the title, B2 with
B3's info to the right of it, etc. etc. up to O2 and O3) The macro would grab
the column titles and the information from the assigned row and print out to
the default printer.

in Cell P3 I have placed the clickable autoshape for the macro to be
embedded into.
I want to use this macro in every row there after still keeping Row A2:O2 as
the titles. Is this possible?

Hi Murph,

We need to set up another loop to do that....

Option Explicit
Sub Transpose()
Dim Ans As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
Ans = InputBox("Enter row number to print.")
Columns("A:B").Insert
For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(Ans, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(Ans, Iloop + 2)
Next Iloop
Range("A1:B10").PrintOut Copies:=1, Collate:=True
Columns("A:B").Delete
Application.ScreenUpdating = False
End Sub

--
Ken Hudson


"Murph" wrote:

Thanks for the response -

I'm asking to print columns A:F and then columns L:O; skipping the
information from G:K

So I was wondering how to do that,
"For ILoop = 1 to 6:12 to 15" ??

Thanks for your help I really appreciate it




"Ken Hudson" wrote:

Are you asking to print only columns A:F? If so, change:

For ILoop = 1 to 15
to
For ILoop = 1 to 6

and change:

Range("A1:B15").PrintOut Copies:=1, Collate:=True
to
Range("A1:B6").PrintOut Copies:=1, Collate:=True

--
Ken Hudson


"Murph" wrote:

Ken this is perfect - thanks.

Follow up question - I only want up to row F to print in the group; so A:F
and then row L:O to print can I eliminate the rows I do not want and maintain
the same look?



"Ken Hudson" wrote:

In this solution the user will prompted for the row they wish to print.

Sub Transpose()
Dim Ans As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
Ans = InputBox("Enter row number to print.")
Columns("A:B").Insert
For Iloop = 1 To 15
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(Ans, Iloop + 2)
Next Iloop
Range("A1:B15").PrintOut Copies:=1, Collate:=True
Columns("A:B").Delete
Application.ScreenUpdating = False
End Sub

--
Ken Hudson


"Murph" wrote:

I am trying to create a macro that will print out a row of information. This
macro will be embedded into an autoshape at the end of each row.

The Column Titles are in Row 2 from A2:O2
The information that I would like to print out is in A3:O3

The print would have the column title followed by the information from the
cell below it. (i.e.: A2 with A3's info to the right of the title, B2 with
B3's info to the right of it, etc. etc. up to O2 and O3) The macro would grab
the column titles and the information from the assigned row and print out to
the default printer.

in Cell P3 I have placed the clickable autoshape for the macro to be
embedded into.
I want to use this macro in every row there after still keeping Row A2:O2 as
the titles. Is this possible?

Ken -

Again thanks, this macro is working well and I appreciate all your time.

Can I ask one or two more things...

the current macro appears like this (see below), I removed the portion
asking for the "Row to print", and simply assigned a cell number.

My 1st question is in regards to having the cell number assigned to the "B"
column change automatically as I paste this macro into different cells (321
cells in all, all in numerical order). I envision this happening as if I were
cutting and pasting a formula from one cell to another where it assumes the
correct formula.
Cells(Iloop, "B") = Cells(3, Iloop + 2) changing to:
Cells(Iloop,"B") = Cells(4,Iloop +2) then too .... Cells(5, Iloop +
2).. etc. etc.
Can a $ sign be used? or do I have to make 321 different macros each with
the specific cell number?

2nd question: for cell B10 I want the font to be a barcode, when assigning
this into columns it appears I lost the font.
How do I assign that cell (B10) to incorporate the font "free 3 of 9"?
Again thank you for your time - if I need to post this on the main board and
not directly to you please feel free to inform me.

Sub Barcode()
Dim Iloop As Integer
Application.ScreenUpdating = False
Columns("A:B").Insert
For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(3, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(3, Iloop + 2)
Next Iloop

With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight * 2
End With
With Worksheets("Counts").Columns("A")
.ColumnWidth = .ColumnWidth * 3
End With
With Worksheets("Counts").Columns("B")
.ColumnWidth = .ColumnWidth * 4
End With
With Worksheets("Counts").Range("A1:B9")
.Font.Size = 20
With Worksheets("Counts").Range("B10")
.Font.Size = 50
End With
End With

Range("A1:B15").PrintOut Copies:=1, Collate:=True
With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight / 2
End With
Columns("A:B").Delete

Application.ScreenUpdating = False
End Sub


"Ken Hudson" wrote:

Hi Murph,

We need to set up another loop to do that....

Option Explicit
Sub Transpose()
Dim Ans As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
Ans = InputBox("Enter row number to print.")
Columns("A:B").Insert
For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(Ans, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(Ans, Iloop + 2)
Next Iloop
Range("A1:B10").PrintOut Copies:=1, Collate:=True
Columns("A:B").Delete
Application.ScreenUpdating = False
End Sub

--
Ken Hudson


"Murph" wrote:

Thanks for the response -

I'm asking to print columns A:F and then columns L:O; skipping the
information from G:K

So I was wondering how to do that,
"For ILoop = 1 to 6:12 to 15" ??

Thanks for your help I really appreciate it




"Ken Hudson" wrote:

Are you asking to print only columns A:F? If so, change:

For ILoop = 1 to 15
to
For ILoop = 1 to 6

and change:

Range("A1:B15").PrintOut Copies:=1, Collate:=True
to
Range("A1:B6").PrintOut Copies:=1, Collate:=True

--
Ken Hudson


"Murph" wrote:

Ken this is perfect - thanks.

Follow up question - I only want up to row F to print in the group; so A:F
and then row L:O to print can I eliminate the rows I do not want and maintain
the same look?



"Ken Hudson" wrote:

In this solution the user will prompted for the row they wish to print.

Sub Transpose()
Dim Ans As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
Ans = InputBox("Enter row number to print.")
Columns("A:B").Insert
For Iloop = 1 To 15
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(Ans, Iloop + 2)
Next Iloop
Range("A1:B15").PrintOut Copies:=1, Collate:=True
Columns("A:B").Delete
Application.ScreenUpdating = False
End Sub

--
Ken Hudson


"Murph" wrote:

I am trying to create a macro that will print out a row of information. This
macro will be embedded into an autoshape at the end of each row.

The Column Titles are in Row 2 from A2:O2
The information that I would like to print out is in A3:O3

The print would have the column title followed by the information from the
cell below it. (i.e.: A2 with A3's info to the right of the title, B2 with
B3's info to the right of it, etc. etc. up to O2 and O3) The macro would grab
the column titles and the information from the assigned row and print out to
the default printer.

in Cell P3 I have placed the clickable autoshape for the macro to be
embedded into.
I want to use this macro in every row there after still keeping Row A2:O2 as
the titles. Is this possible?

Hi Murph,

I inserted a variable named "ActRow" that will hold the row number when you
click on a cell at the end of a row. That should make the macro "generic",
i.e. it will work when you click on any cell in a row.

I don't understand your formatting question. The macro inserts columns A and
B temporarily to hold the data that you want to print. At the end of the
macro, we delete rows A and B. You have added some formatting of A and B
before they are deleted. Therefore, when they are deleted, the formatting is
lost. Did you want to format them after the temporaary rows are deleted?

Option Explicit
Sub Barcode()
Dim ActRow As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
ActRow = ActiveCell.Row
Columns("A:B").Insert
For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(ActRow, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(ActRow, Iloop + 2)
Next Iloop

With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight * 2
End With
With Worksheets("Counts").Columns("A")
.ColumnWidth = .ColumnWidth * 3
End With
With Worksheets("Counts").Columns("B")
.ColumnWidth = .ColumnWidth * 4
End With
With Worksheets("Counts").Range("A1:B9")
.Font.Size = 20
With Worksheets("Counts").Range("B10")
.Font.Size = 50
End With
End With

Range("A1:B15").PrintOut Copies:=1, Collate:=True
With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight / 2
End With
Columns("A:B").Delete

Application.ScreenUpdating = True
End Sub
--
Ken Hudson


"Murph" wrote:

Ken -

Again thanks, this macro is working well and I appreciate all your time.

Can I ask one or two more things...

the current macro appears like this (see below), I removed the portion
asking for the "Row to print", and simply assigned a cell number.

My 1st question is in regards to having the cell number assigned to the "B"
column change automatically as I paste this macro into different cells (321
cells in all, all in numerical order). I envision this happening as if I were
cutting and pasting a formula from one cell to another where it assumes the
correct formula.
Cells(Iloop, "B") = Cells(3, Iloop + 2) changing to:
Cells(Iloop,"B") = Cells(4,Iloop +2) then too .... Cells(5, Iloop +
2).. etc. etc.
Can a $ sign be used? or do I have to make 321 different macros each with
the specific cell number?

2nd question: for cell B10 I want the font to be a barcode, when assigning
this into columns it appears I lost the font.
How do I assign that cell (B10) to incorporate the font "free 3 of 9"?
Again thank you for your time - if I need to post this on the main board and
not directly to you please feel free to inform me.

Sub Barcode()
Dim Iloop As Integer
Application.ScreenUpdating = False
Columns("A:B").Insert
For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(3, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(3, Iloop + 2)
Next Iloop

With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight * 2
End With
With Worksheets("Counts").Columns("A")
.ColumnWidth = .ColumnWidth * 3
End With
With Worksheets("Counts").Columns("B")
.ColumnWidth = .ColumnWidth * 4
End With
With Worksheets("Counts").Range("A1:B9")
.Font.Size = 20
With Worksheets("Counts").Range("B10")
.Font.Size = 50
End With
End With

Range("A1:B15").PrintOut Copies:=1, Collate:=True
With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight / 2
End With
Columns("A:B").Delete

Application.ScreenUpdating = False
End Sub


"Ken Hudson" wrote:

Hi Murph,

We need to set up another loop to do that....

Option Explicit
Sub Transpose()
Dim Ans As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
Ans = InputBox("Enter row number to print.")
Columns("A:B").Insert
For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(Ans, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(Ans, Iloop + 2)
Next Iloop
Range("A1:B10").PrintOut Copies:=1, Collate:=True
Columns("A:B").Delete
Application.ScreenUpdating = False
End Sub

--
Ken Hudson


"Murph" wrote:

Thanks for the response -

I'm asking to print columns A:F and then columns L:O; skipping the
information from G:K

So I was wondering how to do that,
"For ILoop = 1 to 6:12 to 15" ??

Thanks for your help I really appreciate it




"Ken Hudson" wrote:

Are you asking to print only columns A:F? If so, change:

For ILoop = 1 to 15
to
For ILoop = 1 to 6

and change:

Range("A1:B15").PrintOut Copies:=1, Collate:=True
to
Range("A1:B6").PrintOut Copies:=1, Collate:=True

--
Ken Hudson


"Murph" wrote:

Ken this is perfect - thanks.

Follow up question - I only want up to row F to print in the group; so A:F
and then row L:O to print can I eliminate the rows I do not want and maintain
the same look?



"Ken Hudson" wrote:

In this solution the user will prompted for the row they wish to print.

Sub Transpose()
Dim Ans As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
Ans = InputBox("Enter row number to print.")
Columns("A:B").Insert
For Iloop = 1 To 15
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(Ans, Iloop + 2)
Next Iloop
Range("A1:B15").PrintOut Copies:=1, Collate:=True
Columns("A:B").Delete
Application.ScreenUpdating = False
End Sub

--
Ken Hudson


"Murph" wrote:

I am trying to create a macro that will print out a row of information. This
macro will be embedded into an autoshape at the end of each row.

The Column Titles are in Row 2 from A2:O2
The information that I would like to print out is in A3:O3

The print would have the column title followed by the information from the
cell below it. (i.e.: A2 with A3's info to the right of the title, B2 with
B3's info to the right of it, etc. etc. up to O2 and O3) The macro would grab
the column titles and the information from the assigned row and print out to
the default printer.

in Cell P3 I have placed the clickable autoshape for the macro to be
embedded into.
I want to use this macro in every row there after still keeping Row A2:O2 as
the titles. Is this possible?

"ActRow" worked perfectly

the last row of information Row O uses a specific font called "3 of 9", it
is a barcode font.
What is happening when the information is being placed into Columns A & B is
the information in Row O is being converted to Arial. The cell with the
barcode is "B10", once the macro has been enabled.
I was trying to change the format of cell "B10" to just use the font "3 of
9" is that possible?

"Ken Hudson" wrote:

Hi Murph,

I inserted a variable named "ActRow" that will hold the row number when you
click on a cell at the end of a row. That should make the macro "generic",
i.e. it will work when you click on any cell in a row.

I don't understand your formatting question. The macro inserts columns A and
B temporarily to hold the data that you want to print. At the end of the
macro, we delete rows A and B. You have added some formatting of A and B
before they are deleted. Therefore, when they are deleted, the formatting is
lost. Did you want to format them after the temporaary rows are deleted?

Option Explicit
Sub Barcode()
Dim ActRow As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
ActRow = ActiveCell.Row
Columns("A:B").Insert
For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(ActRow, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(ActRow, Iloop + 2)
Next Iloop

With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight * 2
End With
With Worksheets("Counts").Columns("A")
.ColumnWidth = .ColumnWidth * 3
End With
With Worksheets("Counts").Columns("B")
.ColumnWidth = .ColumnWidth * 4
End With
With Worksheets("Counts").Range("A1:B9")
.Font.Size = 20
With Worksheets("Counts").Range("B10")
.Font.Size = 50
End With
End With

Range("A1:B15").PrintOut Copies:=1, Collate:=True
With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight / 2
End With
Columns("A:B").Delete

Application.ScreenUpdating = True
End Sub
--
Ken Hudson


"Murph" wrote:

Ken -

Again thanks, this macro is working well and I appreciate all your time.

Can I ask one or two more things...

the current macro appears like this (see below), I removed the portion
asking for the "Row to print", and simply assigned a cell number.

My 1st question is in regards to having the cell number assigned to the "B"
column change automatically as I paste this macro into different cells (321
cells in all, all in numerical order). I envision this happening as if I were
cutting and pasting a formula from one cell to another where it assumes the
correct formula.
Cells(Iloop, "B") = Cells(3, Iloop + 2) changing to:
Cells(Iloop,"B") = Cells(4,Iloop +2) then too .... Cells(5, Iloop +
2).. etc. etc.
Can a $ sign be used? or do I have to make 321 different macros each with
the specific cell number?

2nd question: for cell B10 I want the font to be a barcode, when assigning
this into columns it appears I lost the font.
How do I assign that cell (B10) to incorporate the font "free 3 of 9"?
Again thank you for your time - if I need to post this on the main board and
not directly to you please feel free to inform me.

Sub Barcode()
Dim Iloop As Integer
Application.ScreenUpdating = False
Columns("A:B").Insert
For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(3, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(3, Iloop + 2)
Next Iloop

With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight * 2
End With
With Worksheets("Counts").Columns("A")
.ColumnWidth = .ColumnWidth * 3
End With
With Worksheets("Counts").Columns("B")
.ColumnWidth = .ColumnWidth * 4
End With
With Worksheets("Counts").Range("A1:B9")
.Font.Size = 20
With Worksheets("Counts").Range("B10")
.Font.Size = 50
End With
End With

Range("A1:B15").PrintOut Copies:=1, Collate:=True
With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight / 2
End With
Columns("A:B").Delete

Application.ScreenUpdating = False
End Sub


"Ken Hudson" wrote:

Hi Murph,

We need to set up another loop to do that....

Option Explicit
Sub Transpose()
Dim Ans As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
Ans = InputBox("Enter row number to print.")
Columns("A:B").Insert
For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(Ans, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(Ans, Iloop + 2)
Next Iloop
Range("A1:B10").PrintOut Copies:=1, Collate:=True
Columns("A:B").Delete
Application.ScreenUpdating = False
End Sub

--
Ken Hudson


"Murph" wrote:

Thanks for the response -

I'm asking to print columns A:F and then columns L:O; skipping the
information from G:K

So I was wondering how to do that,
"For ILoop = 1 to 6:12 to 15" ??

Thanks for your help I really appreciate it




"Ken Hudson" wrote:

Are you asking to print only columns A:F? If so, change:

For ILoop = 1 to 15
to
For ILoop = 1 to 6

and change:

Range("A1:B15").PrintOut Copies:=1, Collate:=True
to
Range("A1:B6").PrintOut Copies:=1, Collate:=True

--
Ken Hudson


"Murph" wrote:

Ken this is perfect - thanks.

Follow up question - I only want up to row F to print in the group; so A:F
and then row L:O to print can I eliminate the rows I do not want and maintain
the same look?



"Ken Hudson" wrote:

In this solution the user will prompted for the row they wish to print.

Sub Transpose()
Dim Ans As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
Ans = InputBox("Enter row number to print.")
Columns("A:B").Insert
For Iloop = 1 To 15
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(Ans, Iloop + 2)
Next Iloop
Range("A1:B15").PrintOut Copies:=1, Collate:=True
Columns("A:B").Delete
Application.ScreenUpdating = False
End Sub

--
Ken Hudson


"Murph" wrote:

I am trying to create a macro that will print out a row of information. This
macro will be embedded into an autoshape at the end of each row.

The Column Titles are in Row 2 from A2:O2
The information that I would like to print out is in A3:O3

The print would have the column title followed by the information from the
cell below it. (i.e.: A2 with A3's info to the right of the title, B2 with
B3's info to the right of it, etc. etc. up to O2 and O3) The macro would grab
the column titles and the information from the assigned row and print out to
the default printer.

in Cell P3 I have placed the clickable autoshape for the macro to be
embedded into.
I want to use this macro in every row there after still keeping Row A2:O2 as
the titles. Is this possible?

Hi Murph,

Did you try-

Range("B10").Font.Name = "3 of 10 Barcode"

--
Ken Hudson


"Murph" wrote:

"ActRow" worked perfectly

the last row of information Row O uses a specific font called "3 of 9", it
is a barcode font.
What is happening when the information is being placed into Columns A & B is
the information in Row O is being converted to Arial. The cell with the
barcode is "B10", once the macro has been enabled.
I was trying to change the format of cell "B10" to just use the font "3 of
9" is that possible?

"Ken Hudson" wrote:

Hi Murph,

I inserted a variable named "ActRow" that will hold the row number when you
click on a cell at the end of a row. That should make the macro "generic",
i.e. it will work when you click on any cell in a row.

I don't understand your formatting question. The macro inserts columns A and
B temporarily to hold the data that you want to print. At the end of the
macro, we delete rows A and B. You have added some formatting of A and B
before they are deleted. Therefore, when they are deleted, the formatting is
lost. Did you want to format them after the temporaary rows are deleted?

Option Explicit
Sub Barcode()
Dim ActRow As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
ActRow = ActiveCell.Row
Columns("A:B").Insert
For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(ActRow, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(ActRow, Iloop + 2)
Next Iloop

With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight * 2
End With
With Worksheets("Counts").Columns("A")
.ColumnWidth = .ColumnWidth * 3
End With
With Worksheets("Counts").Columns("B")
.ColumnWidth = .ColumnWidth * 4
End With
With Worksheets("Counts").Range("A1:B9")
.Font.Size = 20
With Worksheets("Counts").Range("B10")
.Font.Size = 50
End With
End With

Range("A1:B15").PrintOut Copies:=1, Collate:=True
With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight / 2
End With
Columns("A:B").Delete

Application.ScreenUpdating = True
End Sub
--
Ken Hudson


"Murph" wrote:

Ken -

Again thanks, this macro is working well and I appreciate all your time.

Can I ask one or two more things...

the current macro appears like this (see below), I removed the portion
asking for the "Row to print", and simply assigned a cell number.

My 1st question is in regards to having the cell number assigned to the "B"
column change automatically as I paste this macro into different cells (321
cells in all, all in numerical order). I envision this happening as if I were
cutting and pasting a formula from one cell to another where it assumes the
correct formula.
Cells(Iloop, "B") = Cells(3, Iloop + 2) changing to:
Cells(Iloop,"B") = Cells(4,Iloop +2) then too .... Cells(5, Iloop +
2).. etc. etc.
Can a $ sign be used? or do I have to make 321 different macros each with
the specific cell number?

2nd question: for cell B10 I want the font to be a barcode, when assigning
this into columns it appears I lost the font.
How do I assign that cell (B10) to incorporate the font "free 3 of 9"?
Again thank you for your time - if I need to post this on the main board and
not directly to you please feel free to inform me.

Sub Barcode()
Dim Iloop As Integer
Application.ScreenUpdating = False
Columns("A:B").Insert
For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(3, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(3, Iloop + 2)
Next Iloop

With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight * 2
End With
With Worksheets("Counts").Columns("A")
.ColumnWidth = .ColumnWidth * 3
End With
With Worksheets("Counts").Columns("B")
.ColumnWidth = .ColumnWidth * 4
End With
With Worksheets("Counts").Range("A1:B9")
.Font.Size = 20
With Worksheets("Counts").Range("B10")
.Font.Size = 50
End With
End With

Range("A1:B15").PrintOut Copies:=1, Collate:=True
With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight / 2
End With
Columns("A:B").Delete

Application.ScreenUpdating = False
End Sub


"Ken Hudson" wrote:

Hi Murph,

We need to set up another loop to do that....

Option Explicit
Sub Transpose()
Dim Ans As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
Ans = InputBox("Enter row number to print.")
Columns("A:B").Insert
For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(Ans, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(Ans, Iloop + 2)
Next Iloop
Range("A1:B10").PrintOut Copies:=1, Collate:=True
Columns("A:B").Delete
Application.ScreenUpdating = False
End Sub

--
Ken Hudson


"Murph" wrote:

Thanks for the response -

I'm asking to print columns A:F and then columns L:O; skipping the
information from G:K

So I was wondering how to do that,
"For ILoop = 1 to 6:12 to 15" ??

Thanks for your help I really appreciate it




"Ken Hudson" wrote:

Are you asking to print only columns A:F? If so, change:

For ILoop = 1 to 15
to
For ILoop = 1 to 6

and change:

Range("A1:B15").PrintOut Copies:=1, Collate:=True
to
Range("A1:B6").PrintOut Copies:=1, Collate:=True

--
Ken Hudson


"Murph" wrote:

Ken this is perfect - thanks.

Follow up question - I only want up to row F to print in the group; so A:F
and then row L:O to print can I eliminate the rows I do not want and maintain
the same look?



"Ken Hudson" wrote:

In this solution the user will prompted for the row they wish to print.

Sub Transpose()
Dim Ans As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
Ans = InputBox("Enter row number to print.")
Columns("A:B").Insert
For Iloop = 1 To 15
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(Ans, Iloop + 2)
Next Iloop
Range("A1:B15").PrintOut Copies:=1, Collate:=True
Columns("A:B").Delete
Application.ScreenUpdating = False
End Sub

--
Ken Hudson


"Murph" wrote:

I am trying to create a macro that will print out a row of information. This
macro will be embedded into an autoshape at the end of each row.

The Column Titles are in Row 2 from A2:O2
The information that I would like to print out is in A3:O3

The print would have the column title followed by the information from the
cell below it. (i.e.: A2 with A3's info to the right of the title, B2 with
B3's info to the right of it, etc. etc. up to O2 and O3) The macro would grab
the column titles and the information from the assigned row and print out to
the default printer.

in Cell P3 I have placed the clickable autoshape for the macro to be
embedded into.
I want to use this macro in every row there after still keeping Row A2:O2 as
the titles. Is this possible?

Actually yea.. then I noticed.. well nevermind.. it worked.

I again wish to thank you for taking the time for helping me with this
macro. It has been quite the crash course but I feel I now have an idea of
just what it takes to make a macro perform.

Again, thank you

"Ken Hudson" wrote:

Hi Murph,

Did you try-

Range("B10").Font.Name = "3 of 10 Barcode"

--
Ken Hudson


"Murph" wrote:

"ActRow" worked perfectly

the last row of information Row O uses a specific font called "3 of 9", it
is a barcode font.
What is happening when the information is being placed into Columns A & B is
the information in Row O is being converted to Arial. The cell with the
barcode is "B10", once the macro has been enabled.
I was trying to change the format of cell "B10" to just use the font "3 of
9" is that possible?

"Ken Hudson" wrote:

Hi Murph,

I inserted a variable named "ActRow" that will hold the row number when you
click on a cell at the end of a row. That should make the macro "generic",
i.e. it will work when you click on any cell in a row.

I don't understand your formatting question. The macro inserts columns A and
B temporarily to hold the data that you want to print. At the end of the
macro, we delete rows A and B. You have added some formatting of A and B
before they are deleted. Therefore, when they are deleted, the formatting is
lost. Did you want to format them after the temporaary rows are deleted?

Option Explicit
Sub Barcode()
Dim ActRow As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
ActRow = ActiveCell.Row
Columns("A:B").Insert
For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(ActRow, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(ActRow, Iloop + 2)
Next Iloop

With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight * 2
End With
With Worksheets("Counts").Columns("A")
.ColumnWidth = .ColumnWidth * 3
End With
With Worksheets("Counts").Columns("B")
.ColumnWidth = .ColumnWidth * 4
End With
With Worksheets("Counts").Range("A1:B9")
.Font.Size = 20
With Worksheets("Counts").Range("B10")
.Font.Size = 50
End With
End With

Range("A1:B15").PrintOut Copies:=1, Collate:=True
With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight / 2
End With
Columns("A:B").Delete

Application.ScreenUpdating = True
End Sub
--
Ken Hudson


"Murph" wrote:

Ken -

Again thanks, this macro is working well and I appreciate all your time.

Can I ask one or two more things...

the current macro appears like this (see below), I removed the portion
asking for the "Row to print", and simply assigned a cell number.

My 1st question is in regards to having the cell number assigned to the "B"
column change automatically as I paste this macro into different cells (321
cells in all, all in numerical order). I envision this happening as if I were
cutting and pasting a formula from one cell to another where it assumes the
correct formula.
Cells(Iloop, "B") = Cells(3, Iloop + 2) changing to:
Cells(Iloop,"B") = Cells(4,Iloop +2) then too .... Cells(5, Iloop +
2).. etc. etc.
Can a $ sign be used? or do I have to make 321 different macros each with
the specific cell number?

2nd question: for cell B10 I want the font to be a barcode, when assigning
this into columns it appears I lost the font.
How do I assign that cell (B10) to incorporate the font "free 3 of 9"?
Again thank you for your time - if I need to post this on the main board and
not directly to you please feel free to inform me.

Sub Barcode()
Dim Iloop As Integer
Application.ScreenUpdating = False
Columns("A:B").Insert
For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(3, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(3, Iloop + 2)
Next Iloop

With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight * 2
End With
With Worksheets("Counts").Columns("A")
.ColumnWidth = .ColumnWidth * 3
End With
With Worksheets("Counts").Columns("B")
.ColumnWidth = .ColumnWidth * 4
End With
With Worksheets("Counts").Range("A1:B9")
.Font.Size = 20
With Worksheets("Counts").Range("B10")
.Font.Size = 50
End With
End With

Range("A1:B15").PrintOut Copies:=1, Collate:=True
With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight / 2
End With
Columns("A:B").Delete

Application.ScreenUpdating = False
End Sub


"Ken Hudson" wrote:

Hi Murph,

We need to set up another loop to do that....

Option Explicit
Sub Transpose()
Dim Ans As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
Ans = InputBox("Enter row number to print.")
Columns("A:B").Insert
For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(Ans, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(Ans, Iloop + 2)
Next Iloop
Range("A1:B10").PrintOut Copies:=1, Collate:=True
Columns("A:B").Delete
Application.ScreenUpdating = False
End Sub

--
Ken Hudson


"Murph" wrote:

Thanks for the response -

I'm asking to print columns A:F and then columns L:O; skipping the
information from G:K

So I was wondering how to do that,
"For ILoop = 1 to 6:12 to 15" ??

Thanks for your help I really appreciate it




"Ken Hudson" wrote:

Are you asking to print only columns A:F? If so, change:

For ILoop = 1 to 15
to
For ILoop = 1 to 6

and change:

Range("A1:B15").PrintOut Copies:=1, Collate:=True
to
Range("A1:B6").PrintOut Copies:=1, Collate:=True

--
Ken Hudson


"Murph" wrote:

Ken this is perfect - thanks.

Follow up question - I only want up to row F to print in the group; so A:F
and then row L:O to print can I eliminate the rows I do not want and maintain
the same look?



"Ken Hudson" wrote:

In this solution the user will prompted for the row they wish to print.

Sub Transpose()
Dim Ans As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
Ans = InputBox("Enter row number to print.")
Columns("A:B").Insert
For Iloop = 1 To 15
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(Ans, Iloop + 2)
Next Iloop
Range("A1:B15").PrintOut Copies:=1, Collate:=True
Columns("A:B").Delete
Application.ScreenUpdating = False
End Sub

--
Ken Hudson


"Murph" wrote:

I am trying to create a macro that will print out a row of information. This
macro will be embedded into an autoshape at the end of each row.

The Column Titles are in Row 2 from A2:O2
The information that I would like to print out is in A3:O3

The print would have the column title followed by the information from the
cell below it. (i.e.: A2 with A3's info to the right of the title, B2 with
B3's info to the right of it, etc. etc. up to O2 and O3) The macro would grab
the column titles and the information from the assigned row and print out to
the default printer.

in Cell P3 I have placed the clickable autoshape for the macro to be
embedded into.
I want to use this macro in every row there after still keeping Row A2:O2 as
the titles. Is this possible?