Switching from a cut/paste macro to a copy/paste&clear macro

[TheyCallMeIke]

Hi Guys,

So i've been using this macro to gather data that is logged daily on multiple spreadsheets, and what it does is open each spreadsheet, and copy and paste all the data into one main spreadsheet. The problem here is that the individual spreadsheets have formatting in the cells, so when the macro goes and copy&paste the data, it also removes the formatting from all of the cells.

This is a problem, and I don't know why it's removing the formatting. I thought maybe it could be that it was cut & pasting, so the solution would be to change it to copy & paste and then clear contents, so the formatting would stay on the individual spreadsheets. So the macro flow would be like:

Open individual spreadsheet > select active rows > copy > paste into "master spreadsheet" > clear active selection on "individual spreadsheet" > save & close "individual spreadsheet" > rinse & repeat onto next spreadsheet

I don't know how to go about making this change, because the macro that was created for this is not my scripting, he is no longer employed with us.

So I have two questions, because from whats below it looks like it is indeed a copy & paste, but i cant understand if that's true why it's also removing the formatting from the cells that its copy pasting from.

If it is indeed cutting, then what adjustment needs to be made to change this from a cut/paste to a copy/paste&clear?

Anyone that can help me, as always, I greatly appreciate it. I've been given ownership of this process a while back by my boss, but when it comes to macro issues on this, it's messy because I didn't create it.

Pasted below is the macro script modules


>>>>Modules<<<<
Book Constants

Option Explicit
'Constant Names used in procedures
Public Const MainPath As String = "G:\ROC-CLAIMS\Clms Proc-Model Line\Adjustment Tracking Data (Statewide)"
Public Const TrackingFolder As String = "Adjuster Tracking Log"
Public Const TrackingFile As String = TrackingFolder & ".xls"
Public Const MasterFolder As String = "Adjustment Master Tracking Log"
Public Const MasterName As String = MasterFolder & ".xls"

Public Const consErrMsg As String = "An error has been received. If the problem persists," & _
" please report the below information to emailhere[EMAIL="emailhere@!"]@gmail.org[/EMAIL]: "


LoadBooks

Public Function OpenBook(File As String) As Boolean
On Error GoTo OpenError
Workbooks.Open File, UpdateLinks:=0
OpenBook = True
Exit Function
OpenError:
OpenBook = False
End Function


PullData

Public Sub GetData()
Dim FilePath, FileName As String
Dim TrackLastRow As String, MLastRow As String
Dim Mnth As String
Dim Rng As Range
Dim CopyRng As Range
Dim MLog As Workbook, Track As Workbook
Set MLog = ThisWorkbook
Set Rng = MLog.Sheets("Maint.").Range("StaffList")
Mnth = MLog.ActiveSheet.Name
Application.ScreenUpdating = False
'for each staff name in the Range, copy the tracking log
For Each cell In Rng
'get path for tracking log
FilePath = MainPath & "\" & TrackingFolder & "\" & cell.Value
FileName = FilePath & "\" & TrackingFile
'try to open the log. If it fails, alert user and offer to skip it.
If OpenBook(FileName) = False Then
If MsgBox("Unable to open the Tracking Log for " & cell.Value & _
". Would you like to move to the next staff member?", vbYesNo) = vbNo Then
Exit Sub
Else
GoTo TryNext
End If
End If
'select the opened tracking book
For Each Book In Workbooks
If Left(Book.Name, 8) = "Adjuster" Then
Set Track = Book
GoTo FoundIt:
Else
Set Track = Nothing
End If

Next Book
FoundIt:
'make sure the tracking book was found and selected
If Track Is Nothing Then
MsgBox consErrMsg
Exit Sub
End If
'activate the tab for the indicated month
Track.Sheets(Mnth).Activate
'find the last used row in the indicated month tab
TrackLastRow = ActiveSheet.Range("B65536").End(xlUp).Row
'first row of user-entered data is row 3. Don't copy anything above row 3.
If TrackLastRow < 3 Then
TrackLastRow = 3
End If
'set used range for copying
Set CopyRng = ActiveSheet.Range("A3:O" & TrackLastRow)
MLastRow = MLog.Sheets(Mnth).Range("B65536").End(xlUp).Row + 1
If MLastRow < 3 Then
MLastRow = 3
End If
'copy data from individual tracking book into master log
CopyRng.Copy
MLog.Sheets(Mnth).Range("A" & MLastRow).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Track.Sheets(Mnth).Activate
'remove copied data from individual tracking book
Application.DisplayAlerts = False
Track.Sheets(Mnth).Range("A3:O" & TrackLastRow).Delete
Application.DisplayAlerts = True
Track.Close True
TryNext:
Next cell
Application.ScreenUpdating = True
End Sub

 

[TheyCallMeIke]

Wanted to break this up to make it more ledgible rather than a wall of text.

Book Constants

Option Explicit
'Constant Names used in procedures
Public Const MainPath As String = "G:\ROC-CLAIMS\Clms Proc-Model Line\Adjustment Tracking Data (Statewide)"
Public Const TrackingFolder As String = "Adjuster Tracking Log"
Public Const TrackingFile As String = TrackingFolder & ".xls"
Public Const MasterFolder As String = "Adjustment Master Tracking Log"
Public Const MasterName As String = MasterFolder & ".xls"

Public Const consErrMsg As String = "An error has been received. If the problem persists," & _
" please report the below information to emailhere@gmail.org: "


LoadBooks

Public Function OpenBook(File As String) As Boolean
On Error GoTo OpenError

Workbooks.Open File, UpdateLinks:=0
OpenBook = True

Exit Function
OpenError:
OpenBook = False

End Function


PullData

Public Sub GetData()

Dim FilePath, FileName As String
Dim TrackLastRow As String, MLastRow As String
Dim Mnth As String
Dim Rng As Range
Dim CopyRng As Range
Dim MLog As Workbook, Track As Workbook

Set MLog = ThisWorkbook
Set Rng = MLog.Sheets("Maint.").Range("StaffList")
Mnth = MLog.ActiveSheet.Name

Application.ScreenUpdating = False

'for each staff name in the Range, copy the tracking log
For Each cell In Rng

'get path for tracking log
FilePath = MainPath & "\" & TrackingFolder & "\" & cell.Value
FileName = FilePath & "\" & TrackingFile

'try to open the log. If it fails, alert user and offer to skip it.
If OpenBook(FileName) = False Then
If MsgBox("Unable to open the Tracking Log for " & cell.Value & _
". Would you like to move to the next staff member?", vbYesNo) = vbNo Then
Exit Sub
Else
GoTo TryNext
End If
End If

'select the opened tracking book
For Each Book In Workbooks
If Left(Book.Name, 8) = "Adjuster" Then
Set Track = Book
GoTo FoundIt:
Else
Set Track = Nothing
End If

Next Book

FoundIt:

'make sure the tracking book was found and selected
If Track Is Nothing Then
MsgBox consErrMsg
Exit Sub
End If

'activate the tab for the indicated month
Track.Sheets(Mnth).Activate

'find the last used row in the indicated month tab
TrackLastRow = ActiveSheet.Range("B65536").End(xlUp).Row

'first row of user-entered data is row 3. Don't copy anything above row 3.
If TrackLastRow < 3 Then
TrackLastRow = 3
End If

'set used range for copying
Set CopyRng = ActiveSheet.Range("A3:O" & TrackLastRow)

MLastRow = MLog.Sheets(Mnth).Range("B65536").End(xlUp).Row + 1
If MLastRow < 3 Then
MLastRow = 3
End If

'copy data from individual tracking book into master log
CopyRng.Copy
MLog.Sheets(Mnth).Range("A" & MLastRow).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Track.Sheets(Mnth).Activate
'remove copied data from individual tracking book
Application.DisplayAlerts = False
Track.Sheets(Mnth).Range("A3:O" & TrackLastRow).Delete
Application.DisplayAlerts = True
Track.Close True

TryNext:
Next cell

Application.ScreenUpdating = True
End Sub

And it's definetely a copy paste, i'm just wondering if i cant alter that last section from:

Track.Sheets(Mnth).Range("A3:O" & TrackLastRow).Delete

to

Track.Sheets(Mnth).Range("A3:O" & TrackLastRow).ClearContents

would that then fix my problem of trying to keep the formatting in the copy-from contents?