Paste is failing just some times

sofiachr

Board Regular
Joined
Jan 15, 2013
Messages
90
Hi,

I have the following code in my document. The visible values in the autofilter is copied to another sheet and just some times when I paste the values it´s all moved one step to the left.

This is a list with words in for example english and swedish. The code is copying the words from column B:C in the first sheet and is pasting the words in column A:B in the second sheet. If the English words is in column A in tha second sheet and the swedish words is in column B, then the words are moved one step left so that the swedish words is in the english column and the english words is to the left of the A column, with other words, it dissappears. But this is just happening at some times and I don´t know when.

Sub starta_forhor()
Dim LR As Long, r As Range, nR As Long
Dim strName As String
strName = ActiveSheet.Name


Dim myPassword As String
myPassword = "x"
Application.ScreenUpdating = False


Sheets("Förhör").visible = True


Sheets(strName).Unprotect Password:=myPassword
Sheets("Förhör").Unprotect Password:=myPassword


With ActiveSheet
LR = .Range("A" & rows.Count).End(xlUp).Row
If LR = 1 Then
MsgBox "You must select one or more words.", vbExclamation, "No words has been chosen"
Sheets(strName).Protect Password:=myPassword
Sheets("Förhör").Protect Password:=myPassword
Sheets("Förhör").visible = xlVeryHidden
Exit Sub
End If

Set r = .Range("A1:C" & LR)
r.autofilter Field:=1, Criteria1:="a"
.Range("B2:C" & LR).Copy
End With
Sheets("Förhör").Select
Range("A1:E101").Value = ""
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets(strName).Range("A1").autofilter
Sheets(strName).Range("B2:C2").Copy
Sheets("Förhör").Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False


Range("J13").Calculate
Range("J21").Calculate
Range("I12").Value = ""
Range("I20").Value = ""
Range("I13").Value = ""
Range("I21").Value = ""
Range("J12").Value = ""
Range("J20").Value = ""
Columns("A:B").EntireColumn.AutoFit


Dim cellColor1 As Long
Dim cellColor2 As Long
cellColor1 = RGB(Red:=255, Green:=255, Blue:=255)
cellColor2 = RGB(Red:=0, Green:=0, Blue:=0)
Range("A2:B101").Font.Color = cellColor2


Worksheets(strName).AutoFilterMode = False
Worksheets("Förhör").AutoFilterMode = False


ActiveWindow.ScrollRow = 1
Range("A1").Select


Sheets(strName).Protect Password:=myPassword
Sheets("Förhör").Protect Password:=myPassword


Sheets(strName).visible = xlVeryHidden


Sheets("Förhör").Select


Application.ScreenUpdating = True
End Sub


Can anyone help please?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Watch MrExcel Video

Forum statistics

Threads
1,129,750
Messages
5,638,129
Members
417,010
Latest member
jnuss03

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
Top