VBA- running loop wihin another loop

[flashjas]

Hi guys,

I have a problem. I need to copy some 500 files into one. I think I have to do two loops. First loop is correct I guess, I just open and select these files. I have a problem doing second loop. The problem is that the sheet I want to copy changes name every time, but dont know how to do it. The good news is that these sheets have partially same name. The problem is that I dont know how to combine these two loops together.

Will be grateful for your help!

My code:

Sub zboze()


Dim MyObj As Object, MySource As Object, file As Variant, arkusz As Worksheet
file = Dir("C:\Users\Jaś\Desktop\ROLNICTWO\ROLNICTWO\ZBOŻA\")

While (file <> "")

If (Mid(file, 5, 4) & "-" & Mid(file, 10, 2) & "-" & Mid(file, 13, 2)) > CDate("2005-01-01") Then

Workbooks.Open ("C:\Users\Jaś\Desktop\ROLNICTWO\ROLNICTWO\ZBOŻA\" & file)
Windows(file).Activate
If arkusz.Name Like "*Zmiana Roczna*" Then
arkusz.Activate
Range("C7:C16").Select
Selection.Copy
Windows("zboze_dane").Activate
Range("B1").End(xlDown).Offset(1, 0).PasteSpecial Transpose:=True
Range("A1").End(xlDown).Offset(1, 0).Select
Selection.Value = Mid(file, 5, 10)
ActiveCell.Replace What:=".", Replacement:="-", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


Windows(file).Close False
End If
file = Dir
Wend
End Sub

 

[JackDanIce]

Sorry to hear that.