Copy of xlsm file giving incorrect results

[Aparajita]

Hi,

I had a created a code to automate part of my reporting. The format is of a template where I paste raw data in different tabs and then combine them to get the report format using macros. The original code works fine but when I create a copy of the xlsm file in which the original code was written to run weekly data I get incorrect results. The same data gives correct results in the original file but the not in the copies. I do not make any changes in the code in the copies, the format of the raw data is same. Any ideas why this could be happening?

 

[BarryL]

can you post your code?

 

[Aparajita]

can you post your code?

The code is huge and still refining but it is giving me the report format I need

Here you go :

Sub Macro2()
'Adding dashboard data'
'Adding colums for text to column'
Sheets("Comp2").Select
Columns("F:N").Insert Shift:=xlToRight
'Text to column for Ad Name Column'
Range("E1:E1048576").Copy
Range("F1:F1048576").Select
ActiveSheet.Paste
Selection.TextToColumns Destination:=Range("F1:F1048576"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="_", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True

'Adding headers to new columns'
Range("G1").Select
ActiveCell.FormulaR1C1 = "Country"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Device"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Targetting"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Gender"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Theme"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Creative"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Version"
Range("N1").Select
ActiveCell.FormulaR1C1 = "Copy"

'Adding Data from Raw Data to Report Format'
Sheets("Final").Select
Sheets("Comp2").Range("a2:a1048576").Copy
Sheets("Final").Range("a2:a1048576").Select
ActiveSheet.Paste
Sheets("Final").Select
Sheets("Comp2").Range("g2:g1048576").Copy
Sheets("Final").Range("e2:e1048576").Select
ActiveSheet.Paste
Sheets("Final").Select
Sheets("Comp2").Range("h2:h1048576").Copy
Sheets("Final").Range("g2:g1048576").Select
ActiveSheet.Paste
Sheets("Final").Select
Sheets("Comp2").Range("i2:i1048576").Copy
Sheets("Final").Range("h2:h1048576").Select
ActiveSheet.Paste
Sheets("Final").Select
Sheets("Comp2").Range("k2:k1048576").Copy
Sheets("Final").Range("i2:i1048576").Select
ActiveSheet.Paste
Sheets("Final").Select
Sheets("Comp2").Range("l2:l1048576").Copy
Sheets("Final").Range("j2:j1048576").Select
ActiveSheet.Paste
Sheets("Final").Select
Sheets("Comp2").Range("m2:m1048576").Copy
Sheets("Final").Range("k2:k1048576").Select
ActiveSheet.Paste
Sheets("Final").Select
Sheets("Comp2").Range("n2:n1048576").Copy
Sheets("Final").Range("l2:l1048576").Select
ActiveSheet.Paste
Sheets("Final").Select
Sheets("Comp2").Range("q2:q1048576").Copy
Sheets("Final").Range("n2:n1048576").Select
ActiveSheet.Paste
Sheets("Final").Select
Sheets("Comp2").Range("n2:n1048576").Copy
Sheets("Final").Range("l2:l1048576").Select
ActiveSheet.Paste
Sheets("Final").Select
Sheets("Comp2").Range("o21048576").Copy
Sheets("Final").Range("p21048576").Select
ActiveSheet.Paste
Sheets("Final").Select
Sheets("Comp2").Range("p21048576").Copy
Sheets("Final").Range("q2:q1048576").Select
ActiveSheet.Paste
Sheets("Final").Select

Dim Row1 As Long
Row1 = Cells(Rows.Count, 1).End(xlUp).row
ActiveSheet.Range(Cells(Row1, 2), Cells(2, 2)).value = "Comp2"
ActiveSheet.Range(Cells(Row1, 3), Cells(2, 3)).value = "KCP"
ActiveSheet.Range(Cells(Row1, 4), Cells(2, 4)).value = "Comp2"

'Calculating total spend'
Dim rngData As Range
Sheets("Final").Range("n2:n1048576").Copy
Sheets("Final").Range("o21048576").Select
ActiveSheet.Paste
Set rngData = Sheets("Final").Range(Cells(Row1, 15), Cells(2, 15))
rngData = Evaluate(rngData.Address & "*1.07")

'Adding MAT Data'
'Removing Zero instal data lines'

Dim ws As Worksheet
Dim lastrow As Long, i As Long
Dim value As String

Set ws = ActiveWorkbook.Sheets("Comp3")
lastrow = ws.Range("Z" & ws.Rows.Count).End(xlUp).row

' Evaluate each row for deletion.
' Go in reverse order so indexes don't get messed up.
For i = lastrow To 2 Step -1
value = ws.Cells(i, 26).value ' Column AA value'

If value = 0 Then
ws.Rows(i).Delete
End If
Next

Dim ws1 As Worksheet
Dim LastRow1 As Long, j As Long
Dim value1 As String

Set ws1 = ActiveWorkbook.Sheets("Comp1")
LastRow1 = ws1.Range("Z" & ws1.Rows.Count).End(xlUp).row

' Evaluate each row for deletion.
' Go in reverse order so indexes don't get messed up.
For j = LastRow1 To 2 Step -1
value1 = ws1.Cells(j, 26).value ' Column AA value'

If value1 = 0 Then
ws1.Rows(j).Delete
End If
Next

Dim ws2 As Worksheet
Dim LastRow2 As Long, k As Long
Dim value2 As String
Dim value3 As String

Set ws2 = ActiveWorkbook.Sheets("Comp4")
LastRow2 = ws2.Range("Z" & ws2.Rows.Count).End(xlUp).row

' Evaluate each row for deletion.
' Go in reverse order so indexes don't get messed up.
For k = LastRow2 To 2 Step -1
value2 = LCase(ws2.Cells(k, 12).value) ' Column M value'
value3 = ws2.Cells(k, 26).value 'Column AA value'

If value3 = 0 Then
ws2.Rows(k).Delete
If InStr(value2, "in") = 0 Then
ws2.Rows(k).Delete
End If
End If
Next

'Text To Column to get Version and Size'
Sheets("Comp3").Select
Columns("Q:S").Insert Shift:=xlToRight
Range("P1:P1048576").Copy
Range("Q1:Q1048576").Select
ActiveSheet.Paste
Selection.TextToColumns Destination:=Range("Q1:Q1048576"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="_"

Sheets("Comp1").Select
Columns("Q:S").Insert Shift:=xlToRight
Range("P1:P1048576").Copy
Range("Q1:Q1048576").Select
ActiveSheet.Paste
Selection.TextToColumns Destination:=Range("Q1:Q1048576"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="_"

Sheets("Comp4").Select
Columns("Q:S").Insert Shift:=xlToRight
Range("P1:P1048576").Copy
Range("Q1:Q1048576").Select
ActiveSheet.Paste
Selection.TextToColumns Destination:=Range("Q1:Q1048576"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="_"

'Copy data from MAT reports'
'For Comp3 Mobile'
Sheets("Comp3").Select
Dim LR1 As Long
Dim LR2 As Long
Dim LR3 As Long
LR1 = Cells(Rows.Count, 1).End(xlUp).row
LR2 = Sheets("Final").Cells(Rows.Count, 1).End(xlUp).row
Sheets("Comp3").Range(Cells(2, 1), Cells(LR1, 1)).Copy
Sheets("Final").Select
Sheets("Final").Range(Cells(LR2 + 1, 1), Cells(1048576, 1)).Select
ActiveSheet.Paste
Sheets("Comp3").Select
Range(Cells(2, 5), Cells(LR1, 5)).Copy
Sheets("Final").Select
Sheets("Final").Range(Cells(LR2 + 1, 4), Cells(1048576, 4)).Select
ActiveSheet.Paste
Sheets("Comp3").Select
Range(Cells(2, 7), Cells(LR1, 7)).Copy
Sheets("Final").Select
Sheets("Final").Range(Cells(LR2 + 1, 6), Cells(1048576, 6)).Select
ActiveSheet.Paste
Sheets("Comp3").Select
Range(Cells(2, 6), Cells(LR1, 6)).Copy
Sheets("Final").Select
Sheets("Final").Range(Cells(LR2 + 1, 7), Cells(1048576, 7)).Select
ActiveSheet.Paste
Sheets("Comp3").Select
Range(Cells(2, 14), Cells(LR1, 14)).Copy
Sheets("Final").Select
Sheets("Final").Range(Cells(LR2 + 1, 9), Cells(1048576, 9)).Select
ActiveSheet.Paste
Sheets("Comp3").Select
Range(Cells(2, 16), Cells(LR1, 16)).Copy
Sheets("Final").Select
Sheets("Final").Range(Cells(LR2 + 1, 10), Cells(1048576, 10)).Select
ActiveSheet.Paste
Sheets("Comp3").Select
Range(Cells(2, 28), Cells(LR1, 28)).Copy
Sheets("Final").Select
Sheets("Final").Range(Cells(LR2 + 1, 18), Cells(1048576, 18)).Select
ActiveSheet.Paste
LR3 = Sheets("Final").Cells(Rows.Count, 1).End(xlUp).row
Sheets("Final").Range(Cells(LR3, 2), Cells(LR2 + 1, 2)).value = "Ad Network"
Sheets("Final").Range(Cells(LR3, 3), Cells(LR2 + 1, 3)).value = "Branding"
Sheets("Final").Range(Cells(LR3, 8), Cells(LR2 + 1, 8)).value = "RON"
Sheets("Comp3").Select
Range(Cells(2, 17), Cells(LR1, 17)).Copy
Sheets("Final").Select
Sheets("Final").Range(Cells(LR2 + 1, 11), Cells(1048576, 11)).Select
ActiveSheet.Paste
Sheets("Comp3").Select
Range(Cells(2, 18), Cells(LR1, 18)).Copy
Sheets("Final").Select
Sheets("Final").Range(Cells(LR2 + 1, 13), Cells(1048576, 13)).Select
ActiveSheet.Paste

'For Comp1'
Sheets("Comp1").Select
Dim LR11 As Long
Dim LR12 As Long
Dim LR13 As Long
LR11 = Cells(Rows.Count, 1).End(xlUp).row
LR12 = Sheets("Final").Cells(Rows.Count, 1).End(xlUp).row
Sheets("Comp1").Range(Cells(2, 1), Cells(LR11, 1)).Copy
Sheets("Final").Select
Sheets("Final").Range(Cells(LR12 + 1, 1), Cells(1048576, 1)).Select
ActiveSheet.Paste
Sheets("Comp1").Select
Range(Cells(2, 5), Cells(LR11, 5)).Copy
Sheets("Final").Select
Sheets("Final").Range(Cells(LR12 + 1, 4), Cells(1048576, 4)).Select
ActiveSheet.Paste
Sheets("Comp1").Select
Range(Cells(2, 7), Cells(LR11, 7)).Copy
Sheets("Final").Select
Sheets("Final").Range(Cells(LR12 + 1, 6), Cells(1048576, 6)).Select
ActiveSheet.Paste
Sheets("Comp1").Select
Range(Cells(2, 6), Cells(LR11, 6)).Copy
Sheets("Final").Select
Sheets("Final").Range(Cells(LR12 + 1, 7), Cells(1048576, 7)).Select
ActiveSheet.Paste
Sheets("Comp1").Select
Range(Cells(2, 14), Cells(LR11, 14)).Copy
Sheets("Final").Select
Sheets("Final").Range(Cells(LR12 + 1, 9), Cells(1048576, 9)).Select
ActiveSheet.Paste
Sheets("Comp1").Select
Range(Cells(2, 16), Cells(LR11, 16)).Copy
Sheets("Final").Select
Sheets("Final").Range(Cells(LR12 + 1, 10), Cells(1048576, 10)).Select
ActiveSheet.Paste
Sheets("Comp1").Select
Range(Cells(2, 28), Cells(LR11, 28)).Copy
Sheets("Final").Select
Sheets("Final").Range(Cells(LR12 + 1, 18), Cells(1048576, 18)).Select
ActiveSheet.Paste
LR13 = Sheets("Final").Cells(Rows.Count, 1).End(xlUp).row
Sheets("Final").Select
Range(Cells(LR13, 2), Cells(LR12 + 1, 2)).value = "Ad Network"
Range(Cells(LR13, 3), Cells(LR12 + 1, 3)).value = "KCP"
Range(Cells(LR13, 8), Cells(LR12 + 1, 8)).value = "RON"
Sheets("Comp1").Select
Range(Cells(2, 17), Cells(LR11, 17)).Copy
Sheets("Final").Select
Sheets("Final").Range(Cells(LR12 + 1, 11), Cells(1048576, 11)).Select
ActiveSheet.Paste
Sheets("Comp1").Select
Range(Cells(2, 18), Cells(LR11, 18)).Copy
Sheets("Final").Select
Sheets("Final").Range(Cells(LR12 + 1, 13), Cells(1048576, 13)).Select
ActiveSheet.Paste
Sheets("Final").Select
Range(Cells(LR13, 5), Cells(2, 5)).value = "IN"

''For Comp4'
'Sheets("Comp4").Select
'Dim LR30 As Long
'Dim LR31 As Long
'Dim LR32 As Long
'LR30 = Cells(Rows.Count, 1).End(xlUp).row
'LR31 = Sheets("Final").Cells(Rows.Count, 1).End(xlUp).row
'Sheets("Comp4").Range(Cells(2, 1), Cells(LR30, 1)).Copy
'Sheets("Final").Select
'Sheets("Final").Range(Cells(LR31 + 1, 1), Cells(1048576, 1)).Select
'ActiveSheet.Paste
'Sheets("Comp4").Select
'Range(Cells(2, 5), Cells(LR30, 5)).Copy
'Sheets("Final").Select
'Sheets("Final").Range(Cells(LR31 + 1, 4), Cells(1048576, 4)).Select
'ActiveSheet.Paste
'Sheets("Comp4").Select
'Range(Cells(2, 7), Cells(LR30, 7)).Copy
'Sheets("Final").Select
'Sheets("Final").Range(Cells(LR31 + 1, 6), Cells(1048576, 6)).Select
'ActiveSheet.Paste
'Sheets("Comp4").Select
'Range(Cells(2, 6), Cells(LR30, 6)).Copy
'Sheets("Final").Select
'Sheets("Final").Range(Cells(LR31 + 1, 7), Cells(1048576, 7)).Select
'ActiveSheet.Paste
'Sheets("Comp4").Select
'Range(Cells(2, 14), Cells(LR30, 14)).Copy
'Sheets("Final").Select
'Sheets("Final").Range(Cells(LR31 + 1, 9), Cells(1048576, 9)).Select
'ActiveSheet.Paste
'Sheets("Comp4").Select
'Range(Cells(2, 16), Cells(LR30, 16)).Copy
'Sheets("Final").Select
'Sheets("Final").Range(Cells(LR31 + 1, 10), Cells(1048576, 10)).Select
'ActiveSheet.Paste
'Sheets("Comp4").Select
'Range(Cells(2, 28), Cells(LR30, 28)).Copy
'Sheets("Final").Select
'Sheets("Final").Range(Cells(LR31 + 1, 18), Cells(1048576, 18)).Select
'ActiveSheet.Paste
'LR32 = Sheets("Final").Cells(Rows.Count, 1).End(xlUp).row
'Sheets("Final").Select
'Range(Cells(LR32, 2), Cells(LR31 + 1, 2)).value = "Ad Network"
'Range(Cells(LR32, 3), Cells(LR31 + 1, 3)).value = "KCP"
'Range(Cells(LR32, 8), Cells(LR31 + 1, 8)).value = "RON"
'Sheets("Comp4").Select
'Range(Cells(2, 17), Cells(LR30, 17)).Copy
'Sheets("Final").Select
'Sheets("Final").Range(Cells(LR31 + 1, 11), Cells(1048576, 11)).Select
'ActiveSheet.Paste
'Sheets("Comp4").Select
'Range(Cells(2, 18), Cells(LR30, 18)).Copy
'Sheets("Final").Select
'Sheets("Final").Range(Cells(LR31 + 1, 13), Cells(1048576, 13)).Select
'ActiveSheet.Paste
'Sheets("Final").Select
'Range(Cells(LR32, 5), Cells(2, 5)).value = "IN"


'Formatting Data'
Sheets("Final").Columns("G").Replace _
What:="ios tablet", Replacement:="iPad", _
SearchOrder:=xlByColumns, MatchCase:=True
Sheets("Final").Columns("G").Replace _
What:="ios phone", Replacement:="iPhone", _
SearchOrder:=xlByColumns, MatchCase:=True
Sheets("Final").Columns("G").Replace _
What:="android phone", Replacement:="Android Phone", _
SearchOrder:=xlByColumns, MatchCase:=True
Sheets("Final").Columns("G").Replace _
What:="android tablet", Replacement:="Android Tablet", _
SearchOrder:=xlByColumns, MatchCase:=True

Dim LR17 As Long
Dim val1 As String
Dim val2 As String
Dim val3 As String


LR17 = Sheets("Final").Cells(Rows.Count, 8).End(xlUp).row
For N = LR17 To 2 Step -1
val1 = Sheets("Final").Cells(N, 8).value
val2 = Sheets("Final").Cells(N, 8).value
val3 = Left(val2, 8)

If val1 = "Instagram Readers" Or val1 = "Instagram Travelers & Commuters" Or val1 = "Instagram Readers (Video Classic)" Or val1 = "Instagram Readers (Video Viking)" Then
Sheets("Final").Cells(N, 3).value = " Instagram - Branding"
ElseIf val3 = "Comp2" Then
Sheets("Final").Cells(N, 3).value = "Branding - Comp2 Video"
ElseIf val1 = "First Book on Us" Then
Sheets("Final").Cells(N, 3).value = "Innovation - First Book on Us"
End If
Next


End Sub

 

[BarryL]

what is exactly is coming up different when you run from a copy of the file? have you stepped through the macro using F8 and highlighted the parts that are going wrong?