Selecting worksheet when name is a variable????

[Matty5894]

Afternoon All,

Quick question i'm hoping you may help me with as i'm probably being a Numpty.

I'm running an import data macro that will continously pull the same data 'solved' and run some analysis on this data.

I know how to write the script to analyse the data too.

I JUST CAN'T ACTIVATE THE RIGHT SHEET?

I'm very new to macros and use this site alot as a guide :D

Below is the full code so far, problem area is

Worksheets(1).Activate

Range("O2").Select
ActiveCell.FormulaR1C1 = "INSERT INFORMATION AND SO ON..."

This above bit doesn't work? I also dont know what the sheet name is as it will continually change by the information put into the TextBox.

Dream choose is to choose a sheet based on the text box name somehow whatever it is chosen to be.

If not just selecting the first worksheet is fine as this is where the new tab is added.

Cheers as always :D


Private Sub CommandButton1_Click()

'Creating a new tab and naming it

Dim ws As Worksheet

If TextBox1 = vbNullString Then Exit Sub

On Error Resume Next

Application.DisplayAlerts = False
Sheets.Add().Name = TextBox1

' Get customer workbook...
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook

' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook

' get the customer workbook
filter = "Text files (*.xlsx),*.xlsx"
caption = "Please Select an input file "
customerFilename = Application.GetOpenFilename(filter, , caption)

Set customerWorkbook = Application.Workbooks.Open(customerFilename)

' Set Range
' copy data from customer to target workbook
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(1)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets("Table1")

targetSheet.Range("A1", "F500").Value = sourceSheet.Range("E1", "J500").Value

' Close customer workbook
customerWorkbook.Close


Worksheets(1).Activate

Range("O2").Select
ActiveCell.FormulaR1C1 = "INSERT INFORMATION AND SO ON..."


End Sub

[nathansav]

worksheets(sheetName).activate

[Norie]

How is the code not working?

PS Can you add code tags when posting code?

[Matty5894]

The name is determined by the information put into the TextBox - earlier part of the code therefore I can't choose the sheet by inputting the name.

When I try to start to run analysis on the data imported, see after worksheet(1).Acvitate end of code

In cell O2 it should have the text "INSERT INFORMATION AND SO ON...", it doesn't and I dont understand why?

once this is solved i can contiune to add text and formula into cells to build my table.

Does this make sense?

Cheers

[Norie]

Which worksheet should O2 be on?

Is it the newly created worksheet, or is it another worksheet?

Also, if the text isn't going into O2 on the correct worksheet which worksheet is it going in?

[Matty5894]

The text needs to be going on the newly created sheet

[Norie]

Try this.

Please Login or Register  to view this content.

[Matty5894]

Works like a dream! Thankyou very much \o/