I have a problem here regarding a file at work and my VB knowledge is low. I have figured out the logic for it but I do not know how to code it in its entirety.

SHORT VERSION:
theres a detailed version with a few words at the bottom, but basically i'd like to know how to:
1. I need to know how to find the last row in a separate file
2. I need to know how to store the numerical value of that last row into a cell of my choosing
3. i need to know how to copy and entire row based on row conditions from one workbook to another workbook.
bonus:
4. ???
5. Profit

words
There are two different workbooks with one sheet in them each. Lets call them Samples and RefSamples. RefSamples contains data that was in Samples, but some is deleted, edited and changed. I would like to set up RefSamples to automatically update and copy the data that exists in Samples, but only starting from what hasn't been previously copied. I'm imagining doing this by storing the numerical value of the last point in a random cell(say CA5) in the RefSamp file (lets call it valStore), finding the last row(Lr) in the Samples file, and then setting it up to copy the entire row of all rows between valStore and Lr in Samples to RefSamp. Finally storing the last row back into the cell location CA5 in RefSamp to act as reference point to be used next time the program is opened. I have programmed before, but am not good at VB, so if anyone has any ideas on how to do that that would be great.

11/28/2007 11:10:15 AM

Don't use copy/paste methods. They will change your clipboard data, which is annoying, and they are slow, which is annoying.

Worksheets have a UsedRange (that might not be what it's called I haven't used Excel much lately) property. That will tell you the last row that contains any kind of information (including non-visible formatting stuff that may be residual from data being added then removed). Start there and loop backwards til you find a row that has non-empty cells.

fuck it, nevermind, this isn't worth my time. you don't know any VBA. I should have read the part where you don't know how to assign a value to a cell.

11/28/2007 12:09:25 PM

i will look into your bullet points in a minute, but from doing a quick reading of your "words", I would suggest looking into using "Named Ranges". Named Ranges are a way to give a cell or range of cells a name. it's available in normal excel through Insert > Name > Define. The nice thing is once you have a named range in a worksheet, it is also easily accessible and manipulatable in VBA.

so, for example, instead of storing the row number of the last copied row in Samples in a cell somewhere, your VBA would assign a Named Range to the last copied row (or the first cell in that row) instead, like "LastCopiedRow". So next time you wanted to do a copy, VBA would look for LastCopiedRow, and start copying from (pseudocode) Row("LastCopiedRow)+1

11/28/2007 12:12:35 PM

Quote :

"Named Ranges are a way to give a cell or range of cells a name. it's available in normal excel through Insert > Name > Define. The nice thing is once you have a named range in a worksheet, it is also easily accessible and manipulatable in VBA."

In the god damn toolbar where it displays the cell's address, you can directly type the range name

[Edited on November 28, 2007 at 12:29 PM. Reason : easily done in VBA, but i think he needs to go another route since he doesn't know what he's doing]

11/28/2007 12:28:14 PM

jesus f'ing christ..... i use Named Ranges every day, and I alway hated how cumbersome it was to define ranges by going through that dialog and using that stupid Define window. I've always used that box in the toolbar as a read-only box to either take me to a range or show me the name of some highlighted cells. I've never bothered to actually type in it.....

11/28/2007 12:32:32 PM

Limpy, when I said that I didn't know any VBA, that wasn't entirely true. I've written a few things in VBA and for what i'm doing now i've written subs to delete rows data that doesn't meet criteria, save a copy of the file with a time/date stamp to the FTP upon close, etc... I don't know a lot about VBA would have been more accurate.

As for the assign a cell value, I don't know how to do it when its coming from another workbook. I can do it within the same workbook, but when i try to store from one work book to another I get errors out the ass. Thats the bulk of the trouble, how to code from one workbook to another. Thats what i'm really hear to learn. Thanks.

11/28/2007 12:45:22 PM

you have to create Workbook and Worksheet objects, like:

Dim TempWorkbook As Workbook
Dim ResultsWorkbook As Workbook

then you can manipulate those workbooks like normal objects (i'm just pasting some random VBA code that was hacked together by various people at my job as examples of things you can do with workbooks and worksheets)

open and activate workbook

    Workbooks.Open (ResultsFileName)
    Set ResultsWorkbook = ActiveWorkbook

    TempWorkbook.Activate
    TempWorkbook.ActiveSheet.Move After:=Workbooks(ResultsShortFileName + ".xls").Sheets(FlowCounter + 1)
    Set FlowResultsSheet = ResultsWorkbook.Sheets(FlowCounter + 2)
        

    ' Format summary sheet
    SummarySheet.Activate
    SummarySheet.Columns("A:A").ColumnWidth = 10
    SummarySheet.Columns("B:F").EntireColumn.AutoFit
    SummarySheet.Range(Cells(1, 1), Cells(SummarySheet.UsedRange.Rows.Count, SummarySheet.UsedRange.Columns.Count)).Select
    Selection.HorizontalAlignment = xlLeft
    Range("A2").Select

    ResultsWorkbook.Save
    ' disable security alerts for "OUT" files
    Application.DisplayAlerts = False
    ActiveWindow.Close
    ActiveWindow.Close
    ActiveWindow.Close
    Kill WorkbookNamePrgOut + ".xls"

11/28/2007 1:48:20 PM

actually, some of that helps me out a little. Thanks!

11/28/2007 3:39:33 PM

Find some free scripts online and just see how they do stuff. Then learn from that.

That's how I got the basics down, and for the quirky stuff, someone like LimpyNuts can help you

11/28/2007 5:12:56 PM

Quote :

" Workbooks.Open (ResultsFileName) Set ResultsWorkbook = ActiveWorkbook"

Set ResultsWorkbook = Workbooks.Open(filename)

Quote :

"SummarySheet.Range(Cells(1, 1), Cells(SummarySheet.UsedRange.Rows.Count, SummarySheet.UsedRange.Columns.Count)).Select Selection.HorizontalAlignment = xlLeft"

SummarySheet.UsedRange.HorizontalAlignment = xlLeft

Quote :

"then close ("Kill") the workbook"

Kill deletes a file, genius. Workbooks have a close method. It will close the workbook and any associated windows (yes you can have hundreds of windows open with the same workbook displayed in each).

Never use ActiveWindow, ActiveSheet, ActiveWorkbook, or Selection for autonomous code unless they are inputs (i.e. a macro that does something based on what the user selects or activates). That said, don't go around selecting shit or copying and pasting. If the user interacts with excel or uses the clipboard, the whole process will be fucked, probably beyond repair. The user WILL click something while this is running and you will be hosed.


Don't trust ^^^ that guy. Looks like he's copying/pasting other peoples' code who don't know what the hell they're doing.

11/29/2007 6:31:39 PM

Quote :

"Don't trust ^^^ that guy. Looks like he's copying/pasting other peoples' code who don't know what the hell they're doing."

that's exactly what I'm doing. the code fragments there work every time for the specific task at hand that I do daily. It is, 100% admittedly, horrible code, non-flexible, blah blah blah. But for what I need to do, it works. And whenever I need to hack something else into it, I just hit Macro Record and see what is generated, then shape that code into doing what I want it to do. Point was to show him some some commands or objects that are available, however inefficiently or poorly my code is using them

and yeah, i forgot that particular Kill command actually deletes a temporary file that is used here.....

11/29/2007 6:44:35 PM

and - since copying/pasting between worksheets/books is subject to nasty side-effects, what do you suggest is the best way to move or duplicate data between worksheets? Should you read it into VBA datastructures and spit it back out elsewhere?

11/29/2007 6:50:18 PM

^See the FormattedText property

My bad, FormattedText is what you use in Word. In Excel:

MySheet.Range("A1").Copy Range("B1")

Will duplicate cell A1 into B1 without using the clipboard

[Edited on November 29, 2007 at 7:59 PM. Reason : not enough sleep]

11/29/2007 7:41:25 PM