Transfer Data From Workbook To Workbook

Dave

Silly me - forgot to find things in the right sheet.

try this

Sub ExportIt()
  Workbooks.Open (ThisWorkbook.Path & "\master rota.xls")
  Set outsh = Sheets(ThisWorkbook.Sheets("rota").Range("A4").Value)
  
  ThisWorkbook.Activate
  For Each ce In Range("F6:F" & Cells(Rows.Count, "F").End(xlUp).Row)
    Set findit = outsh.Range("F:F").Find(what:=ce.Value)
    For j = 7 To Cells(2, Columns.Count).End(xlToLeft).Column
      outcol = Evaluate("=match(" & Format(CDate(Cells(2, j)), 0) & ",'[master rota.xls]" & outsh.Name & "'!2:2,0)")
      outsh.Cells(findit.Row, outcol).Value = Cells(ce.Row, j).Value
    Next j
  Next ce
  
End Sub

rylo

Hi

This is looking great will need to run some more test but so far i'm having no problems.

Dave

Thanks for your help so Far

Because i have Data under Row F50 on the rota sheet(not the example sheet i posted) i keep getting an error its todo with the End up statement but i'm not sure how to set the range.

Dave

Dave

I see what you mean now.

Change the for each row to

For Each ce In Range("F6", Range("F6").End(xlDown))

It does assume that there will be a break between the last of the staff ids and any other data...


rylo

Hi

This is now working so Far

Thanks for your help

Dave

Hi again

There will be some blank Rows in between some of the ID Numbers in the rota sheet so i'm not sure that end up or end down will be ok.

Dave

Dave

So could you use the fixed range F6:F49 and only action non blank cells????


rylo