Appendind data from one sheet to another workbook sheet?

[Pozzi]

Hi,

Is the following straight forward to do, as I'm having trouble getting anything to do this to work...

I have a weeks data placed on sheet A from a query against a database, this can be re-run when ever the user wants, and will probably be daily. Once the data has been placed and formatted on sheet A I want to open the achive sheet which is within another xls file.

On the archive sheet I want to place just the raw data (values) using copy and paste. The problem is if I just copy and paste the rows everytime the archive sheet will contain duplication of previous days.

The first column of the data on sheet A is the date, as is the same with the archive. I can get the last date in the archive sheet, and using this thought I would carry out a VLookup against the range of data on sheet A. Once I know which date hasn't been pasted onto the archive sheet I'd copy from that point on sheet A and paste the data onto the archive sheet, a way of appending I suppose.

The part I'm realy falling over is the VLookup from within sheet A, which contains the VBA code.

If anyone has any suggestions on the best way to do the above, or could give me an example of doing the above it'd be much, much appreciated.

Regards

[Webtest]

I would suggest that you highlight the relevant area, including headers, on both sheets. You can then cut and paste them into NotePad, and then post that data as thought it were code (to preserve the formatting). I have a hard time visualizing what you are trying to do with the VLOOKUP ... fetching data from one column based on the values in another column.

What you want to do shouldn't be too difficult ... all you are really doing is searching a column and comparing it with the Max value from another column to decide what to copy (presuming that all the data is sorted by date!). If the Max value for the date on the source sheet is greater than the Max value on the destination sheet, then you have something to copy. You just have to find the range of rows on the source that are greater than the current Max on the destination.

Good Learning and Good Programming!

[Webtest]

Is there only one row per date or possibly multiple rows per date? (in the destination sheet) That makes a big difference, and my previous reply might have been far too simplistic.

[Pozzi]

Hi,

Thanks for the reply and suggestion although I'd like to avoid having to use notepad.

The criteria for the VLookup will be quite easy to get, I'll just open the archive sheet, place myself in the first cell and do a 'End(xlDown)'

strLastDate = .Range("A1").End(xlDown).Text

This will give me the last date on the archive. Then use this to carry out the VLookup on my original sheet, once found select the range from that point on.

Just as a note I'd only ever have a max of 7 rows of data on sheet A, as every time the query is run I clear the sheet first and the query 'From' is the start of the week which is selected from a combo box.

It's the VLookup that's giving me the problem, this is what I have so far. It's not complete, selecting the correct range once the date has been found still needs to be added: -

Sub ArchiveData()
    '* Purpose  :   Archive data for use by other sheets.
    
    Dim wbkDataArchive As Workbook
    
    Dim strLastDate As String
    Dim strDate As String
    
    Dim lngRow As Long
    
    Range(Cells(10, 1), Cells(16, 21)).Cells.Copy
    
    Set wbkDataArchive = Workbooks.Open("C:\Weekly_Daily_Data_Archive.xls")
    
    With wbkDataArchive.Sheets("Sheet1")
        '* Ensure we're in first cell.
        .Cells(1, 1).Select
        '* Check to see if data already in sheet.
        If IsEmpty(.Range("A1")) Then
            '* No data found paste onto sheet.
            .Range(.Cells(1, 1), .Cells(7, 21)).PasteSpecial Paste:=xlValues
        Else
            '* Data on sheet, get last date in sheet.
            strLastDate = .Range("A1").End(xlDown).Text
            '* Lookup date on current sheet.
            strDate = .WorksheetFunction.VLookup(strLastDate, .Range(.Cells(1, 1), .Cells(.Range("A1").End(xlDown), 1)), 1)
            '...
            '* More to go in once VLookup is working...
            
            .Range("A1").End(xlDown).Offset(1, 0).Select
            lngRow = ActiveCell.Row
            .Range(.Cells(lngRow, 1), .Cells(lngRow + 6, 21)).PasteSpecial Paste:=xlValues
        End If
    End With
    
    Application.CutCopyMode = False
    
    wbkDataArchive.Close True
 
End Sub

There is currently an error on the VLookup, which is due to me using the 'With/End With' when referencing the archive sheet and the VLookup I want on my original sheet/workbook which is where the VBA code is run from.

On both sheets the rows are in date order, and only one row per date.

Regards

[Webtest]

Pozzi ...

You can create a handle for the source worksheet ... BEFORE you open the Archive Workbook, which makes the Archive workbook the active workbook ...

Dim wbkSource As Workbook
Set wbkSource = ActiveWorkbook

This is just a guess ...

.Range(.Cells(1, 1),   becomes wbkSource.Range(wbkSource.Cells(1,1)  etc.

I'm doing this blind, so good luck!