Copy part of one sheet to another while maintaining formatting

[Jimbo]

I have a large spreadsheet that a clerical person keeps maintained.

I would like her to send me a separate worksheet of the latest updates

I cannot seem to find a way to allow her to copy the last x entries to a new
sheet, while maintaining the formatting (column width, row height, font,
etc.)

I tried Format painter, but I can't seem to get it to work correctly

Can someone help ?

Many Thanks

JM

 

[Rick Rothstein]

If you are up for a macro solution, then you can use the following one. In
my code, I have assumed the sheet that the data will be copied to is named
"Sheet6" (change the sheet name in the With statement to the sheet name you
will always have the data copied to). To use the macro, have your clerical
person select all the rows of new data and then just run the macro (you can
assign the macro to a button to make this easier for the person). If you are
not familiar with working with macros and need help implementing any of
this, then post back to this thread and I, or someone else, will instruct
you further.

Sub CopySelection()
Dim C As Range
With Worksheets("Sheet6")
.Cells.Clear
Selection.EntireColumn.Rows(1).Copy .Range("A1")
Selection.Copy .Range("A2")
For Each C In Selection.Columns
.Columns(C.Column).ColumnWidth = C.ColumnWidth
Next
For Each C In Selection.Rows
.Rows(C.Row).RowHeight = C.RowHeight
Next
End With
End Sub

 

[Jimbo]

Many Thanks for this

I am somewhat capable, but not totally .. (coded assembler in the 60's and
70's)

I created the Macro as given, by following the following instructions

I then highlighted the area I wished to copy, and then executed the Macro by
using Tools --> Macros --> Macros-->highlighted your macro and selected run
(this workbook)

All worked but the Row height

Here is the macro as copied from my macro (changed "Sheet6" to "Latest")

Sub CopySelection()
Dim C As Range
With Worksheets("Latest")
.Cells.Clear
Selection.EntireColumn.Rows(1).Copy .Range("A1")
Selection.Copy .Range("A2")
For Each C In Selection.Columns
.Columns(C.Column).ColumnWidth = C.ColumnWidth
Next
For Each C In Selection.Rows
.Rows(C.Row).RowHeight = C.RowHeight
Next
End With
End Sub

Many Thanks again

JM

 

[Rick Rothstein]

I'm not sure what to tell you... when I rename a sheet to "Latest" and run
the code you are using (that is, I copy/pasted the code you posted), I find
it works perfectly... it copies the data, the formatting and both the column
widths and row heights as expected. I'm using XL2003 in case there is some
problem with versioning.

 

[Jimbo]

I am on 2003 also

Probably something I did while trying to make Format Painter work

Thanks for your help

JM

 

[Rick Rothstein]

Since this sheet is "new" each time, why not try deleting it and renaming
another sheet "Latest" and do *not* use Format Painter on it and see if you
can duplicate my results on the "clean" sheet.

 

[Jimbo]

I was just about to send this ..

In testing it, I SET the formatting on "Latest" something" different than
the macro would produce

If I use a "new" Latest - all is well

Thanks again

 

[grassKutter_dtp]

Did you ask her to copy and paste to an empty worksheet using "Paste
Special"? "Paste Special" allows you to paste formulas, formats, column
widths, etc. I use "Paste all" to get the data before or after I paste the
other options like "column widths". You can use Paste Special any number of
times on the same "copied" data.