Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data range in Array worksheets
Hi,
After several attempts to work around but fails to run the complete codes Thus, I need help to join the 2nd part of vba codes below so that I can make the changes in each sheet("P+L") of every workbook in J folder, thereafter make the defined data range in each sheet of every workbook in that J folder for data consolidation purpose : - Sub Totals() Application.DisplayAlerts = False Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Const MAXBOOK As Long = 4 Dim i%, SheetArg$() Dim sPath1 As String ReDim SheetArg(1 To MAXBOOK) Dim x As String Dim Namerng As Variant, NameList As Variant Dim sPath As String, sFile As String Windows("Budget Consol.xls").Activate sPath = "J:\BBT\LO\Budget\Budget Actual\Acad2\" i = 0 sPath1 = "J:\BBT\LO\Budget\Budget Actual\Acad2\*.xls" sFile = Dir(sPath1) ---------2nd part of join codes --------------- Dim i As Long Dim Lstrow As Long Lstrow = Cells(Rows.Count, "A").End(xlUp).Row If Lstrow 0 Then For i = 5 To Lstrow If Cells(i, 1).Value < "" Then Cells(i, 1).Copy Cells(i, 2).Select ActiveSheet.Paste Application.CutCopyMode = False 'Cells(i, 1).ClearContents End If Next Else MsgBox "It appears that the file is empty, check the file again" Exit Sub End If ----------- End 2nd part ----------------------- Do While sFile < "" i = i + 1 SheetArg(i) = "'" & sPath & _ [ & sFile & "]P+L'!R6C2:R47C15 " sFile = Dir() Loop ThisWorkbook.Sheets("Sheet2").Range("A1").Consolid ate _ Sources:=Array(SheetArg), Function:=xlSum, TopRow:=True, _ LeftColumn:=True, CreateLinks:=True Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Your help will be much appreciated as I'm vba beginner and thanks in advance Regards Len |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Efficiently moving data between worksheets and VBA array variables | Excel Programming | |||
Moving data from an array to a range when range consists of areas? | Excel Programming | |||
Redimming an array dynamically assigned from range (how to redim first dimension of a 2-D array? /or/ reverse the original array order) | Excel Programming | |||
Q: Best way to take data from VBA into graphs without writing data to worksheets? (Can a named range refer to an array in memory only?) | Excel Programming | |||
Read Range Data into Array | Excel Programming |