Object Required - range

[NewED]

Hi,
I'm using this code, which is supposed to Run threw all excel files in a certain directory (c:\temp), and in each workbook that has a sheet called XXX - copy some cells from it into a specific Main-Sheet.
However one thing doesn't work for me:
I can use this line of code:
ws.Cells.Range("A1", "B2").Copy ws1.Cells.Range("A1", "B2")
but I can't use this line of code which supposed to do the same thing:
'ws.Cells.Range(Cells(1, 1), Cells(2, 2)).Copy ws1.Cells.Range(Cells(1, 1), Cells(2, 2))
The second line is needed because I further wish to make further manipulations to this code
Please check out the attached code
What am I doing wrong ?

Thanks



Sub ConFiles2222()


Dim Wbname As String
Dim Wb As Workbook
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim lngCalc As Long
Dim lngrow As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
lngCalc = .CalculationState
.Calculation = xlCalculationManual
End With


Set ws1 = ThisWorkbook.Sheets.Add
'change folder path here
FolderName = "C:\temp"


Wbname = Dir(FolderName & "\" & "b.*")
MsgBox "Wbname: " & Wbname
'ThisWorkbook.Sheets(1).UsedRange.ClearContents
Do While Len(Wbname) > 0
Set Wb = Workbooks.Open(FolderName & "\" & Wbname)
Set ws = Nothing
On Error Resume Next

'Wanted sheet name here
Set ws = Wb.Sheets("XXX")

On Error GoTo 0

If Not ws Is Nothing Then
wslastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
wslastcol = ws.Cells(3, Columns.Count).End(xlToLeft).Column
ws1lastcol = 1


' The following first line works, However the next one doesn't - why?
ws.Cells.Range("A1", "B2").Copy ws1.Cells.Range("A1", "B2")
'ws.Cells.Range(Cells(1, 1), Cells(2, 2)).Copy ws1.Cells.Range(Cells(1, 1), Cells(2, 2))

'This code doesn't work as well...
'ws1.Cells(1, 1).AutoFit


End If
Wb.Close False
Wbname = Dir
Loop


With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = lngCalc
End With
End Sub

[SamT]

Both of these should work. Cells.Range is redundant and Error prone

ws.Range("A1", "B2").Copy ws1.Range("A1", "B2")
ws.Range(Cells(1, 1), Cells(2, 2)).Copy ws1.Range(Cells(1, 1), Cells(2, 2))

Cells.Range is redundant except where the Object "Cells" is really a range of cells and Range is a range inside the outer Range and is referenced to the upper left Cell in the larger Range.
ws.Range("B3:E10").Range("B2") will return the Worksheet Range("C4")
ws.Range("B3:E10").Cells(3,3) will return the Worksheet Range("D5")

ws1.Cells(1, 1).AutoFit " Must be a row or a range of rows, or a column or a range of columns. Otherwise, this method generates an error."

Try
ws1.Rows(1).autofit
and
ws1.Columns(1:3).autofit

[NewED]

Hi Sam,
Thanks for your reply.
Changed the code as you suggested.
However, I get the following error:

"Run time error 1004.
Application defined or object defined error"

Any other suggestions ?


Sub ConFiles2222()


Dim Wbname As String
Dim Wb As Workbook
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim lngCalc As Long
Dim lngrow As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
lngCalc = .CalculationState
.Calculation = xlCalculationManual
End With


Set ws1 = ThisWorkbook.Sheets.Add
'change folder path here
FolderName = "C:\temp"


Wbname = Dir(FolderName & "\" & "b.*")
MsgBox "Wbname: " & Wbname
'ThisWorkbook.Sheets(1).UsedRange.ClearContents
Do While Len(Wbname) > 0
Set Wb = Workbooks.Open(FolderName & "\" & Wbname)
Set ws = Nothing
On Error Resume Next

'Wanted sheet name here
Set ws = Wb.Sheets("XXX")

On Error GoTo 0

If Not ws Is Nothing Then
wslastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
wslastcol = ws.Cells(3, Columns.Count).End(xlToLeft).Column
ws1lastcol = 1


' The following first line works, However the next one doesn't - why?
'ws.Cells.Range("A1", "B2").Copy ws1.Cells.Range("A1", "B2")
ws.Range(Cells(1, 1), Cells(2, 2)).Copy ws1.Range(Cells(1, 1), Cells(2, 2))

ws1.Rows(1).AutoFit
ws1.Columns(1, 3).AutoFit

End If
Wb.Close False
Wbname = Dir
Loop


With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = lngCalc
End With
End Sub

[SamT]

clicking the # icon on the editor tool bar will insert [ Code] tags in the editor's Text pane. You can write or paste your code between the tags, or you can put the code in the text pane, select it, then click the icon. either way will format the code when you post the message. Please use this function. I did it for you this time, but I am a moderator and most people can only edit their own posts.

[SamT]

However, I get the following error:

"Run time error 1004.
Application defined or object defined error"
On what line?

[NewED]

Always here:


ws.Range(Cells(1, 1), Cells(2, 2)).Copy ws1.Range(Cells(1, 1), Cells(2, 2))

[Paul_Hossler]

Try this with the Cells() 's Parent specified




Range(ws.Cells(1, 1), ws.Cells(2, 2)).Copy Range(ws1.Cells(1, 1), ws1.Cells(2, 2))

[NewED]

It worked great, Pual. Thanks!
Why did this work and not the other codes?

Plus, Is there a way to change the Autofit code so it will auto fit all columns from 1 to 3 ?

thanks


'ws1.Rows(1).AutoFit
'ws1.Columns(1).AutoFit

[NewED]

Managed. Thanks a lot!

[snb]

Because there is a difference between:


msgbox range("A1,B2").address
msgbox range(range("A1"),range("B2")).address
msgbox range("A1:B2").address

msgbox range(cells(1,1),cells(2,1)).address
msgbox cells(1).resize(2,2).address

msgbox [A1,B2].address
msgbox range([A1],[B2]).address
msgbox [A1:B2].address

The copy command can only be applied to a contiguous range:


ws.[A1:B2].copy ws1.[A1]
NB. the copy command only needs the first cell of he copy destination.

Most of the time you don't need to bother the clipboard to 'copy' values


ws1.[A1:B2]=ws.[A1:B2].value


range("A1","B1") always errors out: check the basics of Excel.