Finding and Moving Cells From Sheet to Sheet

[ross88guy]

Hi Everyone,



I am looking to do the following. a1) Find a cell called 'test' in sheet2 of my workbook.

2) Cut the value in the cell to the right of the 'test' cell

3 Find a cell called 'test' in sheet1

4) Finally, paste the value found in step2 to the cell to the right of the cell found in step3.



My code is below, any help will be much appreciated!



Kind Regards



Ross

Option Explicit

Sub Arrange_Cells()
Dim sourcecell As Range, sourcecell2 As Range, sourcecell3 As Range, destinationcell As Range, destinationcell2 As Range, sourcerange As Range
Dim matchrow As Integer, matchrow2 As Integer


Set sourcecell = Cells.Find(What:="Test*", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

    Set sourcerange = sourcecell.Offset(0, 2) 'Selects cell in same Row, 2 Columns RIGHT
    sourcerange.Cut


                Set destinationcell = Cells.Find(What:="Test*", After:=Worksheets(1).Cells(1, 1), LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
                
                Set destinationcell2 = destinationcell.Offset(0, 2)
                
                Set sourcecell3 = sourcecell2

               sourcecell3.Paste Destination:=Worksheets(1).Range(destinationcell2)



Set sourcecell = Nothing

End Sub

[Domski]

Maybe:

Sub Arrange_Cells()

Dim sourcecell As Range, destinationcell As Range

Set sourcecell = Sheets("Sheet2").Cells.Find(What:="Test*", LookIn:=xlValues, LookAt:=xlPart)

Set destinationcell = Sheets("Sheet1").Cells.Find(What:="Test*", LookIn:=xlValues, LookAt:=xlPart)
                
destinationcell.Offset(0, 2).Value = sourcecell.Offset(0, 2).Value

End Sub

Dom

[ross88guy]

Sorry for the confusion there Dom!

When I try and run your code, I get the following error:

"Object variable or With block variable not set"

for the line of code:

destinationcell.Offset(0, 2).Value = sourcecell.Offset(0, 2).Value

Any ideas?

[Domski]

That means it hasn't found a cell containing Test on either Sheet1 or Sheet2.

Can you post a sample workbook that demonstrates the error occurring?

Dom

[ross88guy]

Changed the offset values to 1 instead of 2 and it worked a treat!

Thank you very much again Dom!

[Domski]

No worries. Please mark the thread Solved.

Oh, and don't forget to put your code in tags in future

Dom