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
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