New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

New worksheet selection using "text" in cell on active sheet

Macros, VBA, Excel Automation, etc.

New worksheet selection using "text" in cell on active sheet

Postby Lazmail » Tue Feb 03, 2015 7:11 am

Code: Select all
Sub PostToSheet()

    Dim WS1 As Worksheet
    Dim WS2 As Worksheet
    Set WS1 = Sheets("Sheet1")
    Set WS2 = Sheets("Sheet2")
    Set WS2 = Sheets("Jan15")
    Set WS2 = Sheets("Dec14")
    ' Figure out which row is the next row
    NextRow = WS2.Cells(Rows.Count, 44).End(xlUp).Row + 1
    'Write the important values to the sheet 2
    WS2.Cells(NextRow, 44).Resize(1, 5).Value = Array(WS1.Range("D1"), WS1.Range("B3"), WS1.Range("F3"), WS1.Range("E1"), WS1.Range("F1"))

'Sub CleardataIncreasenumber()
'
' CleardataIncreasenumber Macro
' Clears the pertinent data and increases the receipt number
'

'
    Range("B3,F3").Select
    Selection.ClearContents
    Range("F1").Value = Range("F1").Value + 1
   
End Sub


I am trying to use the above VBA code to select a worksheet named Jan15 through to Dec15 which is generated by a cell (L1) located on the active sheet1.
The format for this cell is generated by the formula =text(D1,"MMMYY") where D1 is formatted with =now()

If I change the text in the statement Set WS2= Sheets("sheet2") to read Set WS2 = Sheets("Jan15") then the important data is written to the Worksheet (Jan15) or whatever sheet name that I have available.

How do I select the contents of the cell (L1) and get it to replace he Sheet2 value as a macro.
  • 0

Last edited by pecoflyer on Tue Feb 03, 2015 8:13 am, edited 1 time in total.
Reason: Corrected code tags
Lazmail
Rookie
 
Posts: 14
Joined: Feb 3, 2015
Reputation: 0
Excel Version: 2007

Re: New worksheet selection using "text" in cell on active sheet

Postby pecoflyer » Tue Feb 03, 2015 8:15 am

Hi and welcome to the board.

In the future, please post VBA in the VBA forum. I will move it.

As for the code tags, first select the code, then click on the "code" button, so all code is now between the tags. I corrected it.

Thank you
  • 0

A relevant topic title helps get faster and more answers
pecoflyer
Moderator
 
Posts: 1274
Joined: Jan 24, 2012
Location: Belgium
Reputation: 39
Excel Version: 2003/2007/2010

Re: New worksheet selection using "text" in cell on active sheet

Postby NoSparks » Tue Feb 03, 2015 11:11 am

This should do what you're after. Message box is only to show what WS2 is.
Code: Select all
Sub PostToSheet()

    Dim WS1 As Worksheet
    Dim WS2 As Worksheet
   
    Set WS1 = Sheets("Sheet1")
    Set WS2 = Sheets(WS1.Range("L1").Value)
   
    MsgBox WS2.Name

    '
    ' rest of your code here
    '
   
End Sub
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: New worksheet selection using "text" in cell on active sheet

Postby Lazmail » Tue Feb 03, 2015 10:16 pm

Thanks NoSparks,

Unable to check your response at this moment, I am at work, but will try when I get home.

Cheers
Lazmail
  • 0

Lazmail
Rookie
 
Posts: 14
Joined: Feb 3, 2015
Reputation: 0
Excel Version: 2007

Re: New worksheet selection using "text" in cell on active sheet

Postby Lazmail » Thu Feb 05, 2015 12:27 am

Hi NoSparks,

Tried your remedy but Macro runs to the
Set WS2 = Sheets(WS1.Range("L1").Value)
, but WS2 but still comes up with
<Subscript out of range>
error message as well as
WS2=Nothing.


Am at a loss
  • 0

Lazmail
Rookie
 
Posts: 14
Joined: Feb 3, 2015
Reputation: 0
Excel Version: 2007

Re: New worksheet selection using "text" in cell on active sheet

Postby NoSparks » Thu Feb 05, 2015 12:40 am

If you run only what I posted without the rest of your code does the message box display the sheet name from cell "L1" ? and is that the actual name of a sheet ? ie: no spaces or anything that makes what's in L1 different from the sheet name.
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: New worksheet selection using "text" in cell on active sheet

Postby Lazmail » Thu Feb 05, 2015 1:16 am

Hi NoSparks,

I have discarded all the rest of my macro and only used your Macro up to the MsgBox WS2.Name.

The error message still comes up with the messages
Sheets(WS1.Range("L1").Value)=<Subscript out of range>
WS2.name=<Object variable or With block variable not set>
  • 0

Lazmail
Rookie
 
Posts: 14
Joined: Feb 3, 2015
Reputation: 0
Excel Version: 2007

Re: New worksheet selection using "text" in cell on active sheet

Postby NoSparks » Thu Feb 05, 2015 1:29 am

With the attached file I have combined the code I posted with the code you posted, I've commented out the part at the bottom that is supposed to clear cells and increment another. Does this not do what you have asked ?
  • 0

You do not have the required permissions to view the files attached to this post.
NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: New worksheet selection using "text" in cell on active sheet

Postby Lazmail » Thu Feb 05, 2015 1:47 am

Hi No Sparks,

I am ecstatic after nearly 9 months of stuffing around you have come up with the answer for me.

I had to add a space before the name in tab and it works. i can now post the required information to any sheet name that coincides with the date shown in my cell (L1)

I can not find the space in your macro but I am not that worried, the macro now works, I only have to remember to name the sheet name with a "space" at the commencement of the sheet name
  • 0

Lazmail
Rookie
 
Posts: 14
Joined: Feb 3, 2015
Reputation: 0
Excel Version: 2007

Re: New worksheet selection using "text" in cell on active sheet

Postby NoSparks » Thu Feb 05, 2015 2:08 am

That doesn't sound right, check that your formula in L1 doesn't have a leading space in the format for the cell, within the quotes ahead of the MMMYY.
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Next

Return to Macros and VBA Questions

Who is online

Users browsing this forum: Google Adsense [Bot] and 60 guests