automatic update of data in other spreadsheet

plz help me out

i want to automatically update data from one spreadsheet to ther one
i am novice to vb programming


here are two file plzzz help me out --sheet 1--sheet(data of sheet one to be copied into)
and is there any way that the data copied also gets saved the in that spreadsheet as next time data in sheet 1 is updated!!


thnxx

LOL...you posted two empty workbooks. That's pretty funny...

Ok, instead of dumbing it down, post up an ACTUAL workbook or two showing us examples of what you have in mind.

In the meantime, if you're talking about just simple cross-referencing...in 2.xls put this formula in Sheet1 cell A1:

=[1.xls]Sheet1!A1

Then copy that cell down and over as much as you want. This sheet will now show whatever is on sheet1 of 1.xls.

So, you want to press a button or something and all the data in column D goes over to the "copied data.xls" spreadsheet into the next empty column, starting at D? And then the date is inserted in row 2?

The main sheet has a button, just click on it to run the ARCHIVE DATA macro.

The macro will check to see if "Copied Data.xls" is open or not, it will open it if necessary. It will then paste the data into that sheet in the next open column and insert a date at the top of the column.

Option Explicit

Sub ArchiveData()
Dim NextCol As Long
'Check if destination workbook is open already, open it if necessary
    On Error Resume Next
    Workbooks("copied data.xls").Activate
    If Err <> 0 Then Workbooks.Open "Copied Data.xls"
    
'Copy data
    NextCol = Workbooks("copied data.xls").Sheets("Sheet1").Range("B12").Value
    Windows("nirulas vikas puri.xls").Activate
    Columns("D:D").Copy

'Paste data to new column and insert archive date
    Workbooks("copied data.xls").Sheets("Sheet1").Activate
    Columns(NextCol).PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    Cells(2, NextCol) = Date
    Columns(NextCol).HorizontalAlignment = xlCenter
    Columns(NextCol).AutoFit
    
End Sub

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

hey man there are three files

like u did in first file nirulas one a button to copy data

can u place two buttons on file

and i want the d row to be copied to next file copied one after leaving 2 columns
ex d copied to other file in (column 6 ,nxt time to get pasted in column 9)

in nirulas file only at end the row 138 cells (b,c,d,e,f,G,h) to be copied on click of a button to another file called fill rate with date on top

plzzz help me out its urgent!
thnxx
anujj

Originally Posted by anuj_sethi1043

can u place two buttons on file

Yes, but you don't indicate what this second button does...

and i want the D row to be copied to next file copied one after leaving 2 columns
ex D copied to other file in (column 6 ,nxt time to get pasted in column 9)

On the COPIED DATA.XLS sheet, change the formula in B12 to this to get that behavior:
=MATCH(LOOKUP(9.99999999999999E+307,2:2),2:2,0)+3

in nirulas file only at end the row 138 cells (b,c,d,e,f,G,h) to be copied on click of a button to another file called fill rate with date on top

Ok, here's the macro:

Sub AutoFillRate()
Dim NextRow As Long
'Check if destination workbook is open already, open it if necessary
    On Error Resume Next
    Workbooks("fill rate.xlsx").Activate
    If Err <> 0 Then Workbooks.Open "fill rate.xlsx"

'Copy data
    NextRow = Range("B" & Rows.Count).End(xlUp).Row + 1
    Cells(NextRow, "A").Value = Workbooks("nirulas vikas puri.xls").Sheets("Sheet1").Range("B132").Value
    Cells(NextRow, "B").Value = Workbooks("nirulas vikas puri.xls").Sheets("Sheet1").Range("D132").Value
    Cells(NextRow, "C").Value = Workbooks("nirulas vikas puri.xls").Sheets("Sheet1").Range("E132").Value
    Cells(NextRow, "E").Value = Workbooks("nirulas vikas puri.xls").Sheets("Sheet1").Range("F132").Value

End Sub

Add that to your sheet and add a Forms Button to attach it to.

punna111,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.