Excel VBA to Copy data from an 2007 to 2003 worksheet

I have five  2007 users  with  data on  excel 2007 worksheets  that  needs  to  be copied  to an  Excel 2003 summary workbook with a separate tab for each user.
There are 25 excel 2003 users that copy their data to the same summary workbook into separate tabs under their names and the code works perfectly for 2003.

 What I have done  for 2003 doesnt work for 2007  yet. Can You help

Set work_book = Workbooks.Open(Sheets(2).Range("J2").Value)'2003 workbook
  work_book.Sheets(rng).Name 'error 9 if sheet does not exist
If Err.Number = 9 Then   'create the sheet
  Set last_sheet = work_book.Sheets(work_book.Sheets.Count)'2003 sheets
  Set WshTarget = work_book.Sheets.Add(After:=last_sheet)2003 sheets
  WshTarget.Name = rng
Else                     'use existing sheet
  Set WshTarget = work_book.Sheets(rng)'2003 Worksheet
End If

Set WshSource = ThisWorkbook.Worksheets(1)'2007 Worksheet
With WshSource
For lngcol = 1 To 8
If n < 15 Then n = 15
  n = WshSource.Cells(WshSource.Rows.Count, lngcol).End(xlUp).Row
  WshSource.Range(.Cells(16, lngcol), .Cells(n, lngcol)).Copy
  WshTarget.Cells(3, lngcol).PasteSpecial xlValues
  Application.CutCopyMode = False
Next lngcol

End With

Open in new window

Who is Participating?
There are a few ways to do this:

* use 2007 to open the 2003 workbook.

* use 2007 to consolidate the data and save it in 2003 format, overwriting the old 2003 workbook

* If using 2003 to 'pull' the data (or VBScript), use Excel automation to read the 2007 worksheet data.  You will need to include the version number with the CreateObject() function.
Simon BallCommented:
do you get an error then?

at what point does it go wrong?
llawrencegAuthor Commented:
the 2003 workbook is opened by 2007 but trying to copy columns of data from the 2007 worksheet to the 2003 worksheet summary
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).


>>...but trying to copy...

It appears that your sentence was truncated for some reason.  Please repost.
llawrenceg: Could you also please mention which specific line are you getting the error and what is the exact error message.

llawrencegAuthor Commented:
I believe I figured it out .It was not the Copy Method but the Save As method

Application.EnableEvents = False    
macro     'Excel 2007 and later requires FileFormat parameter in SaveAs method. 52 is .xlsm, 56 is .xls
If LCase(flPath) Like "*.xlsm" Then
    iFileFormat = 52
    iFileFormat = IIf(Application.Version < 12, xlWorkbookNormal, 56)   '.xls file format for Excel 2003
End If
If SaveAsUI Then ThisWorkbook.SaveAs flPath, FileFormat:=iFileFormat    'Save the file using the filename and path chosen by the user
If Not SaveAsUI Then ThisWorkbook.Save  
 Application.EnableEvents = True      
llawrencegAuthor Commented:
I beloieve this was some previous work that RORYA did .
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.