Excel VBA to Copy data from an 2007 to 2003 worksheet

Posted on 2011-04-27
Last Modified: 2012-05-11
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

Question by:llawrenceg
    LVL 15

    Assisted Solution

    by:Simon Ball
    do you get an error then?

    at what point does it go wrong?
    LVL 44

    Accepted Solution

    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.

    Author Comment

    the 2003 workbook is opened by 2007 but trying to copy columns of data from the 2007 worksheet to the 2003 worksheet summary
    LVL 44

    Expert Comment


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

    It appears that your sentence was truncated for some reason.  Please repost.
    LVL 30

    Assisted Solution

    llawrenceg: Could you also please mention which specific line are you getting the error and what is the exact error message.


    Author Comment

    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      

    Author Comment

    I beloieve this was some previous work that RORYA did .

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now