Copy & Paste VBA

[dannyok90]

Hi All,

I'm trying to manipulate the following script.

When I paste new data over a range on sheet 1 I would like the old data range copied and pasted into sheet 2.. id like this as a cycle.. every time I paste new data over the data in sheet 1 the old data overwrites the older data in sheet2 - so basically I have the current and previous weeks data In the workbook at all times

any guidance would be great!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("A:A"), Target)
xOffsetColumn = 3
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            'code here maybe?
        Else
            'code here maybe?
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

 

[igold]

Hi,

A couple of thoughts... I can't see your data nor do I know how the new paste into Sheet1 is generated or where on Sheet2 the old data resides. All that said I would copy the old data from Sheet1 first by either putting it into a Range or an Array, and then once the old data is stored, I would either paste the new data and then take the stored data and put it in Sheet2 or reverse the order. Paste the old data into Sheet2 and then paste the new data in Sheet1.

If you provide a little more information this should be a really straight forward process.

 

[Jaafar Tribak]

Hi All,

I'm trying to manipulate the following script.

When I paste new data over a range on sheet 1 I would like the old data range copied and pasted into sheet 2.. id like this as a cycle.. every time I paste new data over the data in sheet 1 the old data overwrites the older data in sheet2 - so basically I have the current and previous weeks data In the workbook at all times

any guidance would be great!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("A:A"), Target)
xOffsetColumn = 3
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            'code here maybe?
        Else
            'code here maybe?
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

Are you copying data from outside excel ?

 

[Logit]

.
Here is an example of copying data from Sheet 3 to Sheet 1. It first Moves the old data from Sheet 1 to Sheet 2.

If you are copying data from another location, the code will need to be edited.

Option Explicit


Sub Cleanup()
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Dim rCell As Range
    
    'setup
    Application.ScreenUpdating = False
    Set ws1 = ActiveSheet
    Set ws2 = Sheets("Sheet2")
    Set ws3 = Sheets("Sheet3")
    
    'copy data from 1 to 2
    ws2.UsedRange.ClearContents
    ws1.UsedRange.Copy
    ws2.Cells(1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    ws1.UsedRange.ClearContents
        
    'cleanup
    Application.ScreenUpdating = True
    ws2.Select
    ws2.Cells(1, 1).CurrentRegion.EntireColumn.AutoFit
    ws2.Range("A1").Select


    
    'copy from Sht3 to Sht1
    ws3.UsedRange.Copy
    ws3.Activate
    ws3.Range("A1").Select
    ws1.Cells(1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    ws1.Activate
    ws1.Range("A1").Select
    ws1.Cells(1, 1).CurrentRegion.EntireColumn.AutoFit
    Application.CutCopyMode = False


End Sub