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 May 29th, 2011, 02:15 PM
Authorized User
 
Join Date: May 2011
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
Default To copy data from three sheets into one sheet.

Hi i have three sheets -

What i want is to copy data from three sheets into one sheet.
for ex -
All three sheets will be having columns like -

Name Hours
Rahul 24
Nitin 20.

This type of data what i want is To Copy The data from three sheets into one But name and hours heading should not be there.
So excluding the first Row from all three sheets.
 
Old June 3rd, 2011, 11:06 PM
Registered User
 
Join Date: Jun 2011
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

I am not that experienced in VBA, so there probably is a quicker method.

Try this. The second sub is to find a block of data if it does not start in the a1 cell.

Irvy

Code:
Sub integrate()
    'Add a new sheet
    Set newSht = Sheets.Add
    newSht.Name = "All"
    Set newRg = [a1]
    
    'Go through all sheets and skip the new sheet named
    For Each sht In Worksheets
        If sht.Name = "All" Then GoTo skip
        sht.Activate
        If sht.Cells(1, 1) = "" Then
            findFirstCell(sht.Cells).Select
            Set rg = ActiveCell.CurrentRegion
            Set newRg = newSht.[a1].CurrentRegion
            Range(rg(2, 1), rg(rg.Rows.Count, rg.Columns.Count)).Copy newRg(newRg.Rows.Count + 1, 1)
        End If
skip:
    Next
End Sub

Function findFirstCell(InRange As Range) As Range
'This finds the start of the first block of data on the sheet.
    Dim C As Long, R As Long
    R = InRange.Rows.Count
    C = InRange.Columns.Count
    
    For i = 1 To InRange.Columns.Count
        Set findFirstCell = InRange.Cells(1, i).End(xlDown)
        If findFirstCell.Row < R Then
            Exit Function
        Else
            Set findFirstCell = InRange.Cells(i, 1).End(xlToRight)
            If findFirstCell.Column < C Then
                Exit Function
            End If
        End If
      
    Next
    Set findFirstCell = InRange.Cells(1, 1)
End Function
 
Old June 7th, 2011, 06:16 AM
Registered User
 
Join Date: Jun 2011
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Great Irvy!

I have also checked, this is working fine, no issues.


regards,
BinoySingha.
 
Old June 7th, 2011, 07:04 AM
Registered User
 
Join Date: Jun 2011
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Cool

Glad to be of assistance.

Irvy





Similar Threads
Thread Thread Starter Forum Replies Last Post
Open Workbook,Copy Sheet,Move Sheet, Close/Save ptrussell2009 Excel VBA 0 June 13th, 2008 02:28 PM
Copy specific data from one sheet to another yogeshyl Excel VBA 2 May 11th, 2007 09:14 AM
copy sheets jgrant Beginning VB 6 0 March 26th, 2007 01:42 PM
Copy one sheet to another file shamigc Excel VBA 2 April 13th, 2005 06:45 AM
Marcro to copy data from one sheet to another Crupa Excel VBA 1 November 24th, 2004 04:12 AM





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