Incomplete Copy and Paste of Range

[Ark68]

I use this code to copy a range from one worksheet (filtered) to another.

        Worksheets("MasterWKSH").Range("A1:R300").Copy
        With Worksheets("CUE").Range("A1")
            .PasteSpecial xlPasteColumnWidths
            .PasteSpecial xlRowHeights
            .PasteSpecial xlPasteAll
        End With

The paste is not applying the row heights from the original worksheet. I thought .PasteSpecial xlRowHeights would cover that. And second, the original worksheet has a graphic that also needs to be copied over to the destination worksheet. How can I ensure that happens?

 

[kevatarvind]

to paste rowheight use below code in your macro

Sub Test()
Sheets("CUE").Range("A1:A300").RowHeight = Sheets("MasterWKSH").Range("A1:A300").RowHeight
End Sub

 

[Ark68]

Hi Kevatarvind ...

I tried that, and unfortunately ... it didn't do what was expected. No errors, but didn't apply the different row heights.

Jenn

 

[kevatarvind]

sorry pls try below one may be work to paste rowheight

Sub Test()
Sheets("CUE").Select
Range("A1").Select
i = 1
Do Until ActiveCell.Row = 300
ActiveCell.RowHeight = Sheets("MasterWKSH").Range("A" & i).RowHeight
ActiveCell.Offset(1, 0).Select
i = i + 1
Loop
End Sub

 

[Ark68]

ADVERTISEMENT

Hi ... that worked ... kinda.

The "MasterWKSH" worksheet is filtered. So, with the code you suggested ... it also hid rowson the destination sheet ... I'm assuming the row # hidden in the source worksheet (eg. row 12), was also hidden in the destination worksheet, even if that row was intended to be shown.

Does that make any sense?

Jenn

 

[Ark68]

I think for what it's worth Kevatarvind, it might be easier to just assign each row the height rather than copy and paste. There aren't many rows.
The first 6 rows are different row heights. I know I can set the row height of each individual row in a seperate line of code, ie. rows(1).rowheight = 24, rows(2).rowheight=10 etc ...
This would require six lines of code. Is there a way to simply that? If I have a series of rows with the same height, I can use rows(12:22).rowheight=12.75

Jenn

 

[Peter_SSs]

ADVERTISEMENT

The first 6 rows are different row heights. I know I can set the row height of each individual row in a seperate line of code, ie. rows(1).rowheight = 24, rows(2).rowheight=10 etc ...
This would require six lines of code. Is there a way to simply that? If I have a series of rows with the same height, I can use rows(12:22).rowheight=12.75

Jenn

For only 6 rows, the 6 lines of code is probably the simplest, but here's a couple of examples to consider if you have quite a few rows to deal with as they are more easily expandable.

1. Here's one for the first 6 rows.

Sub SetRowheights1()
  Dim r As Long
  Dim H
  
  Const RwH As String = "15 12.75 12.75 30 0 20" '<-Add more if you want
  
  H = Split(RwH)
  For r = 1 To UBound(H) + 1
    Rows(r).RowHeight = CDbl(H(r - 1))
  Next r
End Sub

2. Here's one if the rows are not necessarily sequential & may contain groups with the same height.

Sub SetRowHeights2()
  Dim i As Long
  Dim aRws, aHeights
  
  Const myRws As String = "1,2,3,4:20,30"     '<-Can add more
  Const myHeights As String = "25,35,0,8,54"  '<-Can add more
  
  aRws = Split(myRws, ",")
  aHeights = Split(myHeights, ",")
  For i = 0 To UBound(aRws)
    Rows(aRws(i)).RowHeight = CDbl(aHeights(i))
  Next i
End Sub

 

[Ark68]

Hi Peter ... thanks for your reply and examples. I always appreciate an opportunity to learn.

I have never seen, let alone used, "Split". What is it's use, how does it work?

Cheers

 

[Peter_SSs]

The Split function name is fairly descriptive. It splits a string into smaller parts based on some delimiter. In my first example I didn't specify a delimiter so a space is used by default. In my second example I used a comma as the delimiter. The Split function puts the "bits" created by the split into a zero-based array of strings.

So from my first code

  Dim H
  
  Const RwH As String = "15 12.75 12.75 30 0 20" '<-Add more if you want
  
  H = Split(RwH)

This produces
H(0) = "15"
H(1) = "12.75"
H(2) = "12.75"
H(3) = "30"
H(4) = "0"
H(5) = "20"

There is a corresponding Join function that does the opposite of Split. Look in the built-in vba Help for "Split Function" or "Join Function" for further information.

 

[kevatarvind]

Realy Mr Peter You Are Brilliant man you have lots of knowledge about excel and VB Daily i am learning from seeing your posts Keep doing Help