Updating one sheet data to another sheet

[nareshjoshy]

Dear Sir,

How can I Update My sheet1 data to Sheet2 without overlaping my existing data of Sheet2 with same font and font size. I also want to clear all data of sheet1 after updaing to sheet2 Using VBA code.

Thank You in Advance.

 

[vcoolio]

Hello Naresh,

If you are copying your whole Sheet1 data set (except the headings) to Sheet2, then try the following:-

Sub Test()

With Sheet1.UsedRange.Offset(1)
        .Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
        .Clear
End With

End Sub

Test it in a copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.

 

[nareshjoshy]

Thank you vcoolio. It is working and it is very helpful for me.

 

[nareshjoshy]

Dear Vcoolio, I want to change little bit in code.

[COLOR=#333333]With Sheet1.UsedRange.Offset(1)[/COLOR]

I want to change Sheet1 as Active. I mean to say, Data should be copyed from Active sheet from where macro run.

 

[nareshjoshy]

Dear Vcoolio, I want to change little bit in code.

With Sheet1.UsedRange.Offset(1)

I want to change Sheet1 as Active. I mean to say, Data should be copyed from Active sheet from where macro run.

 

[vcoolio]

Hello Naresh,

Just change Sheet1 to ActiveSheet.

Cheerio,
vcoolio.

 

[nareshjoshy]

Dear Vcoolio, Your code is working well. This code copy entire record and clear as well except Offset range. But I need to copy only column C:K and M:P and row 2:22 and clear column C:K and M:O and row 2:22 of active sheet. I don't neet to column L be blank. I need to copy format and value only. I need copy format because my different sheet contain different font and column format as date, value, text. and some column contain function which I don't want to copy but value only.

Another thing is that, when I inserted some column and row after Inserting module Offset range don't work properly.

 

[vcoolio]

Hello Naresh,

Your requests continue to grow and this thread has gone beyond what you had asked for help with in your opening post.
You need to make your requests for help crystal clear from the opening post. We cannot guess as to what you intend to achieve without a full explanation from the outset.

For further assistance with this thread, please upload a sample of your workbook to a file sharing site such as Drop Box, Box.com or GE.TT and then post the link to your file back here. Explain carefully and fully your inputs and expected results. Manually create your outputs in the destination sheet to help create a clear idea of what you would like to achieve. If your data is sensitive, then please use dummy data.

Thank you Naresh.

Cheerio,
vcoolio.

 

[vcoolio]

In the meantime Naresh, whilst you prepare the sample workbook, you may as well try the following code:-

Sub Test()

Application.ScreenUpdating = False

With ActiveSheet
         .[C2:K22, M2:P22].Copy
         Sheet2.Range("A" & .Rows.Count).End(3)(2).PasteSpecial xlFormats
         Sheet2.Range("A" & .Rows.Count).End(3)(2).PasteSpecial xlValues
         .[C2:K22, M2:P22].Clear
End With

Application.ScreenUpdating = True

End Sub

Cheerio,
vcoolio.