+ Reply to Thread
Results 1 to 17 of 17

Select Range Paste Special

  1. #1
    Forum Contributor
    Join Date
    07-23-2006
    Posts
    185

    Select Range Paste Special

    I'm not to familiar with macros or VBA so I hope someone can help me out with this.

    I have 2 worksheets in this workbook. I want to select the range A2:K3 (or better yet, the first 2 data rows); the first row 1 contains column headers.

    Then copy those two rows.

    Next, select everything else in the worksheet and then Paste Special>Formats
    Then select cell A1 (just to give the active cell a place to rest) and then do the same to the second worksheet.

    Then save the workbook.

    My code below is pretty much a recorded macro. I'm basically wanting to change this macro to be expandable as the worksheet grows.

    Thanks and Dim=.Happy.New.Year !


    Please Login or Register  to view this content.
    Last edited by additude; 12-29-2007 at 07:17 AM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Can you explain exactly what you intend for this code to do? I see no point in Copying a range & Pasteing Formats. Also, you do not need to select any ranges when copying.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Contributor
    Join Date
    07-23-2006
    Posts
    185
    The first two rows have cell formats that alternate visually.

    I need to copy those alternating visual formats to the remainder of the worksheet.

  4. #4
    Forum Contributor
    Join Date
    07-23-2006
    Posts
    185
    I have been able to define a "Range" anchor by Rows and Columns using this:

    Please Login or Register  to view this content.
    Now I need to get this to select my Range but I am having a problem there.

    I'm trying this which of course errors:
    How can I define my "rowcount" and "columncount" to get it as the second anchor for this Range().Select formula?
    Please Login or Register  to view this content.

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    You can set Excel to format cells in a table of data automatically, based on existing data in the Tools menu. Select Options, then open the Edit tab and ensure that Extend data range format & formulas is checked.

  6. #6
    Forum Contributor
    Join Date
    07-23-2006
    Posts
    185
    Quote Originally Posted by royUK
    You can set Excel to format cells in a table of data automatically, based on existing data in the Tools menu. Select Options, then open the Edit tab and ensure that Extend data range format & formulas is checked.
    That is just not what I am trying to do.

    I explained what I am trying to do.

    Thanks.

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Ar you sure that your name is not "Attitude"?

    if you want to be rude then solve it yourself!

  8. #8
    Forum Contributor
    Join Date
    07-23-2006
    Posts
    185
    royUK,

    I didn't mean anything other than I knew what it was that I wanted to accomplish.

    Thanks for evaluating my request to make sure I wasn't progessing in an inefficient manner as you pointed out that you saw, "no point in Copying a range & Pasteing Formats.."

    But yet still however, thats what I needed to do and what was the basis of my request; which BTW, I thought I posted in a groomed and explanitary manner, unlike many requests in this forum.

    Thank you for your input and willingness to help me.

    However, I came up with the bit of code below on my own. It took several trials and errors to get it to do what I wanted, but alas it is.

    Now I am hoping that some of the more versed coders than I who are here can give me some feedback on my attempt and see if there is a better way of getting this done.

    I suppose I haven't added any error checking, but to be honest, I'm not sure what I need to check for.

    Any help is greatly appreciated.

    Additude.. NOT Attitude...


    Please Login or Register  to view this content.
    Edited:
    Please Login or Register  to view this content.
    to:
    Please Login or Register  to view this content.
    As changing Columns.Count to 1 makes it check the headers where there is a better chance that there will be data in every row cell across the worksheet. As it was, Columns.Count equaled 256 and it just so happened I didn't have some data all the way across in row 256 of ws2, so it gave me a headache for a bit.

    Anyway, in case someone copies my code, I updated it.
    Last edited by additude; 12-29-2007 at 05:31 PM.

  9. #9
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Your response came across obviously not how you intended.

    Your code needs tidying up - you haven't declared all your variables. Try this, it will format the sheet's used range based on the format in rows 2 & 3

    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    07-23-2006
    Posts
    185
    I would have not figured out within the near future how to make the macro cycle thru each Worksheet. For one thing I couldn't get '.Range("A1").Select' to work, but now realize '.Cell(1,1).Select' is what to use when inside a With.

    So anyway, the code you presented was not relocating the ActiveCell to "A1" and therefore left the worksheets highlited/selected.

    So I changed the code a bit and inserted '.Cells(1, 1).Select' that accomplished this in only one of the worksheets, leaving the other worksheet still selected.

    I've made several unsuccessful attempts at getting the second worksheet to make "A1" active - highlited therefore de-selecting the copy-to range before saving.

    Do you have a suggestion that would place the highlited active cell at "A1" in both worksheets before saving so as to de-select everything and tidy it up?

    Here is where I am at:

    Please Login or Register  to view this content.

  11. #11
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    The code that I provided does not actually Select any ranges so the only cells selected after running the code would have been previosly selected. If you want to activate A1 in each sheet try this

    Please Login or Register  to view this content.
    Referring to a range as A1 or Cells(1,1) is only a matter of preference, they both work the same. However, by using the Cells(1,1) method you do not need to find the Column Letter as your earlier code did.

  12. #12
    Forum Contributor
    Join Date
    07-23-2006
    Posts
    185
    Thanks. .GoTo was it.

    The macro selected or highlighted something at:

    Please Login or Register  to view this content.
    I stepped it thru and that's where it was happening. Nothing was selected prior to running the macro.

    I appreciate the help and sorry for the initial misunderstanding.

  13. #13
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    cant see why you should have a problem here. It works fine in m test workbook. Can yo attach an example? You will need to zip it

  14. #14
    Forum Contributor
    Join Date
    07-23-2006
    Posts
    185
    Attached...just a clip of the first 50 lines of each worksheet.
    Last edited by additude; 12-30-2007 at 12:25 PM. Reason: Removed Attachment

  15. #15
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Your example works fine for me, no errors.

  16. #16
    Forum Contributor
    Join Date
    07-23-2006
    Posts
    185
    We are talking about two different things then.

    I wasn't having any errors.

    I was looking for a way to de-select the items before the workbook was saved, which I thought selecting cell "A1" would accomplish. The selected cells/range is the "copy-to" range highlighted by the macro.

    I just wanted to "Tidy things up" so I didn't have a workbook/worksheet opened with selected/highlighted cells.

    The .GoTo accomplished that so everything is fine.

  17. #17
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    That's good, I did think the code was erroring for you.

+ 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