Hello All,
Happy 2018!
Is there a way to print related Excel columns as continuous columns on one page? I use a dynamic Excel workbook (I’m not the author), with three related columns (please see sample workbook). I need to print the three columns on one page (paper size/orientation should be flexible – please see sample document and note the column headers, merged cells, etc.). I am reluctant to copy and paste into Word each time there is an update. I Googled and came across a macro for this, but it can be used only for data listed as one long Excel column which then can be printed as multiple columns on a single page. My situation is that I have the three RELATED columns and they must follow each other as a referenced “cheatsheet”. Please note I need the column headers, merged cells, etc., but can edit/housekeep as needed.
Any suggestions?
Thank you.
ER
Why don't you just cut and paste into the same Excel sheet ?
It would only take you 1 minute to do that.
Hi ER,
You can try this code, after you add a new sheet named Sheet2, the data for printing will be transferred there:
Sub ReorganizeData()
Dim i As Long, CellAddress As String, AddressList As String, Result As Variant, BlocksPerColumn As Integer
Dim Dest As Worksheet, Col As Byte, Rw As Long
Set Dest = ThisWorkbook.Worksheets("Sheet2")
'collect the range addresses for each block of data
For i = 2 To Me.UsedRange.Rows.Count
CellAddress = Me.Cells(i, "A").MergeArea.Resize(, 3).Address
If InStr(AddressList, CellAddress) = 0 Then AddressList = AddressList & "," & CellAddress
Next
If Len(AddressList) > 0 Then AddressList = Right(AddressList, Len(AddressList) - 1)
Result = Split(AddressList, ",")
'we know how many blocks of data we have, calculate how many blocks will go into 1 column
BlocksPerColumn = (UBound(Result) + 1) 3 + 1
'clear old data
Dest.UsedRange.Clear
Col = 1
Me.Range("A1:C1").Copy Destination:=Dest.Cells(1, Col)
For i = 0 To UBound(Result)
Rw = Dest.Cells(Dest.Rows.Count, Col + 1).End(xlUp).Row + 1
Me.Range(Result(i)).Copy Destination:=Dest.Cells(Rw, Col)
If (i + 1) Mod BlocksPerColumn = 0 Then
Col = Col + 4
Me.Range("A1:C1").Copy Destination:=Dest.Cells(1, Col)
End If
Next
Dest.UsedRange.Columns.AutoFit
End Sub
The code is written for your specific structure from your sample file, will not work if you change the structure.
Excel Rookie said
Hello All,Happy 2018!
Is there a way to print related Excel columns as continuous columns on one page? I use a dynamic Excel workbook (I’m not the author), with three related columns (please see sample workbook). I need to print the three columns on one page (paper size/orientation should be flexible – please see sample document and note the column headers, merged cells, etc.). I am reluctant to copy and paste into Word each time there is an update. I Googled and came across a macro for this, but it can be used only for data listed as one long Excel column which then can be printed as multiple columns on a single page. My situation is that I have the three RELATED columns and they must follow each other as a referenced “cheatsheet”. Please note I need the column headers, merged cells, etc., but can edit/housekeep as needed.
Any suggestions?
Thank you.
ER
Sorry, I don't know how to respond individually to each reply so I'll address the responder by name.
To SunnyKow: Thank you for your response. I'm missing something, sorry. The original document is in Excel so there is no need for me to cut and paste back into Excel - if that is what you meant. The Word Document was a sample to show what I want the end result to be. It is a dynamic workbook (the data keeps getting updated, which messes up the rows, etc.).
To Catalin B: Thank you for the code. I'm getting the attached error.
Sorry, forgot to mention that the code should be copied into sheet1 module, not in a regular module.
"Me" keyword can only be used inside a worksheet module, or in thisWorkbook module, or in a userform.
Depending on where it is used, Me.Name for example returns the worksheet name, woirkbook name, or the form name. It's presence indicates that the code should not be placed in a regular module.
OMG Catalin - it worked brilliantly! Thank you very much. I am uploading the file to see your handiwork.
It looks just great!
I note in your earlier thread that this is only for the sample workbook. Would you be able to edit the code or write a new one to:
1. include more than three columns, or list the places in the code that must be edited to print multiple columns,
2. print columns that are hidden i.e. print on visible columns?
Would you like me to start a new post? I thought I'd check as I really think it is part of the same issue.
Thank you again.
As written in code already, the code calculates the number of blocks with data and divides this number by 3, to distribute the blocks, the code writes the blocks one under another. It can be changed easily to start writing the blocks to the right of the previous block, until the maximum indicated number of columns is reached, then it will continue in the next row.
Not sure what you mean by printing hidden columns. Are they hidden in the original sheet or in the sheet for printing? It should not mater if there are hidden columns in the source or in the destination sheet, and if you want to hide/unhide columns, that is easy.
You can upload a sample with an example of hidden columns so we can see your real structure.
Hello Catalin,
Thank you for your response and continued help.
I’ve uploaded a revised workbook. This workbook has Columns B (Groomer) and D (Person in Charge) hidden, and I’ve added one more column E (with wrapped text). So basically the final print should only show the visible Columns, A, C, E, and F, as columns in one page, and if possible with the same formatting (centered, wrapped, etc.).
Thank you again,
ER
Hi ER,
Here is the revised code:
Option Explicit
Sub ReorganizeData()
Dim i As Long, CellAddress As String, AddressList As String, Result As Variant, BlocksPerColumn As Integer
Dim Dest As Worksheet, Col As Byte, Rw As Long
Set Dest = ThisWorkbook.Worksheets("Sheet2")
'collect the range addresses for each block of data
For i = 2 To Me.UsedRange.Rows.Count
CellAddress = Me.Cells(i, "A").MergeArea.Resize(, 6).Address
If InStr(AddressList, CellAddress) = 0 Then AddressList = AddressList & "," & CellAddress
Next
If Len(AddressList) > 0 Then AddressList = Right(AddressList, Len(AddressList) - 1)
Result = Split(AddressList, ",")
'we know how many blocks of data we have, calculate how many blocks will go into 1 column
BlocksPerColumn = (UBound(Result) + 1) 3 + 1
'clear old data
Dest.UsedRange.Clear
Col = 1
Me.Range("A1:F1").SpecialCells(xlCellTypeVisible).Copy Destination:=Dest.Cells(1, Col)
For i = 0 To UBound(Result)
Rw = Dest.Cells(Dest.Rows.Count, Col + 1).End(xlUp).Row + 1
Me.Range(Result(i)).SpecialCells(xlCellTypeVisible).Copy Destination:=Dest.Cells(Rw, Col)
If (i + 1) Mod BlocksPerColumn = 0 Then
Col = Me.Cells.Find("*", Me.Cells(1), , , xlByColumns, xlPrevious).Column + 1
Me.Range("A1:F1").SpecialCells(xlCellTypeVisible).Copy Destination:=Dest.Cells(1, Col)
End If
Next
Dest.UsedRange.Columns.AutoFit
End Sub
Hi Catalin,
Thanks for the revision. This works a little bit - the required columns are added. However, it does not follow to the next block of columns - please see row Column H, Row 44 of the attached workbook. Does the macro need tweaking or is it something in my formatting?
ER
No,
Was just a bug, my fault.
Change this:
Col = Me.Cells.Find("*", Me.Cells(1), , , xlByColumns, xlPrevious).Column + 1
to:
Col = Dest.Cells.Find("*", Dest.Cells(1), , , xlByColumns, xlPrevious).Column + 2
Hello Catalin,
You have my sincere thanks! This is a life-saver! Thank you.
ER
Hello Catalin,
Can you please look at the sample file in print preview and revise the code? The cells of a “section” (by this I mean a group of related cells) don’t follow to the next block of columns but down to the next page. What I need is for a section to follow in a continuous manner to the top of the next block to the right. The number of rows in a section are different and can be split to the next column block. The merged cell name “Animal” should follow to the next block if possible, Also, there is a lot of spare “real estate” on each page, so the columns need to fill up the full page first before going to the next page. I’ve also attached an output needed file to show what the final page should look like.
Thank you.
ER
Hello All
Can someone please have a look and reply?
Thank you.
ER
Hi ER,
Sorry for the late reply.
Not saying it's impossible, but it is not easy to do that. How do you know when a page is "full'? It really depends on many things: selected printer, rows height.
We can loop through page breaks, horizontal and vertical, but there is a catch here, there can be automatic and manual page breaks.
Let's assume we identify how many rows and columns can fit into a "page", but it's not enough, as the row heights can be different on the same page.
To add more spices on this problem, you want in fact to make multiple combinations between existing blocks, UNTIL all columns have the same number of rows, without splitting a block. But, because the number of blocks do not have the same number of rows, it's getting too complicated and time consuming to build a solution.
The existing solution is based on counting the number of blocks, the number of blocks per column is calculated:
BlocksPerColumn = (UBound(Result) + 1) 3 + 1
The red 3 number represents the number of columns, you can put there any number of columns you want, as long as it's lower than the number of blocks.
