+ Reply to Thread
Results 1 to 18 of 18

Paste Special

  1. #1
    Kassie
    Guest

    RE: Paste Special

    Hi

    You can actually copy the column widths, but you have to repaste as Special,
    and then select Column Widths. So you do CtrlC, go to destination, right
    click on Paste Special, select All, click on OK, right click on destination,
    select Paste Special, click on column widths, and then on OK.

    I do not know about row heights though.

    "Kingstonsean" wrote:

    > If I copy a range of cells (either one row, or several rows and colums) from
    > one work sheet to another the shading, font, data, borders, etc, all copy
    > over to the new worksheet, but the column widths and row heights do not copy
    > over. It seems that in older versions of Excel (I'm using Office 2003) I
    > could use Paste Special|Formats, but this doesn't seem to work in Office
    > 2003. Any suggestions?


  2. #2
    Kingstonsean
    Guest

    RE: Paste Special

    Thanks. Sorta makes you wonder what "All" is for...


    "Kassie" wrote:

    > Hi
    >
    > You can actually copy the column widths, but you have to repaste as Special,
    > and then select Column Widths. So you do CtrlC, go to destination, right
    > click on Paste Special, select All, click on OK, right click on destination,
    > select Paste Special, click on column widths, and then on OK.
    >
    > I do not know about row heights though.
    >
    > "Kingstonsean" wrote:
    >
    > > If I copy a range of cells (either one row, or several rows and colums) from
    > > one work sheet to another the shading, font, data, borders, etc, all copy
    > > over to the new worksheet, but the column widths and row heights do not copy
    > > over. It seems that in older versions of Excel (I'm using Office 2003) I
    > > could use Paste Special|Formats, but this doesn't seem to work in Office
    > > 2003. Any suggestions?


  3. #3
    Kassie
    Guest

    RE: Paste Special

    Could never figure that one out myself! glad I coul have been of assistance

    "Kingstonsean" wrote:

    > Thanks. Sorta makes you wonder what "All" is for...
    >
    >
    > "Kassie" wrote:
    >
    > > Hi
    > >
    > > You can actually copy the column widths, but you have to repaste as Special,
    > > and then select Column Widths. So you do CtrlC, go to destination, right
    > > click on Paste Special, select All, click on OK, right click on destination,
    > > select Paste Special, click on column widths, and then on OK.
    > >
    > > I do not know about row heights though.
    > >
    > > "Kingstonsean" wrote:
    > >
    > > > If I copy a range of cells (either one row, or several rows and colums) from
    > > > one work sheet to another the shading, font, data, borders, etc, all copy
    > > > over to the new worksheet, but the column widths and row heights do not copy
    > > > over. It seems that in older versions of Excel (I'm using Office 2003) I
    > > > could use Paste Special|Formats, but this doesn't seem to work in Office
    > > > 2003. Any suggestions?


  4. #4
    Tom Ogilvy
    Guest

    Re: Paste Special

    That didn't work any differently in older versions. The pastespecial
    columnwidths as a separate option was added in xl2000 as I recall.

    ColumnWidth is an attribute of the entire column. RowHeight is an attribute
    of the entirerow. Since you are not copying the entirecolumn or entirerow,
    that format is not copied.

    --
    Regards,
    Tom Ogilvy

    "Kassie" <Kassie@discussions.microsoft.com> wrote in message
    news:C7B73F73-1809-4A82-869F-6E65CA0B5876@microsoft.com...
    > Could never figure that one out myself! glad I coul have been of

    assistance
    >
    > "Kingstonsean" wrote:
    >
    > > Thanks. Sorta makes you wonder what "All" is for...
    > >
    > >
    > > "Kassie" wrote:
    > >
    > > > Hi
    > > >
    > > > You can actually copy the column widths, but you have to repaste as

    Special,
    > > > and then select Column Widths. So you do CtrlC, go to destination,

    right
    > > > click on Paste Special, select All, click on OK, right click on

    destination,
    > > > select Paste Special, click on column widths, and then on OK.
    > > >
    > > > I do not know about row heights though.
    > > >
    > > > "Kingstonsean" wrote:
    > > >
    > > > > If I copy a range of cells (either one row, or several rows and

    colums) from
    > > > > one work sheet to another the shading, font, data, borders, etc, all

    copy
    > > > > over to the new worksheet, but the column widths and row heights do

    not copy
    > > > > over. It seems that in older versions of Excel (I'm using Office

    2003) I
    > > > > could use Paste Special|Formats, but this doesn't seem to work in

    Office
    > > > > 2003. Any suggestions?




  5. #5
    rberke
    Guest

    Re: Paste Special


    Don't know if you are interested in vba solution, but if you are, read
    on.

    I had similar problem today. I wrote the following vba macro. It has
    not been well tested, but it worked for me and I hope it helps you.

    You can put into personal.xls.

    I also have a macro in personal.xls which I have assign to ctlr shift
    n.

    Sub askmacro()
    s = Trim(LCase(InputBox("enter macro code")))
    If Right(s, 1) = "s" Then s = Left(s, Len(s) - 1)
    If s = "" Then Exit Sub

    Select Case s
    Case "pastediff": Call PasteSpecialHighlightDifference
    Case "rtrim": Call myRtrimall
    Case "paste45", "ps45": Call PasteHdgs45
    Case "psrowheight": Call PasteSpecialRowHeights

    Case Else
    MsgBox s & "=no such shortcut"
    End
    End Select

    End Sub


    -------------------------------
    Sub PasteSpecialRowHeights()
    ' Excel's EditPasteSpecial allows you to paste column widths, but not
    row heights.
    ' this macro exends that function
    '
    ' to use macro:
    ' 1 format some rows to have your "ideal row heights"
    ' 2 select those full rows and copy them to the clipboard
    ' 3 navigate to the top left cell where you want to paste the row
    heights
    ' 4 call macro.
    ' the destination cells will now have the ideal heights
    ' future enhancement 1: I don't like requiring user to select full
    ' rows before they copy.
    ' this would make it possible for
    PasteSpecialRowHeights and
    ' pastespecialcolumnWidths to both use the same
    clipboard
    ' future enhancement 2: if target rectangle is more than one cell,
    ' restrict paste so only the selected rectangle is pasted
    ' future enhancement 3: bundle together 3 function:
    ' paste data
    ' paste row heights
    ' paste column widths
    '
    ' to test current version quickly add the following steps
    ' select the ideal rows then Insert > Name > Define > "testsrc"
    ' select the top left cell in your target area and Insert > Name >
    Define > "testtgt"
    ' change constant to say "const testmode = true"

    Const testmode = False
    If testmode Then
    Application.Goto ("testsrc")

    Selection.Copy

    Application.Goto ("testtgt")
    End If

    Set tgtsheet = ActiveSheet
    Set tgtsel = Selection
    Set tgtact = ActiveCell

    ActiveWorkbook.Worksheets.Add
    newsheetname = ActiveSheet.name

    Set TempSheet = ActiveSheet
    Selection.Insert Shift:=xlToDown


    Dim c As Long

    For c = 1 To TempSheet.UsedRange.Rows.Count
    tgtsel.Rows(c).RowHeight = TempSheet.Rows(c).RowHeight
    Next c

    chgix = 0

    If chgix = 0 Then
    Application.DisplayAlerts = False
    TempSheet.Delete
    Application.DisplayAlerts = True
    tgtsheet.Activate
    tgtsel.Select
    tgtact.Activate
    Else
    MsgBox chgix & " future use"
    End If


    End Sub


    Tom Ogilvy Wrote:
    > That didn't work any differently in older versions. The pastespecial
    > columnwidths as a separate option was added in xl2000 as I recall.
    >
    > ColumnWidth is an attribute of the entire column. RowHeight is an
    > attribute
    > of the entirerow. Since you are not copying the entirecolumn or
    > entirerow,
    > that format is not copied.
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    > "Kassie" Kassie@discussions.microsoft.com wrote in message
    > news:C7B73F73-1809-4A82-869F-6E65CA0B5876@microsoft.com...-
    > Could never figure that one out myself! glad I coul have been of-
    > assistance-
    >
    > "Kingstonsean" wrote:
    > -
    > Thanks. Sorta makes you wonder what "All" is for...
    >
    >
    > "Kassie" wrote:
    > -
    > Hi
    >
    > You can actually copy the column widths, but you have to repaste
    > as---
    > Special,---
    > and then select Column Widths. So you do CtrlC, go to
    > destination,---
    > right---
    > click on Paste Special, select All, click on OK, right click on---
    > destination,---
    > select Paste Special, click on column widths, and then on OK.
    >
    > I do not know about row heights though.
    >
    > "Kingstonsean" wrote:
    >
    > If I copy a range of cells (either one row, or several rows and---
    > colums) from---
    > one work sheet to another the shading, font, data, borders, etc,
    > all---
    > copy---
    > over to the new worksheet, but the column widths and row heights
    > do---
    > not copy---
    > over. It seems that in older versions of Excel (I'm using
    > Office---
    > 2003) I---
    > could use Paste Special|Formats, but this doesn't seem to work
    > in---
    > Office---
    > 2003. Any suggestions?---



    --
    rberke

  6. #6
    Kassie
    Guest

    RE: Paste Special

    Hi

    You can actually copy the column widths, but you have to repaste as Special,
    and then select Column Widths. So you do CtrlC, go to destination, right
    click on Paste Special, select All, click on OK, right click on destination,
    select Paste Special, click on column widths, and then on OK.

    I do not know about row heights though.

    "Kingstonsean" wrote:

    > If I copy a range of cells (either one row, or several rows and colums) from
    > one work sheet to another the shading, font, data, borders, etc, all copy
    > over to the new worksheet, but the column widths and row heights do not copy
    > over. It seems that in older versions of Excel (I'm using Office 2003) I
    > could use Paste Special|Formats, but this doesn't seem to work in Office
    > 2003. Any suggestions?


  7. #7
    Kingstonsean
    Guest

    RE: Paste Special

    Thanks. Sorta makes you wonder what "All" is for...


    "Kassie" wrote:

    > Hi
    >
    > You can actually copy the column widths, but you have to repaste as Special,
    > and then select Column Widths. So you do CtrlC, go to destination, right
    > click on Paste Special, select All, click on OK, right click on destination,
    > select Paste Special, click on column widths, and then on OK.
    >
    > I do not know about row heights though.
    >
    > "Kingstonsean" wrote:
    >
    > > If I copy a range of cells (either one row, or several rows and colums) from
    > > one work sheet to another the shading, font, data, borders, etc, all copy
    > > over to the new worksheet, but the column widths and row heights do not copy
    > > over. It seems that in older versions of Excel (I'm using Office 2003) I
    > > could use Paste Special|Formats, but this doesn't seem to work in Office
    > > 2003. Any suggestions?


  8. #8
    Kassie
    Guest

    RE: Paste Special

    Could never figure that one out myself! glad I coul have been of assistance

    "Kingstonsean" wrote:

    > Thanks. Sorta makes you wonder what "All" is for...
    >
    >
    > "Kassie" wrote:
    >
    > > Hi
    > >
    > > You can actually copy the column widths, but you have to repaste as Special,
    > > and then select Column Widths. So you do CtrlC, go to destination, right
    > > click on Paste Special, select All, click on OK, right click on destination,
    > > select Paste Special, click on column widths, and then on OK.
    > >
    > > I do not know about row heights though.
    > >
    > > "Kingstonsean" wrote:
    > >
    > > > If I copy a range of cells (either one row, or several rows and colums) from
    > > > one work sheet to another the shading, font, data, borders, etc, all copy
    > > > over to the new worksheet, but the column widths and row heights do not copy
    > > > over. It seems that in older versions of Excel (I'm using Office 2003) I
    > > > could use Paste Special|Formats, but this doesn't seem to work in Office
    > > > 2003. Any suggestions?


  9. #9
    Tom Ogilvy
    Guest

    Re: Paste Special

    That didn't work any differently in older versions. The pastespecial
    columnwidths as a separate option was added in xl2000 as I recall.

    ColumnWidth is an attribute of the entire column. RowHeight is an attribute
    of the entirerow. Since you are not copying the entirecolumn or entirerow,
    that format is not copied.

    --
    Regards,
    Tom Ogilvy

    "Kassie" <Kassie@discussions.microsoft.com> wrote in message
    news:C7B73F73-1809-4A82-869F-6E65CA0B5876@microsoft.com...
    > Could never figure that one out myself! glad I coul have been of

    assistance
    >
    > "Kingstonsean" wrote:
    >
    > > Thanks. Sorta makes you wonder what "All" is for...
    > >
    > >
    > > "Kassie" wrote:
    > >
    > > > Hi
    > > >
    > > > You can actually copy the column widths, but you have to repaste as

    Special,
    > > > and then select Column Widths. So you do CtrlC, go to destination,

    right
    > > > click on Paste Special, select All, click on OK, right click on

    destination,
    > > > select Paste Special, click on column widths, and then on OK.
    > > >
    > > > I do not know about row heights though.
    > > >
    > > > "Kingstonsean" wrote:
    > > >
    > > > > If I copy a range of cells (either one row, or several rows and

    colums) from
    > > > > one work sheet to another the shading, font, data, borders, etc, all

    copy
    > > > > over to the new worksheet, but the column widths and row heights do

    not copy
    > > > > over. It seems that in older versions of Excel (I'm using Office

    2003) I
    > > > > could use Paste Special|Formats, but this doesn't seem to work in

    Office
    > > > > 2003. Any suggestions?




  10. #10
    rberke
    Guest

    Re: Paste Special


    Don't know if you are interested in vba solution, but if you are, read
    on.

    I had similar problem today. I wrote the following vba macro. It has
    not been well tested, but it worked for me and I hope it helps you.

    You can put into personal.xls.

    I also have a macro in personal.xls which I have assign to ctlr shift
    n.

    Sub askmacro()
    s = Trim(LCase(InputBox("enter macro code")))
    If Right(s, 1) = "s" Then s = Left(s, Len(s) - 1)
    If s = "" Then Exit Sub

    Select Case s
    Case "pastediff": Call PasteSpecialHighlightDifference
    Case "rtrim": Call myRtrimall
    Case "paste45", "ps45": Call PasteHdgs45
    Case "psrowheight": Call PasteSpecialRowHeights

    Case Else
    MsgBox s & "=no such shortcut"
    End
    End Select

    End Sub


    -------------------------------
    Sub PasteSpecialRowHeights()
    ' Excel's EditPasteSpecial allows you to paste column widths, but not
    row heights.
    ' this macro exends that function
    '
    ' to use macro:
    ' 1 format some rows to have your "ideal row heights"
    ' 2 select those full rows and copy them to the clipboard
    ' 3 navigate to the top left cell where you want to paste the row
    heights
    ' 4 call macro.
    ' the destination cells will now have the ideal heights
    ' future enhancement 1: I don't like requiring user to select full
    ' rows before they copy.
    ' this would make it possible for
    PasteSpecialRowHeights and
    ' pastespecialcolumnWidths to both use the same
    clipboard
    ' future enhancement 2: if target rectangle is more than one cell,
    ' restrict paste so only the selected rectangle is pasted
    ' future enhancement 3: bundle together 3 function:
    ' paste data
    ' paste row heights
    ' paste column widths
    '
    ' to test current version quickly add the following steps
    ' select the ideal rows then Insert > Name > Define > "testsrc"
    ' select the top left cell in your target area and Insert > Name >
    Define > "testtgt"
    ' change constant to say "const testmode = true"

    Const testmode = False
    If testmode Then
    Application.Goto ("testsrc")

    Selection.Copy

    Application.Goto ("testtgt")
    End If

    Set tgtsheet = ActiveSheet
    Set tgtsel = Selection
    Set tgtact = ActiveCell

    ActiveWorkbook.Worksheets.Add
    newsheetname = ActiveSheet.name

    Set TempSheet = ActiveSheet
    Selection.Insert Shift:=xlToDown


    Dim c As Long

    For c = 1 To TempSheet.UsedRange.Rows.Count
    tgtsel.Rows(c).RowHeight = TempSheet.Rows(c).RowHeight
    Next c

    chgix = 0

    If chgix = 0 Then
    Application.DisplayAlerts = False
    TempSheet.Delete
    Application.DisplayAlerts = True
    tgtsheet.Activate
    tgtsel.Select
    tgtact.Activate
    Else
    MsgBox chgix & " future use"
    End If


    End Sub


    Tom Ogilvy Wrote:
    > That didn't work any differently in older versions. The pastespecial
    > columnwidths as a separate option was added in xl2000 as I recall.
    >
    > ColumnWidth is an attribute of the entire column. RowHeight is an
    > attribute
    > of the entirerow. Since you are not copying the entirecolumn or
    > entirerow,
    > that format is not copied.
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    > "Kassie" Kassie@discussions.microsoft.com wrote in message
    > news:C7B73F73-1809-4A82-869F-6E65CA0B5876@microsoft.com...-
    > Could never figure that one out myself! glad I coul have been of-
    > assistance-
    >
    > "Kingstonsean" wrote:
    > -
    > Thanks. Sorta makes you wonder what "All" is for...
    >
    >
    > "Kassie" wrote:
    > -
    > Hi
    >
    > You can actually copy the column widths, but you have to repaste
    > as---
    > Special,---
    > and then select Column Widths. So you do CtrlC, go to
    > destination,---
    > right---
    > click on Paste Special, select All, click on OK, right click on---
    > destination,---
    > select Paste Special, click on column widths, and then on OK.
    >
    > I do not know about row heights though.
    >
    > "Kingstonsean" wrote:
    >
    > If I copy a range of cells (either one row, or several rows and---
    > colums) from---
    > one work sheet to another the shading, font, data, borders, etc,
    > all---
    > copy---
    > over to the new worksheet, but the column widths and row heights
    > do---
    > not copy---
    > over. It seems that in older versions of Excel (I'm using
    > Office---
    > 2003) I---
    > could use Paste Special|Formats, but this doesn't seem to work
    > in---
    > Office---
    > 2003. Any suggestions?---



    --
    rberke

  11. #11
    Kingstonsean
    Guest

    Paste Special

    If I copy a range of cells (either one row, or several rows and colums) from
    one work sheet to another the shading, font, data, borders, etc, all copy
    over to the new worksheet, but the column widths and row heights do not copy
    over. It seems that in older versions of Excel (I'm using Office 2003) I
    could use Paste Special|Formats, but this doesn't seem to work in Office
    2003. Any suggestions?

  12. #12
    Kassie
    Guest

    RE: Paste Special

    Hi

    You can actually copy the column widths, but you have to repaste as Special,
    and then select Column Widths. So you do CtrlC, go to destination, right
    click on Paste Special, select All, click on OK, right click on destination,
    select Paste Special, click on column widths, and then on OK.

    I do not know about row heights though.

    "Kingstonsean" wrote:

    > If I copy a range of cells (either one row, or several rows and colums) from
    > one work sheet to another the shading, font, data, borders, etc, all copy
    > over to the new worksheet, but the column widths and row heights do not copy
    > over. It seems that in older versions of Excel (I'm using Office 2003) I
    > could use Paste Special|Formats, but this doesn't seem to work in Office
    > 2003. Any suggestions?


  13. #13
    Kingstonsean
    Guest

    RE: Paste Special

    Thanks. Sorta makes you wonder what "All" is for...


    "Kassie" wrote:

    > Hi
    >
    > You can actually copy the column widths, but you have to repaste as Special,
    > and then select Column Widths. So you do CtrlC, go to destination, right
    > click on Paste Special, select All, click on OK, right click on destination,
    > select Paste Special, click on column widths, and then on OK.
    >
    > I do not know about row heights though.
    >
    > "Kingstonsean" wrote:
    >
    > > If I copy a range of cells (either one row, or several rows and colums) from
    > > one work sheet to another the shading, font, data, borders, etc, all copy
    > > over to the new worksheet, but the column widths and row heights do not copy
    > > over. It seems that in older versions of Excel (I'm using Office 2003) I
    > > could use Paste Special|Formats, but this doesn't seem to work in Office
    > > 2003. Any suggestions?


  14. #14
    Kassie
    Guest

    RE: Paste Special

    Could never figure that one out myself! glad I coul have been of assistance

    "Kingstonsean" wrote:

    > Thanks. Sorta makes you wonder what "All" is for...
    >
    >
    > "Kassie" wrote:
    >
    > > Hi
    > >
    > > You can actually copy the column widths, but you have to repaste as Special,
    > > and then select Column Widths. So you do CtrlC, go to destination, right
    > > click on Paste Special, select All, click on OK, right click on destination,
    > > select Paste Special, click on column widths, and then on OK.
    > >
    > > I do not know about row heights though.
    > >
    > > "Kingstonsean" wrote:
    > >
    > > > If I copy a range of cells (either one row, or several rows and colums) from
    > > > one work sheet to another the shading, font, data, borders, etc, all copy
    > > > over to the new worksheet, but the column widths and row heights do not copy
    > > > over. It seems that in older versions of Excel (I'm using Office 2003) I
    > > > could use Paste Special|Formats, but this doesn't seem to work in Office
    > > > 2003. Any suggestions?


  15. #15
    Tom Ogilvy
    Guest

    Re: Paste Special

    That didn't work any differently in older versions. The pastespecial
    columnwidths as a separate option was added in xl2000 as I recall.

    ColumnWidth is an attribute of the entire column. RowHeight is an attribute
    of the entirerow. Since you are not copying the entirecolumn or entirerow,
    that format is not copied.

    --
    Regards,
    Tom Ogilvy

    "Kassie" <Kassie@discussions.microsoft.com> wrote in message
    news:C7B73F73-1809-4A82-869F-6E65CA0B5876@microsoft.com...
    > Could never figure that one out myself! glad I coul have been of

    assistance
    >
    > "Kingstonsean" wrote:
    >
    > > Thanks. Sorta makes you wonder what "All" is for...
    > >
    > >
    > > "Kassie" wrote:
    > >
    > > > Hi
    > > >
    > > > You can actually copy the column widths, but you have to repaste as

    Special,
    > > > and then select Column Widths. So you do CtrlC, go to destination,

    right
    > > > click on Paste Special, select All, click on OK, right click on

    destination,
    > > > select Paste Special, click on column widths, and then on OK.
    > > >
    > > > I do not know about row heights though.
    > > >
    > > > "Kingstonsean" wrote:
    > > >
    > > > > If I copy a range of cells (either one row, or several rows and

    colums) from
    > > > > one work sheet to another the shading, font, data, borders, etc, all

    copy
    > > > > over to the new worksheet, but the column widths and row heights do

    not copy
    > > > > over. It seems that in older versions of Excel (I'm using Office

    2003) I
    > > > > could use Paste Special|Formats, but this doesn't seem to work in

    Office
    > > > > 2003. Any suggestions?




  16. #16
    rberke
    Guest

    Re: Paste Special


    Don't know if you are interested in vba solution, but if you are, read
    on.

    I had similar problem today. I wrote the following vba macro. It has
    not been well tested, but it worked for me and I hope it helps you.

    You can put into personal.xls.

    I also have a macro in personal.xls which I have assign to ctlr shift
    n.

    Sub askmacro()
    s = Trim(LCase(InputBox("enter macro code")))
    If Right(s, 1) = "s" Then s = Left(s, Len(s) - 1)
    If s = "" Then Exit Sub

    Select Case s
    Case "pastediff": Call PasteSpecialHighlightDifference
    Case "rtrim": Call myRtrimall
    Case "paste45", "ps45": Call PasteHdgs45
    Case "psrowheight": Call PasteSpecialRowHeights

    Case Else
    MsgBox s & "=no such shortcut"
    End
    End Select

    End Sub


    -------------------------------
    Sub PasteSpecialRowHeights()
    ' Excel's EditPasteSpecial allows you to paste column widths, but not
    row heights.
    ' this macro exends that function
    '
    ' to use macro:
    ' 1 format some rows to have your "ideal row heights"
    ' 2 select those full rows and copy them to the clipboard
    ' 3 navigate to the top left cell where you want to paste the row
    heights
    ' 4 call macro.
    ' the destination cells will now have the ideal heights
    ' future enhancement 1: I don't like requiring user to select full
    ' rows before they copy.
    ' this would make it possible for
    PasteSpecialRowHeights and
    ' pastespecialcolumnWidths to both use the same
    clipboard
    ' future enhancement 2: if target rectangle is more than one cell,
    ' restrict paste so only the selected rectangle is pasted
    ' future enhancement 3: bundle together 3 function:
    ' paste data
    ' paste row heights
    ' paste column widths
    '
    ' to test current version quickly add the following steps
    ' select the ideal rows then Insert > Name > Define > "testsrc"
    ' select the top left cell in your target area and Insert > Name >
    Define > "testtgt"
    ' change constant to say "const testmode = true"

    Const testmode = False
    If testmode Then
    Application.Goto ("testsrc")

    Selection.Copy

    Application.Goto ("testtgt")
    End If

    Set tgtsheet = ActiveSheet
    Set tgtsel = Selection
    Set tgtact = ActiveCell

    ActiveWorkbook.Worksheets.Add
    newsheetname = ActiveSheet.name

    Set TempSheet = ActiveSheet
    Selection.Insert Shift:=xlToDown


    Dim c As Long

    For c = 1 To TempSheet.UsedRange.Rows.Count
    tgtsel.Rows(c).RowHeight = TempSheet.Rows(c).RowHeight
    Next c

    chgix = 0

    If chgix = 0 Then
    Application.DisplayAlerts = False
    TempSheet.Delete
    Application.DisplayAlerts = True
    tgtsheet.Activate
    tgtsel.Select
    tgtact.Activate
    Else
    MsgBox chgix & " future use"
    End If


    End Sub


    Tom Ogilvy Wrote:
    > That didn't work any differently in older versions. The pastespecial
    > columnwidths as a separate option was added in xl2000 as I recall.
    >
    > ColumnWidth is an attribute of the entire column. RowHeight is an
    > attribute
    > of the entirerow. Since you are not copying the entirecolumn or
    > entirerow,
    > that format is not copied.
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    > "Kassie" Kassie@discussions.microsoft.com wrote in message
    > news:C7B73F73-1809-4A82-869F-6E65CA0B5876@microsoft.com...-
    > Could never figure that one out myself! glad I coul have been of-
    > assistance-
    >
    > "Kingstonsean" wrote:
    > -
    > Thanks. Sorta makes you wonder what "All" is for...
    >
    >
    > "Kassie" wrote:
    > -
    > Hi
    >
    > You can actually copy the column widths, but you have to repaste
    > as---
    > Special,---
    > and then select Column Widths. So you do CtrlC, go to
    > destination,---
    > right---
    > click on Paste Special, select All, click on OK, right click on---
    > destination,---
    > select Paste Special, click on column widths, and then on OK.
    >
    > I do not know about row heights though.
    >
    > "Kingstonsean" wrote:
    >
    > If I copy a range of cells (either one row, or several rows and---
    > colums) from---
    > one work sheet to another the shading, font, data, borders, etc,
    > all---
    > copy---
    > over to the new worksheet, but the column widths and row heights
    > do---
    > not copy---
    > over. It seems that in older versions of Excel (I'm using
    > Office---
    > 2003) I---
    > could use Paste Special|Formats, but this doesn't seem to work
    > in---
    > Office---
    > 2003. Any suggestions?---



    --
    rberke

  17. #17
    rberke
    Guest

    Re: Paste Special


    Don't know if you are interested in vba solution, but if you are, read
    on.

    I had similar problem today. I wrote the following vba macro. It has
    not been well tested, but it worked for me and I hope it helps you.

    You can put into personal.xls.

    I also have a macro in personal.xls which I have assign to ctlr shift
    n.

    Sub askmacro()
    s = Trim(LCase(InputBox("enter macro code")))
    If Right(s, 1) = "s" Then s = Left(s, Len(s) - 1)
    If s = "" Then Exit Sub

    Select Case s
    Case "pastediff": Call PasteSpecialHighlightDifference
    Case "rtrim": Call myRtrimall
    Case "paste45", "ps45": Call PasteHdgs45
    Case "psrowheight": Call PasteSpecialRowHeights

    Case Else
    MsgBox s & "=no such shortcut"
    End
    End Select

    End Sub


    -------------------------------
    Sub PasteSpecialRowHeights()
    ' Excel's EditPasteSpecial allows you to paste column widths, but not
    row heights.
    ' this macro exends that function
    '
    ' to use macro:
    ' 1 format some rows to have your "ideal row heights"
    ' 2 select those full rows and copy them to the clipboard
    ' 3 navigate to the top left cell where you want to paste the row
    heights
    ' 4 call macro.
    ' the destination cells will now have the ideal heights
    ' future enhancement 1: I don't like requiring user to select full
    ' rows before they copy.
    ' this would make it possible for
    PasteSpecialRowHeights and
    ' pastespecialcolumnWidths to both use the same
    clipboard
    ' future enhancement 2: if target rectangle is more than one cell,
    ' restrict paste so only the selected rectangle is pasted
    ' future enhancement 3: bundle together 3 function:
    ' paste data
    ' paste row heights
    ' paste column widths
    '
    ' to test current version quickly add the following steps
    ' select the ideal rows then Insert > Name > Define > "testsrc"
    ' select the top left cell in your target area and Insert > Name >
    Define > "testtgt"
    ' change constant to say "const testmode = true"

    Const testmode = False
    If testmode Then
    Application.Goto ("testsrc")

    Selection.Copy

    Application.Goto ("testtgt")
    End If

    Set tgtsheet = ActiveSheet
    Set tgtsel = Selection
    Set tgtact = ActiveCell

    ActiveWorkbook.Worksheets.Add
    newsheetname = ActiveSheet.name

    Set TempSheet = ActiveSheet
    Selection.Insert Shift:=xlToDown


    Dim c As Long

    For c = 1 To TempSheet.UsedRange.Rows.Count
    tgtsel.Rows(c).RowHeight = TempSheet.Rows(c).RowHeight
    Next c

    chgix = 0

    If chgix = 0 Then
    Application.DisplayAlerts = False
    TempSheet.Delete
    Application.DisplayAlerts = True
    tgtsheet.Activate
    tgtsel.Select
    tgtact.Activate
    Else
    MsgBox chgix & " future use"
    End If


    End Sub


    Tom Ogilvy Wrote:
    > That didn't work any differently in older versions. The pastespecial
    > columnwidths as a separate option was added in xl2000 as I recall.
    >
    > ColumnWidth is an attribute of the entire column. RowHeight is an
    > attribute
    > of the entirerow. Since you are not copying the entirecolumn or
    > entirerow,
    > that format is not copied.
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    > "Kassie" Kassie@discussions.microsoft.com wrote in message
    > news:C7B73F73-1809-4A82-869F-6E65CA0B5876@microsoft.com...-
    > Could never figure that one out myself! glad I coul have been of-
    > assistance-
    >
    > "Kingstonsean" wrote:
    > -
    > Thanks. Sorta makes you wonder what "All" is for...
    >
    >
    > "Kassie" wrote:
    > -
    > Hi
    >
    > You can actually copy the column widths, but you have to repaste
    > as---
    > Special,---
    > and then select Column Widths. So you do CtrlC, go to
    > destination,---
    > right---
    > click on Paste Special, select All, click on OK, right click on---
    > destination,---
    > select Paste Special, click on column widths, and then on OK.
    >
    > I do not know about row heights though.
    >
    > "Kingstonsean" wrote:
    >
    > If I copy a range of cells (either one row, or several rows and---
    > colums) from---
    > one work sheet to another the shading, font, data, borders, etc,
    > all---
    > copy---
    > over to the new worksheet, but the column widths and row heights
    > do---
    > not copy---
    > over. It seems that in older versions of Excel (I'm using
    > Office---
    > 2003) I---
    > could use Paste Special|Formats, but this doesn't seem to work
    > in---
    > Office---
    > 2003. Any suggestions?---



    --
    rberke

  18. #18
    rberke
    Guest

    Re: Paste Special


    Don't know if you are interested in vba solution, but if you are, read
    on.

    I had similar problem today. I wrote the following vba macro. It has
    not been well tested, but it worked for me and I hope it helps you.

    You can put into personal.xls.

    I also have a macro in personal.xls which I have assign to ctlr shift
    n.

    Sub askmacro()
    s = Trim(LCase(InputBox("enter macro code")))
    If Right(s, 1) = "s" Then s = Left(s, Len(s) - 1)
    If s = "" Then Exit Sub

    Select Case s
    Case "pastediff": Call PasteSpecialHighlightDifference
    Case "rtrim": Call myRtrimall
    Case "paste45", "ps45": Call PasteHdgs45
    Case "psrowheight": Call PasteSpecialRowHeights

    Case Else
    MsgBox s & "=no such shortcut"
    End
    End Select

    End Sub


    -------------------------------
    Sub PasteSpecialRowHeights()
    ' Excel's EditPasteSpecial allows you to paste column widths, but not
    row heights.
    ' this macro exends that function
    '
    ' to use macro:
    ' 1 format some rows to have your "ideal row heights"
    ' 2 select those full rows and copy them to the clipboard
    ' 3 navigate to the top left cell where you want to paste the row
    heights
    ' 4 call macro.
    ' the destination cells will now have the ideal heights
    ' future enhancement 1: I don't like requiring user to select full
    ' rows before they copy.
    ' this would make it possible for
    PasteSpecialRowHeights and
    ' pastespecialcolumnWidths to both use the same
    clipboard
    ' future enhancement 2: if target rectangle is more than one cell,
    ' restrict paste so only the selected rectangle is pasted
    ' future enhancement 3: bundle together 3 function:
    ' paste data
    ' paste row heights
    ' paste column widths
    '
    ' to test current version quickly add the following steps
    ' select the ideal rows then Insert > Name > Define > "testsrc"
    ' select the top left cell in your target area and Insert > Name >
    Define > "testtgt"
    ' change constant to say "const testmode = true"

    Const testmode = False
    If testmode Then
    Application.Goto ("testsrc")

    Selection.Copy

    Application.Goto ("testtgt")
    End If

    Set tgtsheet = ActiveSheet
    Set tgtsel = Selection
    Set tgtact = ActiveCell

    ActiveWorkbook.Worksheets.Add
    newsheetname = ActiveSheet.name

    Set TempSheet = ActiveSheet
    Selection.Insert Shift:=xlToDown


    Dim c As Long

    For c = 1 To TempSheet.UsedRange.Rows.Count
    tgtsel.Rows(c).RowHeight = TempSheet.Rows(c).RowHeight
    Next c

    chgix = 0

    If chgix = 0 Then
    Application.DisplayAlerts = False
    TempSheet.Delete
    Application.DisplayAlerts = True
    tgtsheet.Activate
    tgtsel.Select
    tgtact.Activate
    Else
    MsgBox chgix & " future use"
    End If


    End Sub


    Tom Ogilvy Wrote:
    > That didn't work any differently in older versions. The pastespecial
    > columnwidths as a separate option was added in xl2000 as I recall.
    >
    > ColumnWidth is an attribute of the entire column. RowHeight is an
    > attribute
    > of the entirerow. Since you are not copying the entirecolumn or
    > entirerow,
    > that format is not copied.
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    > "Kassie" Kassie@discussions.microsoft.com wrote in message
    > news:C7B73F73-1809-4A82-869F-6E65CA0B5876@microsoft.com...-
    > Could never figure that one out myself! glad I coul have been of-
    > assistance-
    >
    > "Kingstonsean" wrote:
    > -
    > Thanks. Sorta makes you wonder what "All" is for...
    >
    >
    > "Kassie" wrote:
    > -
    > Hi
    >
    > You can actually copy the column widths, but you have to repaste
    > as---
    > Special,---
    > and then select Column Widths. So you do CtrlC, go to
    > destination,---
    > right---
    > click on Paste Special, select All, click on OK, right click on---
    > destination,---
    > select Paste Special, click on column widths, and then on OK.
    >
    > I do not know about row heights though.
    >
    > "Kingstonsean" wrote:
    >
    > If I copy a range of cells (either one row, or several rows and---
    > colums) from---
    > one work sheet to another the shading, font, data, borders, etc,
    > all---
    > copy---
    > over to the new worksheet, but the column widths and row heights
    > do---
    > not copy---
    > over. It seems that in older versions of Excel (I'm using
    > Office---
    > 2003) I---
    > could use Paste Special|Formats, but this doesn't seem to work
    > in---
    > Office---
    > 2003. Any suggestions?---



    --
    rberke

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1