Copy data from one sheet to another sheet

[sathishsusa]

Hi,

i have a sheet 1 and sheet 2 have a data with formula column from A to I its starts from row# 5 to 108. Now all the data from sheet 1 and sheet 2 need to copy and paste ( join with workbook) to sheet 3 by clicking button Get data in sheet 3.

The link of cross post :-

http://chandoo.org/forum/threads/tra...pasting.33575/

Marc L has almost done working with code but issue is not copying with next empty row.

Eg:- sheet 1 A5 to Ai from rows 5 to 108 the data from A5 to A9
sheet 2 A5 to Ai from rows 5 to 108 the data from A5 to A7
but sheet 3 copy whole rows from 5 to 108 in sheet 1 and having empty spaces and copy sheet 2 rows from 5 to 108.

i don't want the empty rows in sheet 3 between both sheets i need the data contentiously copy to next empty row.

if possible experts can u check with code what wrong on it or can change the code with better solution

Sub Button13_Click()
    Sheet2.UsedRange.Offset(4).Clear
    Sheet1.[A5:I5].Resize(Sheet1.Cells(Rows.Count, 1).End(xlUp).Row - 4).Copy Sheet2.[A5]
    Sheet4.[A5:I5].Resize(Sheet4.Cells(Rows.Count, 1).End(xlUp).Row - 4).Copy Sheet2.Cells(Rows.Count, 1).End(xlUp)(2)
End Sub

[Admin]

Welcome to ExcelFox!

try this

    Dim rngLCell    As Range
    
    Sheet2.UsedRange.Offset(4).Clear
        
    Set rngLCell = Sheet1.Cells.Find(What:="*", After:=Sheet1.Range("c1"), LookIn:=xlValues, LookAt:= _
                            xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
    If rngLCell.Row > 5 Then
        Sheet1.Range("a5:i" & rngLCell.Row).Copy Sheet2.Range("a5")
    End If
    
    Set rngLCell = Nothing
    Set rngLCell = Sheet4.Cells.Find(What:="*", After:=Sheet4.Range("c1"), LookIn:=xlValues, LookAt:= _
                            xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
    If rngLCell.Row > 5 Then
        Sheet4.Range("a5:i" & rngLCell.Row).Copy Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp)(2)
    End If

[sathishsusa]

hi admin,

your quick response i really heartfelt appreciate.
Excellent work perfect , thank a lot , thank u so much ...