Looping through excel

I've a piece of code which copys data from am number of spreadsheets in a folder  to a results workbook.

However the data is currentlywriting  to the same cell A7 . how do put in a loop which adds data every sixth row so A& i+6?

The code is

Dim i As Integer
    Range("B2:H6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Application.Workbooks.Open "U:\transfer\a\e\f\Results.xls"
    'Open "U:\transfer\a\e\Results.xls"
    'Windows("Book2").Activate
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   
       
    Cells("A7").Select


Workbooks("Results.xls").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close



End Sub

Public Sub ProcessWorkbooks()

    Dim strDir As String
    strDir = "U:\transfer\a\e"
   
   
    Dim CurFile As String
    Dim wb As Workbook
   
    CurFile = Dir(strDir + "\*.xls")
   
    Do While CurFile <> ""
       
        Set wb = Workbooks.Open(strDir + "\" + CurFile)
       
        Macro2 wb
       
        wb.Close True
       
        CurFile = Dir()
       
    Loop

End Sub
LVL 4
davystocksAsked:
Who is Participating?
 
davystocksAuthor Commented:
Hi,

it's ok , I was able work it out.

Regards,

Davystocks

Option Explicit
 Public i As Integer

Sub Macro2(wb As Workbook)
'
Dim str As String
'Dim EndRow As Integer, counter As Integer

    Range("B2:H6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Application.Workbooks.Open "U:\transfer\a\e\f\Results1.xls"
    'ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    '  If i = 0 Then
  '      i = 1
 '  Else
    i = i + 5
  ' End If
   str = "A" + CStr(i)
   
    Range(str).Select

Workbooks("Results.xls").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub

Public Sub ProcessWorkbooks()

    Dim strDir As String
    strDir = "U:\transfer\a\d"
   
   
    Dim CurFile As String
    Dim wb As Workbook
   
    CurFile = Dir(strDir + "\*.xls")
   
    i = 5
   
    Do While CurFile <> ""
       
        Set wb = Workbooks.Open(strDir + "\" + CurFile)
       
        Macro2 wb
       
        wb.Close True
       
        CurFile = Dir()
       
    Loop

End Sub
0
 
wilcoxonCommented:
Is a perl solution acceptable or do you want to stick with VBA?  I know it can be done in VBA but I'm rusty enough I'd have to do a lot of digging.
0
 
davystocksAuthor Commented:

Can we stick with VBA?

This should really be in the VBA zone.

Thanks for  your help.
0
 
farzanjCommented:
If you want to do a structured program in VBA, it is easy.  I don't like using ranges for this purpose, for many reasons.

If you can give me a simple example, that would help me understand, I would write it for you.

Do you want from B2 to H6?

Which cells do you want to add?
0
 
DhaestCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.