Link to home
Start Free TrialLog in
Avatar of hermesalpha
hermesalphaFlag for Paraguay

asked on

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.
Avatar of Kimputer
Kimputer

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.
If you are copying everything, make a copy of the sheet.

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

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Kimputer
Kimputer

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
There's a ThisWorkBook section, if you put the code there, it always works, no matter what sheet/tab you use.
Worked like charm! Thanks very much Kimputer, this saved me a lot of time.
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.
I have just seen your subsequent question which suggests that you have now come across the issue I mention.
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.
add this line:

Src.Clear

Open in new window


near the end, just above End Sub.
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.
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)?
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.
No, I could see now I have columns grouped. Will try to remove them.
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.)
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).
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.