copy sheet if cell contains string

Valentin · Jul 19, 2011

need code: if cell A1 contains string copy sheet to already existing sheet with string name

John_w · Jul 19, 2011

I'm not sure what you mean by the 'with string name' part of your request. This copies Sheet1 to Sheet2 if cell A1 on Sheet1 contains 'ABC'.

Code:

Sub CS()
    With Sheets("Sheet1")
        If .Range("A1").Value = "ABC" Then
            .Cells.Copy Sheets("Sheet2").Cells
        End If
    End With
End Sub

Valentin · Jul 19, 2011

i have a file with 5 sheets,named: " import, john, mike, peter, arnold".
If the value of cell A1 from sheet import contains one of the sheet names:"john, mike, peter, arnold", copy the contents of sheet import to that sheet

John_w · Jul 19, 2011

Code:

Sub CS()
    With Sheets("import")
        If .Range("A1").Value = "john" Or .Range("A1").Value = "mike" Or .Range("A1").Value = "peter" Or .Range("A1").Value = "arnold" Then
            .Cells.Copy Sheets(.Range("A1").Value).Cells
        End If
    End With
End Sub

Valentin · Jul 19, 2011

Thank you for quick response

Valentin · Jul 19, 2011

I expressed myself wrongi have a file with 5 sheets,named: " import, john, mike, peter, arnold".
If the value of cell A1 from sheet import contains one of the sheet names:"john, mike, peter, arnold", the code has to lookup the sheet names and then copy the contents of sheet import to that sheet

I apologize for my bad english

Valentin · Jul 20, 2011

I have a file with 1 sheet,named: "import", the user of that file has to insert new sheets and renamed them.
then the user execute a macro to import text files.

The code i am looking for is:If the value of cell A1 from sheet" import" contains one of the inserted sheet names, copy the contents of sheet import to that sheet.

I apologize for my bad english

Domski · Jul 20, 2011

Try this:

Code:

Sub test()

With Sheets("Import")
    If SheetExists(.Range("A1").Value) Then
        .UsedRange.Copy Sheets(.Range("A1").Value).Range("A1")
    Else
        MsgBox "Worksheet " & .Range("A1").Value & " does not exist.", vbCritical
    End If
End With

End Sub

Function SheetExists(mySheet As String) As Boolean

On Error Resume Next
SheetExists = Len(Sheets(mySheet).Name)

End Function

Dom

Valentin · Jul 20, 2011

Thank you for quick response

copy sheet if cell contains string

Valentin

Board Regular

Excel Facts

John_w

MrExcel MVP

Valentin

Board Regular

John_w

MrExcel MVP

Valentin

Board Regular

Valentin

Board Regular

Valentin

Board Regular

Domski

Well-known Member

Valentin

Board Regular

Similar threads

Forum statistics

Share this page

copy sheet if cell contains string

Board Regular

Excel Facts

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

Board Regular

Board Regular

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock