אקסל: הדבקה מותאמת אישית (Ctrl+V) עם דילוג אוטומטי על עמודות מוסתרות

ל' שואל:

בגוגל שיטס ובאקסל איך אני יכול לעשות שבהדבקת כמה עמודות יחדיו, יתעלם מעמודה מוסתרת (אני מעתיק עמודות ABC מגליון 1 לגליון 2,

ובגליון 2 עמודה B מוסתרת, אני רוצה שידביק בעמודות ACD ויתעלם מעמודה B).

תשובה:

באקסל אי אפשר להדביק נתונים תוך כדי דילוג על עמודה מוסתרת עם הדבקה רגילה.

העמודה מוסתרת רק בתצוגה ולכן בהדבקה אקסל ידביק בה נתונים.

הדבקה מיוחדת בתאים גלויים בלבד תחזיר שגיאה במקרים כאלה בהם מקור הנתונים הוא טווח רציף והיעד לא רציף.

ניתן לפתור זאת על ידי שימוש בקוד מאקרו macro שיבצע הדבקה חכמה לעמודות גלויות ביעד וידלג על עמודות מוסתרות hidden columns.

יש ליצור קובץ אקסל חדש ולשמור אותו עם סיומת xlsm התומכת בהרצת קוד vba.

יש ללחוץ על Alt+F11 על מנת לפתוח את עורך VBA ואז בתפריט למעלה לבחור insert -> module והלדביק את הקוד של המודולים SmartCopy ו-SmartPasteSkipHiddenCols

Public strCopySheet As String, strCopyAddr As String

Public Sub SmartCopy()
strCopySheet = ActiveSheet.Name
strCopyAddr = Selection.Address
Selection.Copy
End Sub

Public Sub SmartPasteSkipHiddenCols()

Dim wsSrc As Worksheet, wsDst As Worksheet, rngSrc As Range
Dim destTopLeft As Range, j As Long, destCol As Long, nRows As Long
Dim dstColRange As Range

If Len(strCopySheet) = 0 Or Len(strCopyAddr) = 0 Then
On Error Resume Next
ActiveSheet.Paste
On Error GoTo 0
Exit Sub
End If

Set wsSrc = ThisWorkbook.Worksheets(strCopySheet)
Set wsDst = ActiveSheet
Set rngSrc = wsSrc.Range(strCopyAddr)
Set destTopLeft = ActiveCell

nRows = rngSrc.Rows.Count
destCol = destTopLeft.Column

Application.ScreenUpdating = False

For j = 1 To rngSrc.Columns.Count

Do While wsDst.Columns(destCol).Hidden = True
destCol = destCol + 1
Loop

Set dstColRange = wsDst.Cells(destTopLeft.Row, destCol).Resize(nRows, 1)

dstColRange.Value = rngSrc.Columns(j).Value

destCol = destCol + 1
Next j

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

בעורך VBA בצד שמאל יש ללחוץ על ThisWorkbook ולהדביק את הקוד של המודולים Workbook_Open ו-Workbook_BeforeClose

Public Sub Workbook_Open()
Application.OnKey "^c", "SmartCopy"
Application.OnKey "^v", "SmartPasteSkipHiddenCols"
End Sub

Public Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "^c"
Application.OnKey "^v"
End Sub

לאחר מכן יש לסגור את הקובץ ולפתוח מחדש על מנת שהמודולים יעבדו.