pastespecial

[eran3185]

hi
i have a macro that take some files and copy them into one sheet.
but i want to use pastespecial instead simple copy .
ie : in book2 , i can't see c3 .
HOW CAN I DO THIS THING ?

THIS IS MY CODE :

Sub filecopy()
Application.ScreenUpdating = False
aw = ActiveWorkbook.Name
directory = "c:\test"
ChDir directory
myfile = Dir(directory & "/*.xls")
Do Until myfile = ""
Workbooks.Open myfile
Sheets(1).Select
Windows(myfile).Activate

Range("a1:d100").Select
Selection.Copy
Workbooks(aw).Activate
Sheets(1).Select
[a65536].End(xlUp).Offset(1, 0).Select

ActiveSheet.Paste
Application.CutCopyMode = False


Workbooks(myfile).Close False
myfile = Dir
Loop
Application.ScreenUpdating = True
End Sub

 

[Smitty]

This may do it for you:

    Range("a1:d100").Copy
    Workbooks(aw).Activate
    With Sheets(1)
        .Range("a65536").End(xlUp).Offset(1, 0).Select
        .Paste
        With .Cells
            .Copy
            .PasteSpecial Paste:=xlPasteValues
        End With
    End With

HTH,

Smitty

 

[eran3185]

thenk's

it work ...

 

[eran3185]

problem

for some reason , it's not work
this macro use the simple copy-paste , not PasteSpecial

why ?

 

[Lewiy]

Not tested, but does this work?

Workbooks(aw).Sheets(1).Range("a65536").End(xlUp).Offset(1, 0).Value =  Range("a1:d100").Value

 

[Lewiy]

Actually, it won’t work cos you’re working with a multiple cell range, sorry.

 

[eran3185]

there is no way to solve this problem ???

 

[eran3185]

i think that the problem is that i have some files .
some of them are protected by a password.
but i dont know the password .