Help With Copy Formula Down Macro

npalmer610 · #1 03-04-2016, 06:18 AM

Hello. I was able to tweak a macro that was provided on another forum to copy a formula down to the last row (based on the adjacent column). I would like to instruct the code to run on all sheets in the workbook, so long as it's not a blank sheet, without having to click through each sheet then rerun the macro. Could someone please assist me with that? Thank you!

Code:

Sub CopytoLast()
Dim LastRow As Long
Columns("F").ClearContents
LastRow = Cells(Rows.Count, "E").End(xlUp).Row
Range("F2:F" & LastRow).Formula = "=CONCATENATE(A2,"" "", D2, "" "", E2)"
End Sub

cobbe · #2 03-04-2016, 11:53 PM

Code:

Sub CopytoLast()
Dim LastRow As Long
 Columns("F").ClearContents
  LastRow = Cells(Rows.Count, "E").End(xlUp).Row
   Range("F2").Formula = "=CONCATENATE(A2,"" "", D2, "" "", E2)"
    Range("F2").AutoFill Destination:=Range("F2:F" & LastRow)
End Sub

K.r., Cobbe

Philb1 · #3 03-05-2016, 01:54 AM

This will loop through the sheets and provided the worksheet isn't blank, will run your code
Cheers

Code:

Option Explicit

Sub CopytoLast()
Dim LastRow As Long, shtCount As Long, i As Long

shtCount = ThisWorkbook.Worksheets.Count

For i = 1 To shtCount
    If Not Application.WorksheetFunction.CountA(ThisWorkbook.Sheets(i).Cells) = 0 Then
        Columns("F").ClearContents
        LastRow = Cells(Rows.Count, "E").End(xlUp).Row
        Range("F2:F" & LastRow).Formula = "=CONCATENATE(A2,"" "", D2, "" "", E2)"
    End If
Next i

Exit Sub
End Sub

npalmer610 · #4 03-14-2016, 07:32 AM

Quote:

Originally Posted by Philb1

This will loop through the sheets and provided the worksheet isn't blank, will run your code
Cheers

Code:

Option Explicit

Sub CopytoLast()
Dim LastRow As Long, shtCount As Long, i As Long

shtCount = ThisWorkbook.Worksheets.Count

For i = 1 To shtCount
    If Not Application.WorksheetFunction.CountA(ThisWorkbook.Sheets(i).Cells) = 0 Then
        Columns("F").ClearContents
        LastRow = Cells(Rows.Count, "E").End(xlUp).Row
        Range("F2:F" & LastRow).Formula = "=CONCATENATE(A2,"" "", D2, "" "", E2)"
    End If
Next i

Exit Sub
End Sub

Worked perfectly ... thank you!

Philb1 · #5 03-14-2016, 01:15 PM

That's good

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
a macro that can copy data from copy.xls to our current excel macro.xls based on criteria:	udhaya	Excel Programming	1	11-12-2015 10:12 AM
Question about Index match formula to copy all of cells in row	dmcg9760	Excel	1	11-08-2015 01:41 AM
Macro to copy formula from one cell to another	anwar	Excel Programming	1	04-25-2014 08:27 PM
Use formula to copy cell formats also	mike_abc	Excel	10	12-10-2011 01:55 AM
Excel copy formula	KMS	Excel	1	08-16-2011 03:02 PM