Transfer Data From Workbook To Workbook

Hi

See if this gets you started. It doesn't close down the output workbook at this stage as I want you to make sure it copies things across properly.

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) & ",2:2,0)")
      outsh.Cells(findit.Row, outcol).Value = Cells(ce.Row, j).Value
    Next j
  Next ce
  
End Sub

rylo

Hi Rylo

This is looking like what i need.

At the moment it only copies the data into the first week of the Master rota If i change the dates of the rota sheet it doesn't match the dates and copy underneath.

In the rota sheet i will be using under cell F50 will have hundreds of rows of formula, so using endup will this cause problems

Dave

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

Hi Rylo
i'm having some problems with the code you did for me.

When i run the code at work on windows97 i'm getting and error and the Dates in the master rota that it matches are deleted. it does however copy all the times to the correct cells.

you also said that if the dates in the master rota were numeric, then it wouldn't be too hard to enhance the code to do the conversion before processing.

The format of the date cells in master are set as general.

Can you still help please

Dave

Dave

In your macro, once you have opened rota.xls and moved to the sheet rota then insert this code

Range("H2", Cells(2, Columns.Count).End(xlToLeft)).NumberFormat = "general"
  For Each ce In Range("H2", Cells(2, Columns.Count).End(xlToLeft))
    ce.Value = ce.Value * 1
  Next ce

It will format the cells to general, and convert to numeric.

rylo

I found this about using Evaluate in windows97

http://support.microsoft.com/kb/168238

Hi

Try converting the formula to be valid in the spreadsheet, then put it into a cell somewhere that is not going to impact things.

You can then get the result of the formula, and put it to the variable outcol. I'm guessing that is where the problem lies. Once you have the result in the variable, you can delete the formula from the cell.

See how that goes. If it works, then it should be valid for both versions.


rylo