+ Reply to Thread
Results 1 to 12 of 12

Row Height

  1. #1
    Registered User
    Join Date
    01-22-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    20

    Question Row Height

    Hi all,
    I write a VBA for the copy and paste. I think it's almost done but I face one critical issue is how to paste the height on it.
    Shall I have your help to modify the following VBA code?
    Requirement:
    I have to copy the height of A1:A149 from Template, worksheet, to the ActiveSheet
    Sub CreateSheetsFromAList_Correct()
    Dim MyCell As Range, MyRange As Range

    Set MyRange = Sheets("Summary").Range("A7") 'Summary is the basis worksheet and A1 is the starting point
    Set MyRange = Range(MyRange, MyRange.End(xlDown))

    Sheets("Template").Select 'select the template worksheet
    Range("A1:Z149").Select 'select area you needed
    Range("Z149").Activate 'range end point
    Application.CutCopyMode = False
    Selection.Copy 'copy the content

    For Each MyCell In MyRange
    Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
    Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
    ActiveSheet.[A1].Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste
    ActiveSheet.[A150] = ActiveSheet.Name 'create the reference (worksheet name) for vlookup
    Next MyCell
    End Sub

  2. #2
    Registered User
    Join Date
    01-03-2014
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    71

    Re: Row Height

    the sheet you want to paste to

    Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
    Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
    ActiveSheet.[A1].Select
    With Selection.RowHeight = ENTER THE SPECIFIC ROW HEIGHT YOU WANT -----------------------------------ADD THIS
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste
    ActiveSheet.[A150] = ActiveSheet.Name 'create the reference (worksheet name) for vlookup
    Next MyCell
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Last edited by AllenF; 02-12-2016 at 04:01 PM.

  3. #3
    Registered User
    Join Date
    01-22-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    20

    Re: Row Height

    No, I can't do it because different rows are having difference height.
    If I'm correct, your method only can apply to a fixed height.

    Quote Originally Posted by AllenF View Post
    the sheet you want to paste to

    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

    add .RowHeight =**?** ' add your row height in there

  4. #4
    Registered User
    Join Date
    01-22-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    20

    Re: Row Height

    No, I can't do it because different rows are having difference height.
    If I'm correct, your method only can apply to a fixed height.

    Quote Originally Posted by AllenF View Post
    the sheet you want to paste to

    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

    add .RowHeight =**?** ' add your row height in there

  5. #5
    Registered User
    Join Date
    01-03-2014
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    71

    Re: Row Height

    Quote Originally Posted by Uolsiec View Post
    I have to copy the height of A1:A149 from Template, worksheet, to the ActiveSheet
    Is that a fixed height? or are you saying they are different heights
    If they are different heights try adding

    .PasteSpecial xlPasteFormats

  6. #6
    Registered User
    Join Date
    02-11-2016
    Location
    Lincoln, CA
    MS-Off Ver
    2010
    Posts
    8

    Re: Row Height

    Sub SetRowHeight()
    Rows("1:149").RowHeight = 35
    End Sub

    Check out: www.vbamacros.net

  7. #7
    Registered User
    Join Date
    02-11-2016
    Location
    Lincoln, CA
    MS-Off Ver
    2010
    Posts
    8

    Re: Row Height

    Sub SetRowHeight()
    Rows("1:149").RowHeight = 35
    End Sub

    Check out: www.vbamacros.net

    RowHeight = (this number can be adjusted)

  8. #8
    Registered User
    Join Date
    01-22-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    20

    Re: Row Height

    It's not fixed height.
    I failed to add the code. Shall I know where I should put it in?
    It stated "Invalid or unqualified reference".

    Quote Originally Posted by AllenF View Post
    Is that a fixed height? or are you saying they are different heights
    If they are different heights try adding

    .PasteSpecial xlPasteFormats

  9. #9
    Registered User
    Join Date
    01-22-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    20

    Re: Row Height

    Quote Originally Posted by jbaillie View Post
    Sub SetRowHeight()
    Rows("1:149").RowHeight = 35
    End Sub

    Check out: www.vbamacros.net

    RowHeight = (this number can be adjusted)
    Thanks jbaillie,
    However, it's not fixed.

  10. #10
    Registered User
    Join Date
    01-03-2014
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    71

    Re: Row Height

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    01-22-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    20

    Re: Row Height

    Quote Originally Posted by AllenF View Post
    Please Login or Register  to view this content.
    Good solved.

  12. #12
    Registered User
    Join Date
    01-22-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    20

    Re: Row Height

    One more questions is how i can add the autofilter in column E. I need no "blank" is showing in column E.
    Sub CreateSheetsFromAList_Correct()
    Dim MyCell As Range, MyRange As Range

    Set MyRange = Sheets("Summary").Range("A8") 'Summary is the basis worksheet and A8 is the starting point
    Set MyRange = Range(MyRange, MyRange.End(xlDown))

    Sheets("Template").Select 'select the template worksheet
    Range("A1:Z149").Select 'select area you needed
    Range("Z149").Activate 'range end point
    Application.CutCopyMode = False
    Selection.Copy 'copy the content

    For Each MyCell In MyRange
    'creates new worksheet
    Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
    Sheets(Sheets.Count).Name = MyCell.Value 'renames the new worksheet

    'paste the template to new worksheet
    ActiveSheet.[A1].Select 'pastes destination
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False ' pastes special column width
    ActiveSheet.Paste 'paste the formula and data

    'use the worksheet name as key for formula
    ActiveSheet.[A150] = ActiveSheet.Name 'create the reference (worksheet name) for vlookup

    'autofilter the "v" in column E
    ActiveSheet.Range("A1:F150").AutoFilter
    ActiveSheet.Range("A1:F150").AutoFilter Field:=5, Criteria1:="<>"


    Next MyCell

    End Sub
    Last edited by Uolsiec; 02-13-2016 at 03:16 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 09-25-2015, 11:49 AM
  2. Replies: 4
    Last Post: 03-21-2015, 08:18 PM
  3. Replies: 2
    Last Post: 01-12-2015, 02:52 PM
  4. Replies: 2
    Last Post: 03-06-2014, 01:57 PM
  5. Macro to autofit row height, and then reduce back to original height
    By LesleyRicco in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-03-2013, 09:12 PM
  6. Replies: 17
    Last Post: 02-25-2009, 10:09 AM
  7. Replies: 3
    Last Post: 04-23-2007, 01:55 AM

Tags for this Thread

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