Transfer formula data from sheet to another sheet has offset pasting

[sathishsusa]

Hi,

i have a sheet 1 and sheet 2 have a data column from A to I its starts from row# 5 to 108. Now all the data from sheet 1 and sheet 2 need to transfer to sheet 3 by clicking button transfer has offset line from both sheets 1 & 2.

 

[sathishsusa]

Hi,

i have a sheet 1 and sheet 2 have a data column from A to I its starts from row# 5 to 108. Now all the data from sheet 1 and sheet 2 need to transfer to sheet 3 by clicking button transfer has offset line from both sheets 1 & 2.

i tried with some code it copy from sheet 1 to sheet 3 but i need to copy if sheet 2 data too copy in sheet 3 not to overwrite it should go on next empty rows can u suggest to change the code pls...

Sub trans1234()

Sheets("sheet1").Range("A5:I5").Copy Sheets("Sheet3").Range("A5").Offset(0, 0)
Sheets("sheet1").Range("A6:I6").Copy Sheets("Sheet3").Range("A6").Offset(0, 0)

End Sub

sorry for cross post link :

https://www.excelforum.com/excel-pr...e-to-other-sheet-has-offset-vba-function.html

 

[Marc L]

Hi !

Does it need to clear Sheet #3 before to paste Sheet #1 & 2 ?

Is Sheet #3 in your attachment the expected result ?
If not, join a workbook with Sheet #3 as expected result …
​

 

[sathishsusa]

hi marc ,

thanks for your reply and response..
no i need to keep old data and again i click transfer button it should not repeat the old data in sheet 3.

the expected result is to join a workbook.

 

[Marc L]

How do you differ all data from new one ?

If the result is Sheet1 + Sheet2 in Sheet3,
it's easier to clear Sheet3 then paste Sheet1 & Sheet2, no ?
​

 

[sathishsusa]

ok marc your idea is very good i feel too its better option first clear and paste all the data from both sheet

 

[Marc L]

Try this !​

Private Sub Button3_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

Do you like it ? So thanks to click on bottom right Like !
​

 

[sathishsusa]

hi marc,

your code working but there is problem with if both sheet from sheet 1 A5 and sheet2 A5 data both not copying any one only sheet only copy the data to sheet 3 can u check out pls.

 

[Marc L]

Unclear as my code copies both worksheets data !

So as I wrote …

join a workbook with Sheet #3 as expected result

… maybe someone else may understand your need.
​

 

[sathishsusa]

Marc,
In both sheet 1and sheet 2 having data in Row A5 to A7
Eg: sheet 1 row A5 to A7 having data
Sheet 2 row A5 to A8 having data
In Sheet 3 i am getting copied only sheet 1 from A5 to A7 and from sheet 2 copying only row from A8.
I didn't get copy data in sheet 2 from A5 to A7
Is it clear Marc pls check.

 

[Marc L]

As my test computer is under an older Excel version,
I saw nothing in your sheets but formula errors only !
So data is from row #5 to row #108
as you had yourself explained in initial post ‼

As I can not grab another computer maybe until next week,
wait for someone else or at least join a crystal clear workbook
with only values instead of formulas and, for the third time,
a Sheet3 expected result !
​

 

[sathishsusa]

Has any one pls try to solve the remaining issues of the thread ?

 

[sathishsusa]

As my test computer is under an older Excel version,
I saw nothing in your sheets but formula errors only !
So data is from row #5 to row #108
as you had yourself explained in initial post ‼

As I can not grab another computer maybe until next week,
wait for someone else or at least join a crystal clear workbook
with only values instead of formulas and, for the third time,
a Sheet3 expected result !
​

Dear Marc L,

Thanks for your effort and help on my post.

Finally thread has been solved by Mr. Admin of Excelfox.. i sincere thanks to both of you.

http://www.excelfox.com/forum/showthread.php/2158-Copy-data-from-one-sheet-to-another-sheet