[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Excel VBA to Copy data from an 2007 to 2003 worksheet

Posted on 2011-04-27
Medium Priority
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
Simon Ball earned 664 total points
ID: 35481456
do you get an error then?

at what point does it go wrong?
LVL 46

Accepted Solution

aikimark earned 668 total points
ID: 35482575
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

ID: 35484973
the 2003 workbook is opened by 2007 but trying to copy columns of data from the 2007 worksheet to the 2003 worksheet summary
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

LVL 46

Expert Comment

ID: 35485083

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

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

Assisted Solution

SiddharthRout earned 668 total points
ID: 35489986
llawrenceg: Could you also please mention which specific line are you getting the error and what is the exact error message.


Author Comment

ID: 35509854
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

ID: 35509859
I beloieve this was some previous work that RORYA did .

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

834 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