VBA to copy/paste etc..

[reformatted]

I'm trying to sort something out in excel which will involve VBA, problem is I don't have the first clue what I'm doing.

I've browsed loads of forums similar to this and I've probably seen the code that would help me out, but I've never used VBA before so it either goes straight over my head, or I have no idea how to relate it to the spreadsheet I'm actually looking at.

Bottom line is, I'm a beginner, I know I am, so please don't make an issue of it, but I'm hoping somebody can explain in simple terms what I need to do (simply pasting a load of code isn't going to help).:dunno

Assuming you're still reading, what I'm trying to do is this:

I have a spreadsheet, say 7 columns. Column A is a freetext identifier field; the other 6 have Vlookup formulae to return data in other sheets. Given that the data in the other sheets changes constantly, I need to keep a record of the data as it is when the identifier (a 7 digit number) is actually entered. Otherwise as the data in the other sheets changes I'll just end up with #N/A across the board.

What I want to do is this:

Have a macro to:

-When the doc is saved...
*Select all non-blank cells,
*copy info in non-blank cells
*past special as values

-which I would hope will leave a load of values, retaining the formulae down the remaining rows of the sheet.

However I'm anticipating that a cell with a formula in is counted as a non-blank cell?

In that case I would like to:

-When the doc is saved...
*Select all cells in sheet 1
*copy info in cells in sheet 1
*past special as values in sheet 1
*copy the info on range $B$1:$G$1 in sheet 2 (I would keep a copy of the formulae in sheet 1 here, in this range simply to copy from)
*paste this info from sheet 2 into the first blank row in sheet 1.
*copy this info down, say 100 rows.

I'm hoping, if somebody is feeling uber-charitable, they would be able to help me out with some VBA code to do one or both of these options.

Or at least give me some ideas?

Or failing that, just not bite my head off for being clueless.

Cheers

[lucas]

-When the doc is saved...
1*Select all cells in sheet 1
2*copy info in cells in sheet 1
3*past special as values in sheet 1
4*copy the info on range $B$1:$G$1 in sheet 2 (I would keep a copy of the formulae in sheet 1 here, in this range simply to copy from)
5*paste this info from sheet 2 into the first blank row in sheet 1.
6*copy this info down, say 100 rows.



Trying to clarify your need:

A). step 2 and 3 are to copy all the data(formula's) on sheet1 and paste them back into the exact same position in sheet one only as values, not forumla's.
B). In step 4 you want to overwrite that same data with the original formula.

Now it's early here and I'm on my first cup of coffee so bear with me in trying to understand what you are trying to do.

If you are trying to save a permanent copy of a set of inputs you could copy that entire range to a new sheet and paste them as values and that would leave your original set of formula's intact.

Welcome to VBA Express, the friendliest forum on the web.

[reformatted]

thanks for your reply,

your point A is correct.

Because I will have pasted values, I will lose the formula info in sheet 1.

I therefore envisage having a copy of the same set of forumula which are in columns B:G, in a separate sheet/tab (sheet 2).

Once the paste special is completed on sheet 1, there will be a range of data with blank cells at the end.

I then want to find the first blank row in sheet 1, and copy and paste these formula from Sheet 2, into this first blank row on sheet 1.
Finally I want to drag the formulae down the remaining blank rows on sheet 1, so that the sheet continues.

Let me know if you need anything else explaining further.

I appreciate your time

[mdmackillop]

If you could post a sample workbook with your actual layout and test data, it would be easier to provide a working solution.

[reformatted]

If you could post a sample workbook with your actual layout and test data, it would be easier to provide a working solution.

Hope that attachment works (I've altered it to take out (I hope) any identifiable info, but you should get the idea). Yes it is work-related, if you wondered.

Basically Data is entered manually in the "data" tab. The "Equal Ops data" tab is one of 3 (I removed the others in this example) which populate automatically by doing a range of vlookup functions reading from the "Staff Data" tab.

The reason I'm posting here is that the data in the "Staff Data" tab is refreshed manually from a monthly report, and as such the info on the "Equal ops" tab will change accordingly. Except I don't want it to change - I want to retain the data as it was when it was originally entered - which is why I want to use VBA to copy and paste values, but either retain or replace the formulae in the cells below the data (which in this example would mean anything below row 2 in "Equal ops").

Does that help? Hopefully we'll get there.

cheers

[GTO]

I'm trying to sort something out in excel which will involve VBA, problem is I don't have the first clue what I'm doing...

...but I'm hoping somebody can explain in simple terms what I need to do (simply pasting a load of code isn't going to help).:dunno

...What I want to do is this:

...

...However I'm anticipating that a cell with a formula in is counted as a non-blank cell?

...

Greetings and a friendly 'Howdy' from Arizona,

Between Steve's clarifications and your following Malcom's suggestion, I think (or leastwise hope) this is close to one way of accomplishing.

You mentioned being new to vba, so I commented the code quite a bit. If this is "too explanatory" please say so.

In the comments, words bordered by arrows, like <Range>, are the functions/methods etc, that I think you'll want to look up in vba help, to see what the code is doing.


'//*************************************************************************** **********//
'// Note: Change codename Sheet2 to shtEqualOpsData //
'//*************************************************************************** **********//

Option Explicit

Sub CompileFormulas()
Dim _
rngInstantOrigin As Range, _
strAddress As String

'// We are using the <With> statement for convenience. So, anything between here //
'// and the End With that starts with a dot, belongs to/is qualified by //
'// shtEqualOpsData. //
With shtEqualOpsData

'// First, we will look to locate the last row that currently has data, in the //
'// range we are interested in. Since we have a header row, we eliminate //
'// looking in row one, by starting our range to search at row 2. As we are //
'// using the <Find> method in the <Function> called (RangeFound), we will not //
'// be tripped-up by the cells w/formulas that are currently returning an empty //
'// string. Rather, (using the example wb you provided) rngInstantOrigin will //
'// end up being a reference to J2, the lower/right most cell with data in it, //
'// in this case, the #REF! error //
Set rngInstantOrigin = RangeFound(Range(.Range("A2"), .Cells(Rows.Count, 10)))

'// Let's say this was the first time we used the wb, and thus, there are the //
'// formulas present, but as no employee numbers have been entered on the Data //
'// sheet, the formulas are returning "". In that case, we need to bail, as //
'// if no data was found, rngInstantOrigin didn't get set. //
If rngInstantOrigin Is Nothing Then Exit Sub

'// Presuming we passed the above test, we know we have entries already made, //
'// so we're off and running. //

'// Using <Range> and <Cells>, we'll reference the range that we currently want //
'// to just have the values left in and ditch any formulas. Note that although //
'// we start with an unqualified Range, the start and end of the range are both //
'// qualified to our sheet, thus the range is properly qualified. //
Range(.Range("A2"), .Cells(rngInstantOrigin.Row, 10)).Value _
= Range(.Range("A2"), .Cells(rngInstantOrigin.Row, 10)).Value

'// Since the lowermost/rightmost cell w/data was found and referenced, but we //
'// want to have a better 'cornerstone' to work from, we'll re-set it to the //
'// cell in Col A in the same row. //
Set rngInstantOrigin = .Cells(rngInstantOrigin.Row, 1)
End With

'// Now we'll <Offset> by one row, to get the address of the first cell in Col A //
'// that we want to use for our formulas. //
strAddress = rngInstantOrigin.Offset(1).Address(False, False)

'// Finally, we will Offset one row, and x Col(s) to plunk the formulas in. We are //
'// also using <Resize> to plunk the formulas into the next 10 rows in the respective//
'// columns. //

rngInstantOrigin.Offset(1, 0).Resize(10).Formula = _
"=IF(Data!" & strAddress & "="""","""",Data!" & strAddress & ")"

rngInstantOrigin.Offset(1, 1).Resize(10).Formula = _
"=IF(" & strAddress & "="""","""",VLOOKUP(" & strAddress & ",'staff data '!$A:$AY,5,FALSE))"

rngInstantOrigin.Offset(1, 2).Resize(10).Formula = _
"=IF(" & strAddress & "="""","""",VLOOKUP(" & strAddress & ",'staff data '!$A:$AY,2,FALSE))"

rngInstantOrigin.Offset(1, 3).Resize(10).Formula = _
"=IF(" & strAddress & "="""","""",VLOOKUP(" & strAddress & ",'staff data '!$A:$AY,4,FALSE))"

rngInstantOrigin.Offset(1, 4).Resize(10).Formula = _
"=IF(" & strAddress & "="""","""",VLOOKUP(" & strAddress & ",'staff data '!$A:$AY,6,FALSE))"

rngInstantOrigin.Offset(1, 5).Resize(10).Formula = _
"=IF(" & strAddress & "="""","""",VLOOKUP(" & strAddress & ",'staff data '!$A:$AY,7,FALSE))"

rngInstantOrigin.Offset(1, 6).Resize(10).Formula = _
"=IF(" & strAddress & "="""","""",VLOOKUP(" & strAddress & ",'staff data '!$A:$AY,11,FALSE))"

rngInstantOrigin.Offset(1, 7).Resize(10).Formula = _
"=IF(" & strAddress & "="""","""",VLOOKUP(" & strAddress & ",'staff data '!$A:$AY,16,FALSE))"

rngInstantOrigin.Offset(1, 8).Resize(10).Formula = _
"=IF(" & strAddress & "="""","""",VLOOKUP(" & strAddress & ",'staff data '!$A:$AY,48,FALSE))"

rngInstantOrigin.Offset(1, 9).Resize(10).Formula = _
"=IF(" & strAddress & "="""","""",VLOOKUP(" & strAddress & ",'staff data '!$A:$AY,52,FALSE))"

End Sub

Function RangeFound(SearchRange As Range, _
Optional FindWhat As String = "*", _
Optional StartingAfter As Range, _
Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
Optional LookAtWholeOrPart As XlLookAt = xlPart, _
Optional SearchRowCol As XlSearchOrder = xlByRows, _
Optional SearchUpDn As XlSearchDirection = xlPrevious, _
Optional bMatchCase As Boolean = False) As Range

If StartingAfter Is Nothing Then
Set StartingAfter = SearchRange(1)
End If

Set RangeFound = SearchRange.Find(What:=FindWhat, _
After:=StartingAfter, _
LookIn:=LookAtTextOrFormula, _
LookAt:=LookAtWholeOrPart, _
SearchOrder:=SearchRowCol, _
SearchDirection:=SearchUpDn, _
MatchCase:=bMatchCase)
End Function

I hope this helps,

Mark

[lucas]

I would add another bit of information to help you understand vba.

In module1 of your example you have a bit of code that I don't think you could possibly be using as it is sheet change code and should be in a module for one of the worksheets in the workbook. I have no idea which one.

It operates when a change is made on that sheet and cannot be run from a standard module which is where it currently resides.

I also strongly suggest that you use option explicit at the top of every module to point out errors that might not otherwise seem apparent. Just good coding practice.


Not trying to overwhelm you and I hope You take the input in the spirit of learning.