Excel 2010-Now
Excel 2007
Excel 2003
Tips & Tricks
Excel Forum
Course Index CIG Excel Book
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
Home > TechHelp > Directory > Excel > Copy Column Row Sizes < Excel Automation 2 | Flowcharts >
Back to Copy Column Row Sizes    Comments List
Pinned    Upload Images   Link   Email  
Transcript
Richard Rost 
          
2 months ago
Welcome to another TechHelp video brought to you by ExcelLearningZone.com. I'm your instructor, Richard Rost. In today's video, I'm going to show you how to copy and paste a range of cells from one sheet to another and preserve their row and column sizes. We're going to talk about it for the beginners first using the entire sheet and then, for the developers, we're going to do a little VBA and I'm going to show you how to do this with just a part of the sheet, which is really cool. So here we go.

Today's question comes from John in Graham, Washington, one of my platinum members. John says, "I need to copy data from one Excel sheet to another on the source sheet. I've set the row heights and the column widths the way I want. But when I copy the data to a new sheet, those sizes don't carry over. Is there a way to copy and paste data in Excel while keeping both the row heights and column widths? It works if I copy the entire sheet, but sometimes I only want part of it."

Alright, let's see an example. Here I've got a sample spreadsheet set up with some customers: Customer ID, first name, last name, and some notes. The notes I've got these rows and columns set exactly the way I want. Some of the notes are really long, as you can see here for John Luke. But I only want to see the first five rows. Okay, so this is the exact heights and widths that I want this stuff to be. Now, how do I copy and paste this over on sheet 2?

Well, if I select the cells like this and then Ctrl C to copy, come over to sheet 2 and then I'll hit paste, it comes in like that. Alright, the row heights are all messed up, and the column widths are all uniform. Oh, I don't like that at all. Alright, undo, Ctrl Z. Back to sheet 1, let's try selecting just the rows. Come over here, I'll click on the row headers, 1 through 7, copy with Ctrl C, come back over to sheet 2, and then I'll click on A1 and paste. And, okay, alright, I got the row heights, but I didn't get the column widths. So that's not good, undo.

How about if we try copying the columns? Let's select the columns, A through D. Ctrl C, now I've got the columns selected. Come over to sheet 2 and let's paste those in. Okay, now I got the column widths, but I didn't get the row heights. It's trying to fit everything from each one. And as you can see here, John looks really big. I don't want all that, I just want the first five rows of text. Alright, undo that.

So is there a way I can select the row heights and the column widths? Well, if we click right there where the headers meet, the row header and the column header, and then copy with Ctrl C, now I've got the whole sheet selected. Now I can come over to sheet 2 and then paste, and there we go. And I copied the row heights and the column widths from sheet 1.

If that's all you want to do, if you want to make an exact copy of the sheet, all you have to do is right-click and pick Move or Copy. That'll make a copy of the entire sheet. But what if what you really want to do is, instead of copying the entire sheet, you only want a piece of it? Let's say you only want Deanna Troy and Jean Luc Picard. If I copy those and come over here and paste them again, I don't get the dimensions right. So let's undo that.

What you really want is to get both dimensions, but only on parts of the sheet. You're going to need a little VBA. Now, if you've never done any Excel VBA programming before, pause the video now, go watch this other video. It's free, it's on my website, it's on my YouTube channel. It's less than 20 minutes long and will teach you everything you need to know to get started programming in Excel VBA. So go watch that and then come on back.

Alright, so we're going to need a little code. I'm going to go up to my Developer tab. If you don't know where the Developer tab is, you didn't watch the other video, did you? I'm going to click on Visual Basic. We're going to insert a new module. So just come over here in this pane over here, this project explorer pane, right-click, insert module. That'll create a brand new module for us.

Now, I'm not going to sit here and make you watch me type all this code in. I'm just going to show you the code and then we'll go over it. Now, the code is in the code vault on my website. If you're a gold member, you can come in here and just click the Copy button and it'll copy it to your clipboard. Everybody else, if you're not a gold member, well, get to typing.

And there it is, there's a screenshot. Take a shot of it now. I'll scroll down in a second so you can get the rest of it. There it is, not too much code. Another reason to join, become a gold member. So I'm just going to paste that in here. There it is. And I think we got two options to explicit.

Now, I've got two subroutines that I created. One is called Copy Selected and the other one is called Paste Selected. Now, Copy Selected is pretty straightforward. It copies the selected range and what worksheet you're on. So it sets a variable called SourceRange equal to Selection. Selection means whatever you've got selected in the sheet. That's a special keyword called Selection.

Now, SourceRange and SourceWorksheet are up here. They're outside of these subroutines. That means they're global variables. So that way both of these subroutines can work with these variables here. Well, they're global as far as this module is concerned. They're module-level variables. So we're going to set the SourceRange equal to the selection of the SourceWorksheet, which is the SourceRange.Worksheet. In other words, the worksheet that you're on. This is important later. So that's how we copy that information.

Now, when we go to paste it, the first thing we're going to do is make sure we've got a SourceRange and a SourceWorksheet. So if the SourceRange is nothing, in other words, there's nothing in there, or the SourceWorksheet is nothing, inform the user with a message and say, "Hey, you didn't copy anything. Use CopyFirst," and then exit out of town.

Now, we're going to set a couple more variables. The destination cell is going to be the active cell. Active cell means where the selected cell currently is, wherever you're sitting, where you want to paste it. The destination worksheet is the destination cell's worksheet, so whatever worksheet you're on.

We're going to use SourceRange.copy DestinationCell. The copy property here, or the copy method, you could call it, can be used to copy data to a destination. The destination is the destination cell. So SourceRange that we set up here is going to be copied to the destination cell. Now, that just copies the data without any formatting.

Now that we've got that data there, we're going to set the column widths and the row heights. We're going to loop through all of the columns. L is a new variable. It's going to be from one to SourceRange.columns.count. That means how many columns are in that source range. How many columns are in here? One, two, three, four. That's going to loop from one to four. It's going to say the destination worksheet's columns and then what column we're on, .ColumnWidth equals the column width from the same column in the source range.

Then we'll do the same thing with the rows. For L equals one to SourceRange.Rows.Count, go down each row and set the heights accordingly. Makes sense? That's how this code works in a nutshell. Alright, let's give it a save, Ctrl S. Now it's going to say the following features cannot be saved in a macro-free workbook. VB project. I want you to put a module in your project. You have to save it as an XLSM file so it knows it's got a module or macro, whatever you want to call it. So save this.

Now we're going to come back out here. We're going to file save as, and we're going to save this as an XLSM macro-enabled workbook. Save it. Okay, now just having that code in there doesn't do much. We need to assign those macros, those VBA code subs, those subroutines. We need to assign those to keyboard shortcuts. I'm going to use Ctrl Shift C and Ctrl Shift P for paste. I know V is normally paste, but I've got something else on my system that I record with, set to Ctrl Shift V. So you can use V if you want to. I'm going to use P. That's just me.

So how do you do that? We're going to go to macros. In the macro window, you can see there's our two little subroutines we just created. Copy Selected: I'm going to go to options, and come over here and hit Shift C. That's going to make my macro key Ctrl Shift C. Hit OK. Then for paste, I'm going to go to options, and I'm going to go Shift P for mine. You can make it V if you prefer. Now those are both set.

Now I can close the macro window. Now I can select the rows just like this, select a range normally. Ctrl Shift C. I got a program with a beep in there; you heard the beep. Now I can go to a new sheet if I want to. I can click over here and go Ctrl Shift P. Look at that. It pasted it in right at that spot and preserved all of the heights and widths in that section.

Now the one downside is when you're using VBA code, there's no undo. You cannot Ctrl Z and undo that. Sorry. But we can just go to a sheet. Let's go to sheet 1. Let's say I want just Deanna and Picard. Ctrl Shift C. Go to sheet 3. Now I'm going to put it right there. Ctrl Shift P. Boom. There it is. I preserved the row heights and widths because I copied it, and it copied the cells and then reset the row columns and the row and column sizes with those loops.

The nice thing about this too is if you open up a new book, let's go to file and then open up a new blank book. As long as you're in the same instance of Excel, you can copy and paste and do stuff between these. How do you know if you're in the same instance? Look for the Switch Windows button in the View tab. If you see them both in here, right here's my book 5 and there's the customer samples. That means you're in the same instance of Excel. You can copy and paste and do stuff between these.

So I can come over here. I can say give me Riker and Malcolm Reynolds. Ctrl Shift C. Come over to this book and then Ctrl Shift P. And there they are. As long as you're in the same instance of Excel. Remember Excel used to have that multiple document interface if you're old like me. You have two sets of maximized, minimized, and close buttons. Well, they don't do that anymore. They just open up a new window, but it's still the same instance of Excel.

So there you go. That's how you do it. There's both the beginner and the developer ways to copy and paste a sheet or a range and keep the row and column settings sizes, whatever you want to call them. But that's going to do it. That's your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you next time.

I'm excited to announce that I'm creating a brand new series of lessons focused on programming in Microsoft Excel VBA. If you've been looking to take your Excel skills to the next level and learn how to automate tasks, write custom macros, and unleash the full power of Excel VBA, these lessons are for you. Sign up now on my website at the link shown. You'll find a copy in the description down below the video window and I'll send you more information on this exciting new series.

If you'd like to see me make more Excel TechHelp videos, post a comment down below and say I want more Excel. The vast majority of my videos are from Microsoft Access, the database program, because that's been my forte for the past three decades or so. However, I love Excel, and I'm more than happy to make more videos for Excel and Word and PowerPoint and all the other topics that I teach. As you know, the squeaky wheel gets the grease, so make your voice heard and let me know you want more Excel videos.

TOPICS:
Copying cells with row and column sizes  
Preserving row heights and column widths  
Copying partial sheet data  
VBA for copying specific ranges  
Using Developer tab in Excel  
Inserting a module in VBA  
Writing VBA subroutines  
Copying selected Excel range with VBA  
Pasting selected Excel range with VBA  
Setting global variables in VBA  
Using copy method in VBA  
Loop for column widths in VBA  
Loop for row heights in VBA  
Saving a macro-enabled workbook  
Assigning macros to keyboard shortcuts  
Using VBA without undo functionality  
Copy-pasting within the same Excel instance

COMMERCIAL:
In today's video, you'll learn how to copy and paste a range of Excel cells from one sheet to another while preserving row heights and column widths. We'll start with the basics for beginners, tackling the entire sheet. Then, we'll dive into VBA for developers, demonstrating how to copy only part of the sheet while maintaining dimensions. You'll see step-by-step code, keyboard shortcuts, and tips on managing Excel instances to streamline your workflow. This video is perfect for anyone looking to enhance their Excel skills, whether you're a novice or an experienced developer. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Copy Column Row Sizes.
 

 
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2025 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 2/11/2025 7:12:08 PM. PLT: 2s