VB to copy/delete formulas based upon last row of data 2

[password]

Hi

I have a worksheet where users will paste a block of data into columns A:E. In columns G:J there are a bunch of formula refering to columns A:E (mainly concatenations and vlookups). Row 1 is empty and Row 2 is headers.

Because the data paste that is made by the user can vary in terms of the number of rows, I want some code that copies the formulas in G:J to the same row number as that where the data paste ended.

eg. if 208 records were pasted the code would copy the formulas to row 208 (or 210 if I follow the example properly). If, next time, the user pastes 200 records (and deletes the 10 old ones) the VB should again evalute the last row of the data paste abd delete formula rows accordingly.

Summary> Last row of data paste = last row of formulas

Hoping also to use Worksheet_change so that this is automated when user makes the data paste.

Grateful for any help offered.

Regards

Nick

 

[mordja]

password,

ActiveSheet.UsedRange.End(xlDown).Row will give you the last used row of data.

Mordja

 

[TonyJollans]

Hi password,

How is the User going to do this paste? If the sheet contains 210 rows and they paste 200 over the top, the last 10 rows of user data (columns A:E) will not be deleted so you will have a hard job picking up the details you want in code.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[password]

Tony

The user may first delete the 'old data' and do the paste, or do the paste and then delete the excess lines. Either way, lets assume the data present is the data were working with. ie the last row is always the row reference we want.

Nick

 

[SkipVought]

Nick,

Cut 'n' paste in a periodicly updated workbooks (daily, weekly, monthly, etc) is a classic recipe for error and inefficiency. I see this problem so often at small mom 'n' pop companies as well as large corporations, like the Aerospace & Defense company that I am surrently consulting with.

Chances are, the data that you are copying is from a table in another workbook or on another sheet. If the table is properly designed, the very same thing could be accomplished with a simple Query via Data/Get External Data...

But I'd venture that an analysis of the business case, would reveal that a more direct solution could be accomplshed in some other entirely different way, quicker, simpler, and with less chance or error.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[TonyJollans]

Hi Nick,

The reason I ask is because User action affects the 'UsedRange'. It sounds as though you won't be able to use it.

If all the (relevant) cells are non-empty then ..
[blue][tt] [E1].End(xlDown).Row[/tt][/blue]
.. will get the last row. If not then you will need to check each column, perhaps like this ..
[blue][tt] Cells.Find("*", Range("E65536"), , , xlByRows, xlPrevious).Row[/tt][/blue]

When you have the row you should be able to fill down to that row in columns G:J - but will excess formulae need deleting as well?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[password]

Skip

Reason for this 2-step process is I cannot get required groupings from the original data source (SAP Business Warehouse). Therefore my process is to get 'raw data' from BW (pasted to cols A:E), lookup the necessary groupings (cols G:J) and summarise in a seperate sheet (using mainly SUMIFs based upon Dynamic Named Ranges (of data in G:J).

Regards

Nick

 

[SkipVought]

You could get an Excel workbook output from BW and save copy as.

Then in a new workbook

on sheet 1, Query to get columns A-E

on sheet 2, Query to get columns G-J

then do your lookups.

How is the data in A-E related to G-J? This may be able to be accomplushes with ONE QUERY!!!

Nick, you're not with lmco are you?

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[password]

Tony

Yes, all the relevant cells will be non-empty
Yes, excess formulae will need deleting as well.

I was thinking of something along the lines of:

[...on pasting of new data]

Clear contents of cells G4:J65xxx (ie end of sheet).
Copy G3:J3 and paste to G3:Jx (this x being the row number identified as being the last row of data in column A for example).

I'm ok at understanding/amending other peoples' code but cannot write code myself so grateful for some pointers.

Regards

Nick

 

[password]

Evening Skip

1) Disappointingly, my skills don't stretch to queries
2) This workbook will be distributed to the 'users' for them to run the BW query (with their user restrictions) and copy paste in the data to get the 'summary' they need - the users are infinately more useless in Excel than I am and I beleive this solution requires the least input from them
3) Example of data is colA = calendar day, colG=cal month (am then able to make summary by cal month...)
4) I had to do a search to see who IMCO was - International Muffler COmpany, Internation Mistral Class Organisation???

Regards

Nick

 

[SkipVought]

LMCO - Lockheed Martin

This solution would be...

1) save the BW Workbook in the same folder as the report workbook AS IS with a given file name, ALWAYS the same.

2) Open the report workbook and view the results!

It doesn't get much easier for users than that!

What are your column headings? I'll help you setup the query. NO CODE or very little, maybe!

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[password]

Skip

A-SalesCo
B-SalesBrand 1
C-Mill
D-G/M2
E-Cal.year / month
F-MonthOrigInv
G-Curr
H-Tons
I-Turnover Value
J-Turnover Value Main Curr
K is an empty column
L-months
M-string1
N-string2
O-curr string1
P-curr string2
Q-tons
R-EUR value
S-curr value
T-gsmtons

These headers in ROW2 (but no reason why they could not be ROW1 if this makes things easier.Not as few columns as I described and also there are two BW queries but if you can show me how this one works, I'll figure out the second.

Columns L:T are being SUMIF'ed in the summary table.

Regards

Nick

 

[SkipVought]

1) On a clean sheet perform the query -- Data/Get External Data/New Database Query - Excel Fiels -- Your BW Workbook -- scroll for Sheet1$ and >> include the table, [next],[next],[next],[finish]

2) copy your headings and ONE ROW OF FORMULAS to K1, the very next cell adjacent to the last heading.

3) Data/Import Data/Data Range Properties and select the check box for Fill down formulas in adjacent columns.

4) refresh the query -- Data/Refresh data

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[password]

Skip

Am just running through this and I hit what I think could be my first hurdle. Miscrosoft query was not installed for me (though it now has). Users will freak if they see the same issue.

Will anyhow update you when I've got this to work...

N

 

[SkipVought]

It probably is an Add-In -- Tools Addins...

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[password]

Hi

have got it to work, thanks. This is a useful technique.

However

1) there is the problem with the add-in
2) solution does not delete 'excess' formula rows.

Part 2 is my main issue as the number of formula rows (and the resulting size of the ranges) are having a big impact upon speed and size of the file, so the ranges need to be as small as possible, ie (the size of the data paste (or query data or whatever).

Nick

 

[SkipVought]

If you paset in ONE ROW of formulas, all contiguous, it will adjust.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[password]

Skip

Don't understand your last comment.

First time the user uses file he opens and the query gets the data and fills the formula the - perfect.
Next month user opens file again. The query updates but it has less rows than previous month = excess forula rows (generated from previous months' auto-fill).

I don't think your comment solves this.

Regards

Nick

 

[SkipVought]

First time the user uses file he opens and the query gets the data and fills the formula...

Why is the USER filling the formulas the first time?

YOU put the formulas in the FIRST ROW OF DATA starting in the column immediately adjacent to the last column of data from the query.

You also need to be sure that the Fill down formulas in adjacent columns is checked.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[password]

Skip

Apologies, I was not being clear.

First time the user uses file he opens and the query, gets the data, and the formulas automatically fill to the row number of the data paste - perfect.
Next month user opens file again. The query updates but it has less rows than previous month = excess forula rows (generated from previous months' auto-fill).

I've been working on the code today and and got a working solution which clears contents of formula rows (bar one) identifies the last cell of the data paste and copy pastes the formulas row to that row. Also managed to get this to work with the Worksheet_change funtion so coe runs when user pastes data.

Thanks for the simple intro to some new fuctionality in Excel - shame Microsoft Query is not installed as standard for my co. otherwise it would be one step better than the solution I have.

Regards

Nick