• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

How to print multiple related columns as continuous columns on one page|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / How to print multiple related columns as continuous columns on one page|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…How to print multiple related colum…
sp_PrintTopic sp_TopicIcon
How to print multiple related columns as continuous columns on one page
Avatar
Excel Rookie
Member
Members
Level 0
Forum Posts: 15
Member Since:
August 22, 2015
sp_UserOfflineSmall Offline
1
January 12, 2018 - 8:40 am
sp_Permalink sp_Print

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

sp_AnswersTopicSeeAnswer See Answer
Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
2
January 12, 2018 - 1:32 pm
sp_Permalink sp_Print

Why don't you just cut and paste into the same Excel sheet ?

It would only take you 1 minute to do that.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
3
January 12, 2018 - 4:40 pm
sp_Permalink sp_Print sp_EditHistory

Hi ER,

You can try this code, after you add a new sheet named Sheet2, the data for printing will be transferred there:

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(, 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.

Avatar
Excel Rookie
Member
Members
Level 0
Forum Posts: 15
Member Since:
August 22, 2015
sp_UserOfflineSmall Offline
4
January 13, 2018 - 3:04 am
sp_Permalink sp_Print

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.

sp_PlupAttachments Attachments
  • sp_PlupImage excel-columns-code-error.gif (38 KB)
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
5
January 13, 2018 - 4:11 am
sp_Permalink sp_Print

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.

Avatar
Excel Rookie
Member
Members
Level 0
Forum Posts: 15
Member Since:
August 22, 2015
sp_UserOfflineSmall Offline
6
January 13, 2018 - 9:16 am
sp_Permalink sp_Print

OMG Catalin - it worked brilliantly! Thank you very much. I am uploading the file to see your handiwork.SmileSmileSmile 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. 

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
7
January 13, 2018 - 1:34 pm
sp_Permalink sp_Print

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.

Avatar
Excel Rookie
Member
Members
Level 0
Forum Posts: 15
Member Since:
August 22, 2015
sp_UserOfflineSmall Offline
8
January 16, 2018 - 1:49 am
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
9
January 16, 2018 - 2:12 pm
sp_Permalink sp_Print

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

Avatar
Excel Rookie
Member
Members
Level 0
Forum Posts: 15
Member Since:
August 22, 2015
sp_UserOfflineSmall Offline
10
January 17, 2018 - 5:02 am
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
11
January 17, 2018 - 4:24 pm
sp_Permalink sp_Print

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

sp_AnswersTopicAnswer
Answers Post
Avatar
Excel Rookie
Member
Members
Level 0
Forum Posts: 15
Member Since:
August 22, 2015
sp_UserOfflineSmall Offline
12
January 19, 2018 - 12:52 am
sp_Permalink sp_Print

Hello Catalin,

You have my sincere thanks! This is a life-saver! Thank you.SmileSmileSmile

 

ER

Avatar
Excel Rookie
Member
Members
Level 0
Forum Posts: 15
Member Since:
August 22, 2015
sp_UserOfflineSmall Offline
13
March 14, 2018 - 12:49 am
sp_Permalink sp_Print

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

Avatar
Excel Rookie
Member
Members
Level 0
Forum Posts: 15
Member Since:
August 22, 2015
sp_UserOfflineSmall Offline
14
March 16, 2018 - 2:49 am
sp_Permalink sp_Print

Hello All

Can someone please have a look and reply?

Thank you.

ER

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
15
April 10, 2018 - 7:00 pm
sp_Permalink sp_Print

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.

Avatar
Excel Rookie
Member
Members
Level 0
Forum Posts: 15
Member Since:
August 22, 2015
sp_UserOfflineSmall Offline
16
April 12, 2018 - 2:09 am
sp_Permalink sp_Print

Hi Catalin,

Thank you for picking up this thread again.

Yes, it is tricky to get the formatting just right as the number of entries per range vary, and so I’ve changed the original format to two columns instead of three, and merged cells across the columns to identify the “range” that falls under the merged cells. If there is a prompt to manually enter the number of rows per page that might add some fixed structure for the code to work. Perhaps these changes might help revise the script?

Please look at the new sample workbook with the changes. The actually data in the output might be messed up, as this is only to show you the formatting output.

Thank you for your help,

ER

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: RAMEZ ATTAR, Jeanine Hagge, Tiffany Kang, Scot C
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 205
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Scot C
Othman AL MUTAIRI
Misael Gutierrez Sr.
Attif Ihsan
Kieran Fee
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Bhuwan Devkota
Kathryn Patton
Forum Stats:
Groups: 3
Forums: 24
Topics: 6222
Posts: 27293

 

Member Stats:
Guest Posters: 49
Members: 31917
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.