Paste issue

[Thecraftycarrot]

Hi i am having issues with the following code The first half works fine(It copies, but doesn't paste), its just the second half that is causing me an issue - i am getting run time error 438 -object doesn't support this property or method.

Dim UpdateRow As Long
Dim UpdateCol As Long
Dim ColName As String
Dim S3 As Worksheet
Dim S2 As Worksheet
Dim S1 As Worksheet


Set S3 = Sheets("SHEET3")
Set S2 = Sheets("SHEET2")
Set S1 = Sheets("SHEET1")



        Rowname = S1.Range("E16").Value
        UpdateRow = S2.Range("A3:a1000").Find(Rowname, LookIn:=xlValues).Row
        ColName = "Contract number"
        UpdateCol = S2.Range("A2:AZ2").Find(ColName, LookIn:=xlValues).Column
        S2.Select
        S2.Range(Cells(UpdateRow, UpdateCol), Cells(UpdateRow, UpdateCol).Offset(0, 29).Address).Copy
        
        Rowname = ArchContract.Range("a10000").End(xlUp).Row
        ColName = "Contract line number"
        UpdateCol = ArchContract.Range("A2:AZ2").Find(ColName, LookIn:=xlValues).Column
        ArchContract.Select
        ArchContract.Cells(Rowname, UpdateCol).Paste

 

[Yongle]

you did not say which line contains the error

but
instead of

S2.Range(Cells(UpdateRow, UpdateCol), Cells(UpdateRow, UpdateCol).Offset(0, 29)[COLOR=#ff0000].Address)[/COLOR].Copy

try

S2.Range(Cells(UpdateRow, UpdateCol), Cells(UpdateRow, UpdateCol).Offset(0, 29)).Copy

 

[Fluff]

What line gives the error?
Also what is ArchContract?
lso you need to change Paste to Pastespecial

 

[Thecraftycarrot]

Hi Both, thanks for the quick responses!

Please see the amended code - the archcontract was one of the tabs in the file, that i should of renamed - s3 - please see the updated code below.

Dim UpdateRow As Long
Dim UpdateCol As Long
Dim ColName As String
Dim S3 As Worksheet
Dim S2 As Worksheet
Dim S1 As Worksheet


Set S3 = Sheets("SHEET3")
Set S2 = Sheets("SHEET2")
Set S1 = Sheets("SHEET1")



        Rowname = S1.Range("E16").Value
        UpdateRow = S2.Range("A3:a1000").Find(Rowname, LookIn:=xlValues).Row
        ColName = "Contract number"
        UpdateCol = S2.Range("A2:AZ2").Find(ColName, LookIn:=xlValues).Column
        S2.Select
        S2.Range(Cells(UpdateRow, UpdateCol), Cells(UpdateRow, UpdateCol).Offset(0, 29).Address).Copy
        
        Rowname = S3.Range("a10000").End(xlUp).Row
        ColName = "Contract line number"
        UpdateCol = S3.Range("A2:AZ2").Find(ColName, LookIn:=xlValues).Column
        S3.Select
        S3.Cells(Rowname, UpdateCol).Paste

Yongle the code you proposed is what i originally had, but an error kept appearing (cannot remember which error of the top of my head), but problem was resolved with the additional .address code.

Fluff - the code that was giving me errors was the pasting line, and the pastespecial has resolved the issues i was having, thanks really appreciate it, i knew it would be something simple.

 

[Yongle]

glad you found a fix and thanks for your feedback

 

[Thecraftycarrot]

Yongle / fluff

I wonder if you could provide any insight in how i could adapt the above code i previously pasted that works, is still does but i need for another macro an adaption to this coding.

I need the above to loop for every instance of:

Rowname = S1.Range("E16").Value

that can be found in sheet 2, to be copied and pasted into the new sheet. then once pasted delete the row of data within sheet 2

 

[Fluff]

As this is now a totally different question, please start a new thread.