Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-10-2015, 12:18 AM
Sandhya Sandhya is offline Consolidate data from different excel sheets Windows 7 32bit Consolidate data from different excel sheets Office 2007
Novice
Consolidate data from different excel sheets
 
Join Date: Nov 2014
Posts: 21
Sandhya is on a distinguished road
Arrow Consolidate data from different excel sheets

Hi Friends,

I have below mentioned code, it will open different excel files and paste into new excel, but the pasted location ie offset location of paste to file should start as required i.e. different files to different locations, can any one modify this code.

My Paste o sheet will Sheet1 location Start from G285 and so on...

Public strFileName As String
Public currentWB As Workbook
Public dataWB As Workbook
Public strCopyRange As String

Sub GetData()
Dim strWhereToCopy As String, strStartCellColName As String
Dim strListSheet As String

strListSheet = "List"

On Error GoTo ErrH
Sheets(strListSheet).Select
Range("B2").Select

'this is the main loop, we 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(0, 1) & ActiveCell.Value
strCopyRange = ActiveCell.Offset(0, 2) & ":" & ActiveCell.Offset(0, 3)
strWhereToCopy = ActiveCell.Offset(0, 4).Value
strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)

Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True
Set dataWB = ActiveWorkbook

Range(strCopyRange).Select
Selection.Copy

currentWB.Activate
Sheets(strWhereToCopy).Select
lastRow = LastRowInOneColumn(strStartCellColName)
Cells(lastRow + 1, 1).Select

Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
Application.CutCopyMode = False
dataWB.Close False
Sheets(strListSheet).Select
ActiveCell.Offset(1, 0).Select
Loop
Exit Sub

ErrH:
MsgBox "It seems some file was missing. The data copy operation is not complete."
Exit Sub
End Sub

Public Function LastRowInOneColumn(col)
'Find the last used row in a Column: column A in this example
'http://www.rondebruin.nl/last.htm
Dim lastRow As Long
With ActiveSheet
lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
End With
LastRowInOneColumn = lastRow
End Function
Reply With Quote
  #2  
Old 10-10-2015, 12:28 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Consolidate data from different excel sheets Windows 7 64bit Consolidate data from different excel sheets Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,655
Pecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud of
Default

Please wrap code with code tags - Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Consolidate data from different excel sheets Excel, transfer data from Master Sheet to sub sheets, using key word from column anvqui Excel Programming 9 06-16-2015 01:35 PM
How to replicate, consolidate and count cell data in excel for data consoldation. Loran Excel 7 06-05-2014 01:07 AM
Consolidate data from different excel sheets [EXCEL 2010] Merging data from three different sheets bremen22 Excel 1 08-20-2013 10:00 AM
Consolidate data from different excel sheets How to syncronize data in two different sheets diegogeid Excel 2 09-30-2010 12:19 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:03 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2023, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2023 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft