NewED
08-28-2015, 11:12 AM
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
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