Fill Blank Cell Not working

[el dee]

I have a macro that is supposed to copy and paste data from one spreadsheet
into another in the same workbook.
I need the blank cells to be filled with a space (" ") if there is not
data in it. I have the macro below.

TIA

The macro works for the data in the ("e40:e43") range, but removes the
pasted data in the ("c40:d43") range. Any ideas??

With ThisWorkbook
Set wksCopy = .Worksheets("Summary")
Set wksPaste = .Worksheets("Plot_Dominant_Rip_Veg")
End With

With Range("c40:d43", Range("c" &
Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks)
.FormulaR1C1 = "r1c1"
.Value = " "
.Columns("c").MergeCells = False
End With


With Range("e40:F43", Range("e" &
Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks)
.FormulaR1C1 = "r1c1"
.Value = " "
.Columns("e").MergeCells = False
End With


Set rngCopy = SetCopyRange(wksCopy, "B5:C5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "A")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "C4043")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "D")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "E40:F43")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "F")
rngCopy.Copy rngPaste

With Sheets("Plot_Dominant_Rip_Veg")
LastRow = .Cells(.Rows.Count, "f").End(xlUp).Row

'using the last used cell in column A to get the row to copy
Set RngToCopy = .Cells(.Rows.Count, "A").End(xlUp)

HowManyRows = LastRow - RngToCopy.Row + 1

If HowManyRows > 1 Then
RngToCopy.Resize(HowManyRows, 2).FillDown
End If

.Columns("A").MergeCells = False
End With

 

[Patrick Molloy]

1) this is wrong
.FormulaR1C1 = "r1c1"
.Value = " "
in the first line you put the letters r1c1 into the cell thenfollowed bay
puttig in a spcae
so all you need is

.Value = " "

2) this line
Range("c40:d43", Range("c" &
Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks)

is odd

your range syntax is screwed
With Range(Range(X),Range(Y) )

where X and Y are the topleft and bottom right cells

so maybe
Range( Range("c40") , Range("D" & Rows.Count).End(xlUp)
).SpecialCells(xlCellTypeBlanks)