Hi all,
Trying to resolve some old VBA I found that xladept helped me with.
I am trying to copy and paste the ranges A2:G34 and paste in sets of 32 rows one after the other in the wb "trackerdatamerge.xlsm". There are 20 wbs:
7aIt1,7aIt2,7aIt3,7aIt4,7aIt5,7bIt1,7bIt2,7bIt3,7bIt4,7bIt5,8aIt1,8aIt2,8aIt3,8aIt4,8aIt5,8bIt1,8bIt2,8bIt3,8bIt4,8bIt5
Directories below are correct. The vba is opening (and closing) 7aIt1 (first wb) and copying/pasting the correct values, but it then doesn't open the next wb.
If anyone could help I'd really appreciate it!
Sub Level(): Dim P, U As String, A As Range, R As Range, i As Long: i = 1
Dim wb As Workbook, sb As Workbook, ws As Worksheet, ss As Worksheet
P = "T:\Common Area\ICT\Tracking\KS3\"
U = Dir(P)
Workbooks.Open Filename:="T:\Common Area\ICT\Tracking\KS3\" & "Trackerdatamerge.xlsm"
Set sb = Workbooks("Trackerdatamerge.xlsm"): Set ss = sb.Worksheets("Sheet1")
SetNextBook:
If U = "Trackerdatamerge.xlsm" Then GoTo GetNextBook
Workbooks.Open Filename:="T:\Common Area\ICT\Tracking\KS3\" & U
Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Project1")
Set A = ws.Range("A2:G33"): Set R = ss.Range("A" & i)
A.Copy
R.PasteSpecial xlPasteValues
i = i + 32
wb.Close SaveChanges:=False
GetNextBook:
If i > 31 Then Exit Sub
On Error GoTo EndSub
U = Dir(): GoTo SetNextBook
EndSub: End Sub
Bookmarks