Macro to overwrite existing month data - help

bluepenink

Well-known Member
Joined
Dec 21, 2010
Messages
585
Hello

My macro below dates the data from the file it is called from i.e. August Raw data...and dumps it below the last existing line on GS Report sheet.

usually in a month i will get copies of august data 3 times, so i simply paste it into the august raw data file, but when i run my macro, it DOES NOT overwrite the existing august data, just dumps it below, creating duplicates.

can someone pls help me with this; i need for the macro to overwrite the respective month's data regardless of how many times i hit the button "run macro"

pls advise

Code:
Option Explicit
Option Base 1
Sub copyMonthlySheets()
    
    Dim aColCopy, AnameShsrc
    Dim shDes As Worksheet, shSrc As Worksheet
    Dim i As Long, j As Long, eR As Long, iRdes As Long, k As Integer
    
    OnEven False
    Call Unprotect_All_Sheets
    
    With ThisWorkbook.Sheets("RUN Pr")
        Set shDes = ThisWorkbook.Sheets(.Range("DesSheet").Offset(1).Value)
        AnameShsrc = .Range("SrcSheets").Offset(1).Resize( _
                            .Cells(Rows.Count, .Range("SrcSheets").Column).End(xlUp).Row _
                            - .Range("SrcSheets").Row + 1).Value
        
        aColCopy = .Range("DesCol").Offset(1).Resize(.Cells(Rows.Count, _
                            .Range("DesCol").Column).End(xlUp).Row - .Range("DesCol").Row + 1, 2).Value
    End With
    
    For i = 1 To UBound(AnameShsrc, 1) - 1
        iRdes = shDes.Cells(Rows.Count, aColCopy(1, 1)).End(xlUp).Row + 1
        Set shSrc = Sheets(AnameShsrc(i, 1))
        eR = shSrc.Cells(Rows.Count, 1).End(xlUp).Row
        
        For j = 1 To UBound(aColCopy, 1) - 1
            With shDes.Range(aColCopy(j, 1) & iRdes).Resize(eR - 1)
              Select Case aColCopy(j, 2)
                Case "Month"
                    .Value = Left(shSrc.Name, InStr(1, shSrc.Name, Space(1), vbTextCompare) - 1)
                Case "Nothing"
                    k = 1
                Case "Formula"
                    .FormulaR1C1 = shDes.Range(aColCopy(j, 1) & iRdes - 1).FormulaR1C1
                Case Else
                    .Value2 = shSrc.Range(aColCopy(j, 2) & 2).Resize(eR - 1).Value2
              End Select
            End With
        Next j
        Application.StatusBar = "Finish copying the sheet " & shSrc.Name
    Next i
    
    Application.StatusBar = ""
    OnEven
    shDes.Select
    Call ProtectAllSheets
    MsgBox "Completed; data transferred"
End Sub
Private Sub OnEven(Optional OnOn As Boolean = True)
   With Application
        .ScreenUpdating = OnOn
        .EnableEvents = OnOn
        .DisplayAlerts = OnOn
    If OnOn Then
        .Calculation = xlCalculationAutomatic
    Else
        .Calculation = xlCalculationManual
    End If
   End With
End Sub
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Forum statistics

Threads
1,224,548
Messages
6,179,445
Members
452,915
Latest member
hannnahheileen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top