hermesalpha
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.
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.
If you are copying everything, make a copy of the sheet.
Right click the tab, Move or Copy, click the Create a Copy option.
Right click the tab, Move or Copy, click the Create a Copy option.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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.
ASKER
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.
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.
ASKER
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:
near the end, just above End Sub.
Src.Clear
near the end, just above End Sub.
ASKER
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.
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.
ASKER
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)?
ASKER
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.
ASKER
No, I could see now I have columns grouped. Will try to remove them.
ASKER
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.)
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.)
ASKER
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.
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.