FillDown VBA Excel doesn't work on second Worksheet

[penera]

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?

[Aflatoon]

What is the error?

Also, what is the point of using this:


Workbooks(strWb).Sheets(strSheet).Cells(rngCel.Row, rngCel.Column).Value = rngZrodlo.Value

rather than just this:


rngCel.Value = rngZrodlo.Value

[penera]

Also, what is the point of using this:
Workbooks(strWb).Sheets(strSheet).Cells(rngCel.Row, rngCel.Column).Value = rngZrodlo.Value

Target can be a range. You can not assign one cell value to the range.


There is no error, FillDown just doesnt do his job.

However when i have changed .FillDown on .Select i get an error "Run Time Err '1004':
Saying there was a problem with .Select method of Range class. So it seems to be a hint. Maybe range object is wrong?
I was checking values in Cells on break mode and they are correct as expected.

[Aflatoon]

If rngCel can be more than one cell (which makes the variable name misleading! ;)) then use:


rngCel(1).Value = ...

I'd suggest using:


With Workbooks(strWb).Sheets(strSheet)
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 With
End Sub

[penera]

(which makes the variable name misleading! ;))
:) My Dear mate, i know it looks strange but Cel is written in Polish (cel means target - so rng(range) Cel(targetRange))


I'd suggest using:

With Workbooks(strWb).Sheets(strSheet)
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 With
End Sub


That made my code working... you deserve on ur VBAX Master Title in 110%.
Thanks mate, God bless You and your family!