Copy of xlsm file giving incorrect results

Aparajita

New Member
Joined
Aug 1, 2016
Messages
2
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?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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("o2:eek:1048576").Copy
Sheets("Final").Range("p2:p1048576").Select
ActiveSheet.Paste
Sheets("Final").Select
Sheets("Comp2").Range("p2:p1048576").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("o2:eek:1048576").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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

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