Copy cell formulas

[UHsoccer]

I have many columns in my spreadsheet

Some cells have formulas others do not

I tried the following code to improve the creation of all formulas on the worksheet

   rowM = 16000
    Sheets("Sheet1").Select
    Range("BA4:KZ4").Select
    Selection.Copy
    Range("BA5:KZ" & rowM).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Th problem is that if a cell does not have a formula, it copies the value of the "from" cell

Please advice

 

[Fluff]

How about

Range("BA4:KZ4").Copy
With Range("BA5:KZ" & rowM)
   .PasteSpecial xlPasteFormulas
   .SpecialCells(xlConstants).ClearContents
End With

 

[UHsoccer]

How about

Range("BA4:KZ4").Copy
With Range("BA5:KZ" & rowM)
   .PasteSpecial xlPasteFormulas
   .SpecialCells(xlConstants).ClearContents
End With

1) It debugs on PasteSpecial with "PasteSpecial method of Range class failed"

2) Also the " SpecialCells(xlConstants).clearContents" statement would seem to remove a constant value in a cell without a formula

 

[Fluff]

1) If you're code worked I can't see why you'd get an error on the PasteSpecial line.
2) Yes that's right. Is that not what you wanted?

 

[UHsoccer]

Allow me to explain what I am trying to accomplish

I have csv file from an AS400 that contains about 20,000 rows of data in nearly 700 columns
That information is selectively copied to a spreadsheet which has 900 columns of data on a sheet called Sheet1

The columns of Sheet1 will contain textual data or plain numbers.
However there are columns mixed in that have formulas. Loading the formulas in ONE row (row 4) is fairly fast.
What consumes excessive time is to copy the formulas from row 4 to 5 and down to 20,000 ONE column at the time. THAT is what I try to do in one massive copy statement

'Range("BA4:KZ4").Select
    Selection.Copy
    Range("BA5:KZ" & rowM).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

It copies the formulas from cells that contain one, BUT it copies the VALUES from row 4 if a cell does not contain a formula

Is there a option to specify "SkipValues=True" ???? or something like that?

 

[Fluff]

You cannot "skip" cells, but you can clear the cells afterwards as I showed.

 

[Fluff]

Alternatively you can loop through the columns like

   Dim rng As Range, rowm As Long
   rowm = 100
   For Each rng In Range("B4:W4").SpecialCells(xlFormulas).Areas
      rng.Resize(rowm - 3).FillDown
   Next rng