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