Question:

Run the below code in loop 35 times and... also replace the file1 with increament value and also increment Rannge("D6") as range("D7") and so on .
Use for loop and iterate 35 times increamenting file1 and d6 cell
 

Windows(File1).Activate

Range("A1").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, -2).Range("A1:B1").Select
ActiveCell.Columns("A:B").EntireColumn.Select
ActiveCell.Offset(27961, 0).Range("A1").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "Result"
ActiveSheet.Paste
Range("C1").Value = "Year"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-2],4)"
Selection.AutoFill Destination:=Range("C2:C" & Range("B1048576").End(xlUp).Row)


Dim year As Integer
year = InputBox("Fill in year")
Dim week As Integer
week = InputBox("Fill in week")

Cells(1, 4) = "YEAR"
Range("C2:C" & Range("C1048576").End(xlUp).Row).Select
Selection.Copy
Range("D2").Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

Cells(1, 5) = "Values"
Cells(2, 5).Select
ActiveCell.FormulaR1C1 = "=IF(AND(RC[-1]=" & year & ",RC[-3]=" & week & "),1,0)"
Selection.AutoFill Destination:=Range("E2:E" & Range("D1048576").End(xlUp).Row)

  Range("D:D").Select

With Selection
Selection.NumberFormat = "0"
.Value = .Value
 End With

Range("F2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R[98576]C[-1])"
ActiveCell.Select

Cells(2, 6).Select
Selection.Copy
Windows("Penetrace Macros.xlsm").Activate
Sheets("Book1withCount").Activate
Range("D6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveCell.Offset(1, 0).Select
Windows("Penetrace Macros.xlsm").Activate
Application.DisplayAlerts = False
Windows(File1).Activate
Sheets("Result").Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Workbooks(File1).Close SaveChanges:=False
Windows("Penetrace Macros.xlsm").Activate
Range("A1").Select


 Answers:
 


Can you please try this? Idea would be something like this.
Dim FileNo As String

For i = 1 To 35
FileNo = "File" & i
Windows(FileNo).Activate

'Rest of the code
Next

Commentaires

Enregistrer un commentaire

Posts les plus consultés de ce blog

XAJAX with PHP – The future of web development

XAJAX is a class library or framework written purely on PHP that allows you to easily create powerful, Web-based, Ajax applications or plugins. You can download xajax framework from http://sourceforge.net/projects/xajax/ XAJAX is easy to impement. You need to just include XAJAX library, instantiate the xajax object, and register the name of function (or an object or class method) which you want to call from XAJAX framework. xajaxResponse() is an object to return the results of our requests as XML formats. Then call that particular function you want from xajax framework through your HTML or javascript. That's it. XAJAX takes care of most everything else. You just need to write the PHP functions and returning XAJAX XML responses from them, which is made extremely easy by the xajaxResponse class. Code: <?php include './xajax/xajax_core/xajax.inc.php'; $xajax = new xajax(); $rqstAdd =& $xajax->register(XAJAX_FUNCTION, 'ad...
Lire la suite

XAJAX with PHP – The future of web development

XAJAX is a class library or framework written purely on PHP that allows you to easily create powerful, Web-based, Ajax applications or plugins. You can download xajax framework from http://sourceforge.net/projects/xajax/ XAJAX is easy to impement. You need to just include XAJAX library, instantiate the xajax object, and register the name of function (or an object or class method) which you want to call from XAJAX framework. xajaxResponse() is an object to return the results of our requests as XML formats. Then call that particular function you want from xajax framework through your HTML or javascript. That's it. XAJAX takes care of most everything else. You just need to write the PHP functions and returning XAJAX XML responses from them, which is made extremely easy by the xajaxResponse class. Code: <?php include './xajax/xajax_core/xajax.inc.php'; $xajax = new xajax(); $rqstAdd =& $xajax->register(XAJAX_FUNCTION, 'a...
Lire la suite

Database connection pooling in ADO.Net

Imagine a situation that multiple users are connecting the same database through our application. In an application establishing a database connection and logging into it is a time consuming activity. It needs several seconds for every time it establishes a connection. So opening and maintaining a database connection for each user is not an ideal solution. When the number of users increases, and if the system�s memory set is limit, probably it leads to a system crash. This problem can solve through connection pooling. A connection pool is a cache or pool of database connections maintained by the application so that the connections can be reused when the database receives future requests for data. Establishing once and reserving it for future request can essentially improve the overall performance of the application. In connection pooling, after a connection is created, the connection object is placed in the pool. When a simultaneous connection request is made later, ...
Lire la suite