+ Reply to Thread
Results 1 to 2 of 2

Copy Formatting from hidden row to range created by UserForm

Hybrid View

  1. #1
    Registered User
    Join Date
    12-29-2014
    Location
    Indiana
    MS-Off Ver
    Excel 2010
    Posts
    4

    Copy Formatting from hidden row to range created by UserForm

    My spreadsheet is used to for sample data and the number of samples changes from run to run. I currently have a macro that inserts a row and then copies the formatting from a hidden row and pastes the formatting into the inserted row.
    I have a spreadsheet with a UserForm that starts when the spreadsheet is opened and asks the user to specify how many rows they need inserted into spreadsheet. The code I have for the UserForm inserts the rows just fine but I am having a hard time copying the formatting and pasting in all the rows the UserForm inserted. My code for the UserForm is as follows;

    [Option ExplicitSub CloseButton_Click()
    Unload Me
    End Sub


    Sub InsertRowsButton_Click()


    ' Make sure a number is entered
    If NumberofRows.Text = "" Then
    MsgBox "You must enter a number."
    NumberofRows.SetFocus
    Exit Sub
    End If

    ActiveSheet.Unprotect
    Range("E20").Activate
    With ActiveCell
    .End(xlDown).Offset(0, 0).Activate
    End With


    ActiveCell.Offset(1).EntireRow.Resize(NumberofRows).Insert

    ' This is the hidden range that contains the formatting that needs to be copied
    Range("A20:V20").Copy

    ' This is where I think my code is going wrong because it is not selecting the whole range that was inserted
    ActiveCell.Offset(1, -4).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

    ' Clear the controls for the next entry
    NumberofRows.Text = ""
    NumberofRows.SetFocus

    Unload Me

    End Sub]

  2. #2
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Copy Formatting from hidden row to range created by UserForm

    Hi smpatty08

    Welcome to the Forum!

    Please use Code Tags around your Code...it's a Forum Requirement. To do so, in your Original Post, highlight your code and click the # icon at the top of your post window

    Your issue can perhaps be easily resolved.

    Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

    Include in the attachment any code you're currently using (whether it works or not) and, if appropriate, an "After" worksheet that demonstrates what you wish the output to be.

    To Attach a File:

    1. Click on Go Advanced
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the "Add Files"... button to locate your file for uploading.
    6. This will open a new window File Upload...Click "Select Files"
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the "Upload Files" button and wait until the file has uploaded.
    10. Click the "Done" Button.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

+ 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. copy paste macros
    By rrk2008 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-31-2014, 06:42 AM
  2. Replies: 0
    Last Post: 01-15-2014, 05:17 PM
  3. Created "+" button on a userform and need it to add 1 to a cell range
    By simeonmein in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 09-18-2013, 08:32 AM
  4. VBA to copy heading when new sheet is created via userform
    By jaarthur in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-28-2013, 10:56 AM
  5. Copy Range to a Hidden Sheet
    By soup23 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-21-2012, 03:25 PM

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