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

hermesalpha
hermesalpha used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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.
Rob HensonFinance Analyst

Commented:
If you are copying everything, make a copy of the sheet.

Right click the tab, Move or Copy, click the Create a Copy option.

Author

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.
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Commented:
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.

Author

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?

Commented:
There's a ThisWorkBook section, if you put the code there, it always works, no matter what sheet/tab you use.

Author

Commented:
Worked like charm! Thanks very much Kimputer, this saved me a lot of time.
Rob HensonFinance Analyst

Commented:
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.
Rob HensonFinance Analyst

Commented:
I have just seen your subsequent question which suggests that you have now come across the issue I mention.

Author

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.

Commented:
add this line:

Src.Clear

Open in new window


near the end, just above End Sub.

Author

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.

Author

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)?

Author

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.

Author

Commented:
No, I could see now I have columns grouped. Will try to remove them.

Author

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.)

Author

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).
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial