Copy cell to a new sheet on same place

[elmnas]

Hi guys Could someone help me,

I use this the variable: FindCol_RowID="sv" (that is the search string the script looking for in a sheet)

then in the msgbox I get the ID of the cell

but could someone help me to

paste the cell + column into a new sheet on the same place?

See code below:

Dim FindCol_RowID As String
Dim aCell As Range


FindCol_RowID = "sv"


Set aCell = Sheet1.Rows(1).Find(What:=FindCol_RowID, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)


If Not aCell Is Nothing Then
MsgBox "the Cell is" & aCell.Address


Thank you in advance

 

[yky]

Where is the new sheet? Assuming the new sheet is "Sheet 2" in the same workbook, just add one line:

[COLOR=#333333]If Not aCell Is Nothing Then[/COLOR]
[COLOR=#333333]MsgBox "the Cell is" & aCell.Address
[/COLOR]ThisWorkbook.Sheets(2).Cells(aCell.Row, aCell.Column).Value = aCell.Value
End If

 

[elmnas]

thanks man,

Here is my code,

Could you help me to fit your code in my code? I dont get it work

Thank you in advance

Sub Langauge_Combination()




For Each sht In ActiveWorkbook.Worksheets
Set Rng = sht.UsedRange
Set MyRange = Rng


For Each MyCol In MyRange.Columns
For Each MyCell In MyCol.Cells


'MsgBox ("Address: " & MyCell.Address & Chr(10) & "Value: " & MyCell.Value)


If MyCell.Interior.ColorIndex = 23 Then








sht.Cells(MyCell.Row, MyRange.Columns(2).Column).Copy




'MsgBox "Language is: " & MyCol.Cells(1, 1).Text
 Dim objSheet As Worksheet
For Each objSheet In ActiveWorkbook.Sheets
   If objSheet.Name = MyCol.Cells(1, 1).Text Then
        End 'MsgBox "A worksheet with that name already exists."
 Worksheets(MyCol.Cells(1, 1).Text).Activate
    End If
      Next objSheet
      
      
       Worksheets.Add
       
       
       
       
       
       
       
       
       
        
     '   MsgBox "hej"
        






        
    ActiveSheet.Name = MyCol.Cells(1, 1).Text




    
            End If
        Next
    Next
Next
End Sub