Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 5th, 2013, 12:34 AM
Registered User
 
Join Date: May 2010
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Consolidate data from different excel files (VBA)

Hi,

I have the excel file which consolidates the data from different workbooks and puts in desired workbook, now I need small customization to that where I need to incorporate sheet name for copying the data because I have multiple sheets available on the workbooks.
As I said, I need to specify the sheet from which the specified ranges will be copied. I tweaked the code accordingly and added the columns in List worksheet which is not working, running the macro then produces the error message. My code is as follows. My offsets are different because I have a total of 9 columns (I specified a specific range into which the data should be pasted. Headings are as follows:

Item No
File Name
Full Path
Data Range Start Cell
Data Range End Cell
Copy to Sheet
Copy To Location(Start Cell Only)
Copy To Location(End Cell Only)
Which Sheet Copy

PHP Code:
Sub GetData() 
Dim strWhereToCopy As StringstrStartCellRange As String 
Dim strListSheet 
As StringstrWhichSheetCopy As String 
strListSheet 
“List” 
On Error 
Goto ErrH 
Sheets
(strListSheet).Select 
Range
(“B2″).Select 
‘this Is the main Loopwe will Open the files one by one And copy their data into the masterdata sheet 
Set currentWB 
ActiveWorkbook 
Do While ActiveCell.Value <> â€œâ€ 
    strFileName 
ActiveCell.Offset(01) & ActiveCell.Value 
    strCopyRange 
ActiveCell.Offset(02) & â€œ:” ActiveCell.Offset(03
    
strWhereToCopy ActiveCell.Offset(04).Value 
    strStartCellRange 
ActiveCell.Offset(05) & â€œ:” ActiveCell.Offset(06
    
strWhichSheetCopy ActiveCell.Offset(07).Value 
    Application
.Workbooks.Open strFileNameUpdateLinks:=FalseReadOnly:=True 
    Set dataWB 
ActiveWorkbook 
    Sheets
(strWhichSheetCopy).Range(strCopyRange).Select 
    Selection
.Copy 
    currentWB
.Activate 
    Sheets
(strWhereToCopy).Select 
    Selection
.PasteSpecial xlPasteValuesxlPasteSpecialOperationNone 
    Application
.CutCopyMode False 
    dataWB
.Close False 
    Sheets
(strListSheet).Select 
    ActiveCell
.Offset(10).Select 
Loop 
Exit Sub 
ErrH

MsgBox â€œIt seems one Or more files were missingThe data copy operation Is Not complete.” 
Exit Sub 
End Sub 
Thanks in advance

Regards,
GVR
 
Old March 6th, 2013, 10:05 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi GVR

The code needs to be revisited. Please remove all ActiveSheet, Activeworkbook, Activate and Selection with the actual

For example Activecell can be replaced with

Set oRange = currentWB.Sheets(1).Range(A2)
etc

This gives you a good command

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips &amp; Tricks (http://www.vbadud.blogspot.com)





Similar Threads
Thread Thread Starter Forum Replies Last Post
2010 excel VBA changing links to external excel files andrewpage500 BOOK: Excel 2007 VBA Programmer's Reference ISBN: 978-0-470-04643-2 0 September 20th, 2012 08:42 PM
Excel vba: data processing snowman.hk Excel VBA 1 November 6th, 2009 05:56 AM
Vba Consolidate Syntax grouth Excel VBA 3 September 29th, 2006 07:37 PM
Converting excel data to Access using excel VBA ShaileshShinde VB Databases Basics 1 April 26th, 2006 07:57 AM
External db files to Excel, via VBA automation ?? cipher_nb Excel VBA 1 December 13th, 2004 04:56 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.