Copying a Range in two different WorkSheets

[zubin]

Hi Guys....The code below copies three different ranges to a new location in the same worksheet......

Sheet1.Range("B4:B14").Copy
    Sheet1.Range("B" & Sheet1.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 2).PasteSpecial xlValues
    Selection.Value = Selection.Value
    
    Sheet1.Range("E4:E14").Copy
    Sheet1.Range("D" & Sheet1.Columns("D").Find("*", , xlValues, , xlRows, xlPrevious).Row + 2).PasteSpecial xlValues
    Selection.Value = Selection.Value
    
    Sheet1.Range("F4:F14").Copy
    Sheet1.Range("E" & Sheet1.Columns("E").Find("*", , xlValues, , xlRows, xlPrevious).Row + 2).PasteSpecial xlValues
    Selection.Value = Selection.Value

Everything fine.......but now my requirement has changed and I need that apart from the above ranges being copied simultaneously the below ranges should be copied in sheet4 of the same workbook

 Sheet1.Range("C4:C14").Copy
    Sheet4.Range("A" & Sheet4.Columns("A").Find("*", , xlValues, , xlRows, xlPrevious).Row + 2).PasteSpecial xlValues
    Selection.Value = Selection.Value
    
    Sheet1.Range("D4:D14").Copy
    Sheet4.Range("B" & Sheet4.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 2).PasteSpecial xlValues
    Selection.Value = Selection.Value
    
    Sheet1.Range("E4:E14").Copy
    Sheet4.Range("C" & Sheet4.Columns("C").Find("*", , xlValues, , xlRows, xlPrevious).Row + 2).PasteSpecial xlValues
    Selection.Value = Selection.Value
    
    Sheet1.Range("F4:F14").Copy
    Sheet4.Range("D" & Sheet4.Columns("D").Find("*", , xlValues, , xlRows, xlPrevious).Row + 2).PasteSpecial xlValues
    Selection.Value = Selection.Value

How do I achieve this.....

 

[zubin]

Thi

Hi,​

​

if only the values must be 'copied' so the better is to use DestinationRange.Value2 = SourceRange.Value2 …​

​

If the source data contains cells formated as Date so use Value rather than Value2.​

​

Almost all cells have formulas and most important this range has to be copied two rows below the last occupied row and these ranges keep adding below the last range..