Thread: Sheet name in cell need to reference

Hi All

Firstly, thank you all for your help - I've been reading the forum for some time but only now have not been able to find the solution to my problem.

I have created a book to keep track of contracts. I have an oboarding sheet which I enter the details of the new contract and then I have a macro which moves it into my active sheet.

With more contracts I have had to separate these out into other sheets based on the name of the client.

What I want to do is have a cell on the onboarding sheet which I can enter the name of the sheet and have the contract placed into that sheet.

My VB code is:

Sheets("Active").Select
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Selection.Font.Bold = False
Sheets("Onboarding").Select
Range("C2:C16").Select
Selection.Copy
Worksheets("Active").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Range("A2").Select
Sheets("Onboarding").Select
Application.CutCopyMode = False
Range("C2:C16").Select
Selection.ClearContents
Range("C2").Select


The bold sections are where I would like to put the name of the sheet from the cell. The cell with the name in is C20, I have tried the obvious:

Sheets("C20").Select

Sheets(C20).Select

And some indirect function, but these don't seem to work - any ideas?

Not tested but try this:

Code:

With Sheets(Sheets("Onboarding").Range("C20").Value)
Rows("2:2").Insert Shift:=xlDown
Selection.Font.Bold = False
End With
Sheets("Onboarding").Range("C2:C16").Copy
With Worksheets(Sheets("Onboarding").Range("C20").Value).Range("A2")
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
End With
With Sheets("Onboarding")
Application.CutCopyMode = False
.Range("C2:C16").ClearContents
End With

Hi Simon

Thanks for this!

It didn't work as is, but taking it apart it seems the problem is with the first few lines.

i now have the following code:

Sheets("Active").Select
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Selection.Font.Bold = False
Sheets("Onboarding").Range("C2:C16").Copy
With Worksheets(Sheets("Onboarding").Range("C20").Value).Range("A2")
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
End With
With Sheets("Onboarding")
Application.CutCopyMode = False
.Range("C2:C16").ClearContents
End With

This inserts the line into Active adn then copies all of the data across as I wanted.

UI've treid replacing the first few lines, but this doesn work.

so essentially now my question is much simpler - how do you insert a line into a sheet named by a cell?

OK, so it's sorted now, a bit of tinkering and found the right syntax.

In case anyone is interested, here's the final result (oh and thanks again Simon, you've really tidied up my code!):

With Worksheets(Sheets("Onboarding").Range("C20").Value).Select
Rows("2:2").Insert Shift:=xlDown
End With
With Worksheets(Sheets("Onboarding").Range("C20").Value).Range("2:2").Select
Selection.Font.Bold = False
End With
Sheets("Onboarding").Range("C2:C16").Copy
With Worksheets(Sheets("Onboarding").Range("C20").Value).Range("A2")
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
End With
With Sheets("Onboarding")
Application.CutCopyMode = False
.Range("C2:C16").ClearContents
End With