Copy text from a sheet to another depending on where copy comes from
<p>I have an excel book with numerous sheets, at the moment I have a macro to copy two cells to another sheet then print,</p>

<p>What I would like to do now is depending on what sheet the copy comes from then paste them in a dedicated sheet,</p>

<p>so like if I copy from sheet one paste into sheet 11, copy from sheet 2 the paste into sheet 12 and so on,</p>

<p>The code below worked to copy from whichever sheet I was in to one sheet, I now added the if statements at the top to try and copy to dedicated sheet but this does not work and has an error,</p>

<pre><code>Sub Tag_75()

Application.ScreenUpdating = False

Sheets("lb").Visible = True

Dim lb As Worksheet
Dim sh As Worksheet
Set sh = ActiveSheet

If sh = "Sheet1" Then
    lb = "Sheet11"

ElseIf sh = "Sheet2" Then
    lb = "Sheet12"

ElseIf sh = "Sheet3" Then
    lb = "Sheet13"

ElseIf sh = "Sheet4" Then
    lb = "Sheet14"

End If

ActiveCell.Resize(1, 1).Copy Worksheets("lb").Range("A1")

ActiveCell.Offset(, 1).Resize(1, 1).Copy Worksheets("lb").Range("A2")

Sheets("lb").Select
    Range("A1:A2").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

Worksheets("lb").Range("A2").WrapText = True
Worksheets("lb").Range("A1:A2").Font.Size = 22
Worksheets("lb").Range("A1:A2").ShrinkToFit = True

Dim STDprinter As String

STDprinter = Application.ActivePrinter
'Application.ActivePrinter = "\\SBS2011\Xerox WorkCentre 7830 on Ne04:"
Application.ActivePrinter = "\\JCSP55J\DYMO LabelWriter 450 (Copy 2) on Ne03:"
ActiveSheet.PrintOut
Application.ActivePrinter = STDprinter

Sheets("lb").Visible = False

sh.Activate

End Sub
</code></pre>

<p>Can anyone shed some light on the if statement</p>

<p>Thanks</p>