How can I copy cell contents, cell formats, row heights and column widths from one Excel 2007 tab to several other?

I found this Youtube video about the subject: https://www.youtube.com/watch?v=dhnsg-yYE3Y

And tried his method, but it only works for copying cell contents and cell formats to other tabs. The row heights and column widths stay the same.

So I wonder if there is any method to include also copying the row heights and column widths from a source tab to several other tabs?

I tried now also to select everything under the source tab, right-clicked and chose "Copy", then moved to the new tab to right-click and choose "Paste special...". But even when I choose "All" it doesn't copy the column widths and row heights, neither if I choose "Column widths".

Well, I could copy special the column widths, but then there is no alternative for copying the row heights.
LVL 1
hermesalphaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KimputerCommented:
I would assign a macro to a hotkey, and use this code:

Sub test()

Set src = Application.InputBox("Select a source cell", "Obtain Range Object", Type:=8)
Set dst = Application.InputBox("Select a destination cell", "Obtain Range Object", Type:=8)

src.Copy
dst.PasteSpecial (xlPasteFormats)
dst.Value = src.Value
dst.ColumnWidth = src.ColumnWidth
dst.RowHeight = src.RowHeight

End Sub

Open in new window


You will get a few prompts (select source, destination), but it should take care of all you want, with less clicking, less work, less time.
0
Rob HensonFinance AnalystCommented:
If you are copying everything, make a copy of the sheet.

Right click the tab, Move or Copy, click the Create a Copy option.
0
hermesalphaAuthor Commented:
Kimputer,

I will try your suggestion, just wonder where to paste your code in Developer/Visual Basic? And does it work for this scenario?

I have tab 1, 2 and 3 (each with different contents), and want to copy and paste contents, formating, row height and column width from each of these tab 1, 2 and 3 into separate areas on this destination tab 4.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

KimputerCommented:
Use ALT-F11 to bring up the VBA editor, then copy and paste:

Sub copypasterange()

Set src = Application.InputBox("Select a source cell", "Obtain Range Object", Type:=8)
Set Dst = Application.InputBox("Select a destination cell", "Obtain Range Object", Type:=8)

src.Copy
Dst.PasteSpecial (xlPasteAllUsingSourceTheme)
Dst.PasteSpecial (xlPasteColumnWidths)
Dst.PasteSpecial (xlPasteAllUsingSourceTheme)

Count = 0
For Each Row In src.Rows
    Dst.Offset(rowoffset:=Count).RowHeight = Row.RowHeight
    Count = Count + 1
Next

End Sub

Open in new window


Should work now, copies content, values, widths, heights, from anywhere to anywhere (can be from top left sheet  1 to bottom right of sheet 2).
First prompt, select range, second prompt, only select one cell.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hermesalphaAuthor Commented:
If I already have a VBA code (General, Declarations) for tab 1, where should I paste your code? Now when I open the VBA editor in Excel the code that is already there is displayed.

And I need to paste this code to VBA editor for tab 1, 2, 3 and the destination tab 4 right?
0
KimputerCommented:
There's a ThisWorkBook section, if you put the code there, it always works, no matter what sheet/tab you use.
0
hermesalphaAuthor Commented:
Worked like charm! Thanks very much Kimputer, this saved me a lot of time.
0
Rob HensonFinance AnalystCommented:
I assume that tab 4 has 3 separate areas that do not share rows or columns, laid out something like the numbers 7 5 & 3 on the number pad.

if they share rows or columns, pasting the row height and column width of subsequent pastes could affect row height and column width of previous pastes.
0
Rob HensonFinance AnalystCommented:
I have just seen your subsequent question which suggests that you have now come across the issue I mention.
0
hermesalphaAuthor Commented:
Kimputer, is it possible to modify your VBA somewhat so the routine doesn't copy but cut and paste instead? Because I discovered now I need to move big text areas on the same worksheet.
0
KimputerCommented:
add this line:

Src.Clear

Open in new window


near the end, just above End Sub.
0
hermesalphaAuthor Commented:
It worked fine to cut and paste for the first 2 replacements I did on the same worksheet. But when I tried moving an area (the area that I first moved from down in the sheet to almost the first row) a few rows down and to column A5) I received this error message:

Run-time error '1004':
Application-defined or object-defined error

The first 2 replacements that succeeded, then the area stayed in the same column (only moved between rows). When I got the error message I tried to move between both columns and rows at the same time. I'll try now to only move either between only columns or between only rows.
0
hermesalphaAuthor Commented:
I tried now moving within the same column and it did it partly, then I got the same error message again and only parts of the area was formatted as the original formatting, most was not formatted. Could it have anything to do with that I have left grouped areas that need to be removed first (Data/Group Data)?
0
hermesalphaAuthor Commented:
I removed all groupings of rows and columns now but still get this error message. The strange thing is that the "1" and "2" (for collapsing and expanding the rows) are still displayed in the upper left corner but all the - and + signs are gone.
0
hermesalphaAuthor Commented:
No, I could see now I have columns grouped. Will try to remove them.
0
hermesalphaAuthor Commented:
All groupings removed but still this error message.

By the way, row 2, 3 and 4, the numbers themselves kind of go up into the roof so only half of the number is displayed. Could this have something to do with the error message? (I am trying to move the area Z2:DO62 to A5.)
0
hermesalphaAuthor Commented:
The area I get this error message for, it's something strange with some of the cells: when I enter text in them nothing of the text is displayed in the actual cell (only in the formula bar furthest up).
0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.