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/transfer-formula-data-from-sheet-to-another-sheet-has-offset-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 ...