0

I am writing VBA code in Excel to copy formulas from the last row of data to the row below it and then copying that last row (now second-to-last row) and paste as values in it's place. I would like to do this for multiple sheets. The problem is that after it works properly for the first sheet, it errors out on the next sheet (and presumably the rest of them).

The code works for the first worksheet but when it moves to the next sheet, Excel gives me a "Run-time error '1004': No cells were found" error message". When I debug the error, the 2nd line in the 3rd paragraph below is what gives me the problem. What do I have to do to allow this code to work for multiple worksheets in the same workbook?

Sheets("BrentSkew").Select
NextFree = Range("A2:A" & 
Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row
Range("A" & NextFree - 1).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
NextFree = Range("A2:A" & 
Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row
Range("A" & NextFree).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, 
SkipBlanks _
    :=False, Transpose:=False

Sheets("BrentSkew").Select
NextFree = Range("A2:A" & 
Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row
Range("A" & NextFree - 2).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
NextFree = Range("A2:A" & 
Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row
Range("A" & NextFree - 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks 
_
    :=False, Transpose:=False

Sheets("LLSSkew").Select
'the line below is the problem
NextFree = Range("A2:A" & 
Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row
Range("A" & NextFree - 1).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
NextFree = Range("A2:A" & 
Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row
Range("A" & NextFree).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, 
SkipBlanks _
    :=False, Transpose:=False

Sheets("LLSSkew").Select
NextFree = Range("A2:A" & 
Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row
Range("A" & NextFree - 2).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
NextFree = Range("A2:A" & 
Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row
Range("A" & NextFree - 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks 
_
    :=False, Transpose:=False
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
TNieland
  • 27
  • 6
  • 2
    [Do NOT use Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Declare all your variables (put Option Explicit at the top of your module). That will lead you to most of your errors. – Ron Rosenfeld Feb 15 '19 at 19:25
  • just a comment, but it seems you missed the continuation line char. where you have an &. Replace with "& _" or it is the formatting a the text? – foxtrott Feb 15 '19 at 19:40

0 Answers0