+ Reply to Thread
Results 1 to 7 of 7

Save New Worksheet as String using names of information from sourcesheet

  1. #1
    Registered User
    Join Date
    09-25-2017
    Location
    US
    MS-Off Ver
    Pro Plus 2013
    Posts
    7

    Save New Worksheet as String using names of information from sourcesheet

    Hi,

    I am trying to save a new worksheet using a value in my sourcesheet and the tab name of the sourcesheet. My code works well if I just use the value, but as soon as I add the sourcesheet name code, I get an error. Can you help?

    [code]
    Sub NameNewWorksheet

    Dim Name as String
    Dim sourcesheet As Variant

    activesheet.copy
    Name = sourcesheet.range("A1").Value & " " & sourcesheet.Name 'sourcesheet.Name is where my code isn't working. If I just do the A1 value it works
    ActiveWorkbook.SaveAs Filename:=Name

    End Sub
    [\code]

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,006

    Re: Save New Worksheet as String using names of information from sourcesheet

    "sourcesheet" isn't a sheet, it is a variant with no value assigned to it...

    I assume sourcesheet is supposed to be the original worksheet you are copying from?
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,006

    Re: Save New Worksheet as String using names of information from sourcesheet

    Since it is the activesheet prior to the copy, no need for the extra variable, just set the Name variable before you copy?

    Please Login or Register  to view this content.

  4. #4
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,720

    Re: Save New Worksheet as String using names of information from sourcesheet

    .
    Another :

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    12-02-2010
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2013, 2016
    Posts
    100

    Re: Save New Worksheet as String using names of information from sourcesheet

    Hello vlr516,

    If you are just looking to save the same file as a different name utilizing cell data then I would try this adaptation of your current code:

    Please Login or Register  to view this content.
    It doesn't have the copy function in there, so I don't know if you are wanting to keep each version of it open simultaneously or not. If so you could make the workbook have a rename function built off the Workbook Open trigger. Just a thought.

    Hope this helps,

    DarkF1ame
    Mark as "Solved" and add Reputation if applicable.

  6. #6
    Registered User
    Join Date
    09-25-2017
    Location
    US
    MS-Off Ver
    Pro Plus 2013
    Posts
    7

    Re: Save New Worksheet as String using names of information from sourcesheet

    DarkF1ame,

    I am trying to save a version within the original workbook and have a copy of just that tab because it will need to be uploaded. Is my code appropriate for what I want to do?

    Thanks so much.

  7. #7
    Forum Contributor
    Join Date
    12-02-2010
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2013, 2016
    Posts
    100

    Re: Save New Worksheet as String using names of information from sourcesheet

    vlr516,

    This should address that. In my version of the test workbook it only copies the current sheet and uses the sheet name and the value in A1 for the name of the new macro-enabled workbook. When you run the macro it won't check for another sheet with the same name, so you can either add that or deal with overwriting issues. Hope it helps:

    Please Login or Register  to view this content.
    DarkF1ame

+ 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: 0
    Last Post: 10-19-2015, 11:27 AM
  2. [SOLVED] Output in same sourcesheet
    By macrolearnerkk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-27-2015, 03:49 PM
  3. [SOLVED] Attachments : Save and Rename using Sublect string or File name string
    By checksamir in forum Outlook Programming / VBA / Macros
    Replies: 4
    Last Post: 11-13-2014, 01:13 AM
  4. [SOLVED] Worksheet Names macro that pastes names as hyperlinks
    By bungaree in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-04-2013, 07:15 PM
  5. Pulling out single names from a string of names
    By HBF in forum Excel General
    Replies: 4
    Last Post: 02-23-2013, 08:25 AM
  6. Find string and save...insert another string
    By twckfa16 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-21-2013, 04:48 PM
  7. How to declare variable names depending on worksheet names
    By Dynelor in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-28-2008, 08:43 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