[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
> >
>

__._,_.___

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

Messages in this topic (3)

Recent Activity:

• New Members 8
• New Files 1

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

.

__,_._,___