Re: [ExcelVBA] Re: Copy column range of formulas to next column

 

Hi Steve

My first comment about your code is that Selects in macros should be avoided because they slow down execution and make the screen flicker.

c.Offset(0, -1).SelectSelection.Copy
could become :-

c.Offset(0, -1).Copy 

When you record a macro all of the defaults appear in the code, so :-
c.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone,  SkipBlanks:=False, Transpose:=False 

may be simplified to :-

c.PasteSpecial Paste:=xlPasteFormulas 

Here is how to find the last used cell in the range "B2:M2" :-

Set rLastUsedCellInRow = Range("B2:M2").SpecialCells(xlCellTypeBlanks).Offset(, -1).Cells(1, 1)

So :-

Sub PropagateFormulaToRightEmptyColumn()
    With Sheets("Rej by Month").Range("B2:M2").SpecialCells(xlCellTypeBlanks).Offset(, -1).Cells(1, 1)
        .Copy Destination:=Sheets("Rej by Month").Range(.Offset(, 1), .End(xlDown).Offset(, 1))
    End With
End Sub

Regards

Derek Turner

+++

>________________________________
> From: sspatriots <sspatriots@yahoo.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Thursday, 26 April 2012, 15:57
>Subject: [ExcelVBA] Re: Copy column range of formulas to next column
>
>
> 
>
>
>Got it! I must be getting better at this stuff...at least finding similar solutions on-line and making them work for me. Anyhow, below is what I came up with. If anyone sees that I have something that shouldn't be, please let me know, but it seems to work ok.
>
>Option Explicit
>Sub FindLastNonEmptyCellInRowRange()
>'
>' Find Last Non-Empty Cell In Row Range Copy Formulas Over From Col To Left Macro
>'
>
>'
>Application.DisplayAlerts = False
>Application.ScreenUpdating = False
>
>Sheets("Rej by Month").Select
>
>Dim c
>For Each c In Range("B2:M2").Cells
>If c = "" Then
>
>c.Offset(0, -1).Select
>Selection.Copy
>
>c.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
>SkipBlanks:=False, Transpose:=False
>
>Selection.Copy
>Selection.End(xlDown).Select
>ActiveCell.Offset(-1, 0).Range("A1").Select
>Range(Selection, Selection.End(xlUp)).Select
>ActiveSheet.Paste
>Selection.End(xlDown).Select
>
>Exit For
>End If
>Next
>
>Application.CutCopyMode = False
>
>Range("A1").Select
>
>Application.DisplayAlerts = True
>Application.ScreenUpdating = True
>
>End Sub
>
>--- In ExcelVBA@yahoogroups.com, "sspatriots" <sspatriots@...> wrote:
>>
>> Well I've managed to come up with the code below that gets me to the last cell with a formula in it in the second row. I just need to figure out how to tell it to copy it and the 38 cells with formulas directly below it over to the next column.
>>
>> Thanks,
>>
>> Steve
>>
>>
>>
>> Option Explicit
>> Sub FindLastNonEmptyCellInRowRange()
>> Dim c
>> For Each c In Range("B2:M2").Cells
>> If c = "" Then
>>
>> c.Offset(0, -1).Select
>>
>> Exit For
>> End If
>> Next
>> End Sub
>>
>> --- In ExcelVBA@yahoogroups.com, "sspatriots" <sspatriots@> wrote:
>> >
>> > Hi,
>> >
>> > I have a worksheet that has column headings by the month in the range 'B1:M1'. The first one is 'AUG' (B1), because that is the start of our fiscal year. Anyhow, right now I have formulas in the range 'B2:I40' that are populating data from another worksheet into that range. Columns 'B' through 'I' are the months from last 'AUG' through 'MAR', which is all I have data for at the moment. I don't want to put the formulas into 'J2:M40' until I get the data for the months of 'APR' through 'JUL' and I only want them added one month at a time as I obtain each months data.
>> >
>> > My reasoning for this is that this range of data gets fed into a bar chart with an exponential trendline. If those cells have anything in them (even a formula that makes them appear blank), the trendline option isn't available.
>> >
>> > So if I were to run the code today, I would want it to find the last range of formulas for the previous month's data (currently in 'I2:I40') and paste them into 'J2:J40' just before I bring my new data into the workbook for 'APR'. I hope this makes sense and that I've explained it clearly.
>> >
>> > Thanks in advance for any suggestions/ideas, because I've been scouring the web and not able to come up with anything close.
>> >
>> >
>> > Regards,
>> >
>> > Steve
>> >
>>
>
>
>
>
>

[Non-text portions of this message have been removed]

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (6)

Recent Activity:

• New Members 6

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___