penera
06-18-2018, 06:37 AM
Hi there!
I'm fighting with it for about few hours and i can not make it working.
I've got quite big project in VBA, few days ago i decided to remove all .copy commands from my code to give back to users a clipboard, coz they works on it a lot.
Making a Sub, which will copy my stuff from one cell to range and from one cell to one, i found a problem with .FillDown and .FillRight Method of Range.
Here is my code:
Public Sub subKopiujDoZakresu(rngZrodlo As Range, rngCel As Range, Optional blnFormat As Boolean)Dim lngIlRek As Long, lngIlKol As Long
Dim intRek As Integer, intKol As Integer
Dim strSheet As String, strWb As String
strSheet = rngCel.Parent.Name
strWb = rngCel.Worksheet.Parent.Name
lngRek = rngCel.Row
lngKol = rngCel.Column
lngIlRek = rngCel.Rows.Count
lngIlKol = rngCel.Columns.Count
If blnFormat = False Then
Workbooks(strWb).Sheets(strSheet).Cells(rngCel.Row, rngCel.Column).Value = rngZrodlo.Value
Else
Workbooks(strWb).Sheets(strSheet).Cells(rngCel.Row, rngCel.Column).Value(11) = rngZrodlo.Value(11)
End If
Workbooks(strWb).Sheets(strSheet).Activate
For c = 0 To lngIlKol - 1
If c >= 1 Then
Range(Cells(lngRek, lngKol + c - 1), Cells(lngRek, lngKol + c)).FillRight
End If
If lngIlRek > 1 Then
Range(Cells(lngRek, lngKol + c), Cells(lngIlRek, lngKol + c)).FillDown
End If
Next c
End Sub
If I call it to copy from one sheet and past to the same sheet, it works very well. Fast and correct. But when I want to paste to another sheet FillDown and FillRight doesnt work without any error.
Any idea my more advanced friends?
I'm fighting with it for about few hours and i can not make it working.
I've got quite big project in VBA, few days ago i decided to remove all .copy commands from my code to give back to users a clipboard, coz they works on it a lot.
Making a Sub, which will copy my stuff from one cell to range and from one cell to one, i found a problem with .FillDown and .FillRight Method of Range.
Here is my code:
Public Sub subKopiujDoZakresu(rngZrodlo As Range, rngCel As Range, Optional blnFormat As Boolean)Dim lngIlRek As Long, lngIlKol As Long
Dim intRek As Integer, intKol As Integer
Dim strSheet As String, strWb As String
strSheet = rngCel.Parent.Name
strWb = rngCel.Worksheet.Parent.Name
lngRek = rngCel.Row
lngKol = rngCel.Column
lngIlRek = rngCel.Rows.Count
lngIlKol = rngCel.Columns.Count
If blnFormat = False Then
Workbooks(strWb).Sheets(strSheet).Cells(rngCel.Row, rngCel.Column).Value = rngZrodlo.Value
Else
Workbooks(strWb).Sheets(strSheet).Cells(rngCel.Row, rngCel.Column).Value(11) = rngZrodlo.Value(11)
End If
Workbooks(strWb).Sheets(strSheet).Activate
For c = 0 To lngIlKol - 1
If c >= 1 Then
Range(Cells(lngRek, lngKol + c - 1), Cells(lngRek, lngKol + c)).FillRight
End If
If lngIlRek > 1 Then
Range(Cells(lngRek, lngKol + c), Cells(lngIlRek, lngKol + c)).FillDown
End If
Next c
End Sub
If I call it to copy from one sheet and past to the same sheet, it works very well. Fast and correct. But when I want to paste to another sheet FillDown and FillRight doesnt work without any error.
Any idea my more advanced friends?