Copy Paste and Extend Row Formulas

[Papi]

I have a workbook that opens another workbook, does some sorting and then copies from Row 1 to the end row but only columns A:J. The macro returns to the first workbook and pastes those numbers by pressing enter into Cell A7. The problem is that two things can happen. The rows can be the same number as the month before or can increase by 1 or more rows. What I cannot figure out is how to get the columns from K:AM to copy and past those formulas down to the bottom row, which could be the same as before or more. Hopefully this makes sense.

 

[Jerry Sullivan]

Hi Papi,

If I understand you correctly, you could:
Get the last row in Column A
Get the last row in Column K.
If Last Row K > Last Row Then copy your formulas from Last Row K+1 to Last Row A.

On what row are your formulas for K:AM always located?

 

[Papi]

Thanks JS411,

This is something like the data looks like. The example shows four addition items from the other workbook and places them from Row A6 to J15, The issue is trying to get the last four (or more or less or the same) from K12:AM15 as those are all formulas.

Purchases.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
5		Total	Main	Second	Hardware	Fastener	Wood	Housewares	Electrical	Misc	2011		AlbertaDW	Ben's	Canyon
6	Customer	58	3	7	6	11	25		2	4	22		109	100
7	AlbertaDrywall	6	1		3		1		1		6	AlbertaDrywall	6
8	Ben'sBicycles	2		1			1				2	Ben'sBicycles	100
9	CanyonMeadowsElectrical	12	1	1	3		2		1	4	4	CanyonMeadowsElectrical		100
10	DeerfootDriveInn	3	1			1	1				3	DeerfootDriveInn	3
11	ErnstLithographers	7		1		2	4				7	ErnstLithographers
12	FrontPorchDesign	7		1		2	4
13	JavaJavaPath	7		1		2	4
14	KirchensPlus	7		1		2	4
15	Larken'sBooks&Mags	7		1		2	4
Main

 

[Jerry Sullivan]

Try...

Sub Fill_Formulas()
    Dim lLastRowColA As Long, lLastRowColK As Long
    
    '---get last row of Columns A and K
    lLastRowColA = Range("A" & Rows.Count).End(xlUp).Row
    lLastRowColK = Range("K" & Rows.Count).End(xlUp).Row
    If lLastRowColA <= lLastRowColK Then Exit Sub
    
    '---copy down
    With Range("K" & lLastRowColK & ":AM" & lLastRowColK)
        .Copy Destination:=.Offset(1, 0) _
            .Resize(lLastRowColA - lLastRowColK)
    End With
End Sub

 

[Papi]

Hello Jerry,

In simple terms it works perfectly. Thanks for your time and effort. It is truly appreciated.

Alan

 

[Jerry Sullivan]

My pleasure Alan.