1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2003 help to copy data to new tab and make data transparent

Discussion in 'Business Applications' started by Strybes, Mar 8, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. Strybes

    Strybes Thread Starter

    Joined:
    Sep 7, 2007
    Messages:
    67
    Excel 2003 SP3
    Could anybody suggest a way, using whatever method you prefer to copy existing data to another tab, but have the data go transparent by 50%(or thereabouts, so you can just see it), but keep the headings normal font.
    Thanks
     
  2. Strybes

    Strybes Thread Starter

    Joined:
    Sep 7, 2007
    Messages:
    67
    Hi All

    So i have copied the worksheet and even renamed it. Now all the data that I want transparent has a $ sign in front of it. Otherwise it starts at H4 but has X number across and down.
    Thanks for any help.
     
  3. Strybes

    Strybes Thread Starter

    Joined:
    Sep 7, 2007
    Messages:
    67
    Ok, now i have been able to change the colour which does what i want in essence.
    I had to use the exact range as i couldn't find a way to say go to end of data to right as there were some blank columns with no data, just a Main heading.
    I also found it difficult as I could grab data from top to bottome within a column, but i didn't then know how to keep that range and then add to it by going right.
    If you could show me how to do that it could be good for future reference.

    Sub InsertActualDataWorksheet()
    Worksheets("ProjData").Copy After:=Worksheets("ProjData")
    Worksheets("ProjData (2)").Name = "ActualData"

    End Sub
    Sub RangeShapes()

    Worksheets("ActualData").Range("H5:EK611").Select
    With Selection.Font
    .Name = "Arial"
    .FontStyle = "Regular"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = 24
    End With
    End Sub
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    1. When you programatically copy a sheet, the new sheet becomes active. So instead of:

    Worksheets("ProjData (2)").Name = "ActualData"

    you can (although it's no big deal) just use:

    ActiveSheet.Name = "ActualData"

    2. The top left corner of your data is H5, yes?

    x = Cells(5, Columns.Count).End(xlToLeft).Column

    gives you last used column in row 5.

    x = Cells(5, x).Address

    gives you the address for that in absolute format, e.g. $L$5.

    x = Mid(x, 2, 256)

    strips the first dollar sign (L$5).

    x = Left(x, InStr(x, "$") - 1)

    gives you everything before the (used to be first but is now) second and only dollar sign (L).

    y = Range("H" & Rows.Count).End(xlUp).Row

    then gives you the last used row in column H. All you need then is to cobble it all together:

    x = Cells(5, Columns.Count).End(xlToLeft).Column
    x = Cells(5, x).Address
    x = Mid(x, 2, 256)
    x = Left(x, InStr(x, "$") - 1)
    y = Range("H" & Rows.Count).End(xlUp).Row
    Range("H5:" & x & y).Select


    Of course you could crunch that down, I'm just showing you the steps. At the end of that is Select. You may not need to actually select but ... it's difficult to figure from "make transparent" what you're trying to do, exactly. :D

    If all you're trying to do is (font-colour) the whole range, then:

    Range("H5:" & x & y).Font.ColorIndex = 24

    ought to do it.

    HTH :)
     
  5. Strybes

    Strybes Thread Starter

    Joined:
    Sep 7, 2007
    Messages:
    67
    Thanks bomb #21, that works better.
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/807436

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice