How to transfer information from a workbook to another Excel 2003

[Excelnoub]

I have some code running in my main Workbook. In my last sheet, called "Archives" is information that is dead, meaning, its information that will not be used but necessary.


I would love to modify my transfer vb code that I have right now to open a new workbook that I have created called “Archives” and transfer my information to this workbook instead.


Example:


Main workbook sheet 5 Called “Reports”. In this sheet I have to following code:


‘If Not Intersect(Target, Range("X:X")) Is Nothing And Target.Cells.Count = 1 Then

Application.EnableEvents = False


If LCase(Trim(Target.Value)) = "yes" Then

Msg = MsgBox("Contract completed. Send it to Archives?", vbYesNo + vbQuestion + vbSystemModal, "Excel Report")

If Msg = vbYes Then

With Range("A" & Target.Row)

Sheets("Archives").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(, 18).Value = .Resize(, 18).Value

.Resize(, 24).ClearContents

End With

Else

Cells(TRow, TCol) = ""

End If

End If’


Now instead of sending it to the same workbook I need to scrap Sheet “Archives” to minimize the quantity of information within this workbook. So I figured, if I have a new workbook called “Archives” in my C: Document, then when I select “yes” from the above code, it will first open the new “Archives” workbook, then transfer the information in the next new available row, then close the “Archives” workbook.


Is this possible and could anyone help me with this?

 

[kaushik03]

Hi Excelnoub,


Assumption:


1)I assume,your parent workbook's name is : "MyData.xlsm"

2)And your New workbook's name is : "Archives.xlsx" (Manually create a new workbook and save it in your desktop and close it)

3)Data will be copied from sheet1 of "MyData.xlsm" to sheet1 of "Archives.xlsx"


The below code will do the following( write the code in your parent workbook from where you want to copy data to a new workbook):


1)If you hit "Yes" from messagebox:


i)A new workbook ("Archives.xlsx") will open

ii)Copy the date from your parent workbook and paste it from the last available row of the new workbook, then save and close it.


2)If you hit "No" from messagebox, then nothing will happen.


The code is here:


Sub CopyData()


Msg = MsgBox("Contract completed. Send it to Archives?", vbYesNo + vbQuestion + vbSystemModal, "Excel Report")


If Msg = vbYes Then


Path = ThisWorkbook.Path & ""


Application.Workbooks.Open (Path & "Archives.xlsx")


'Workbooks("Archives.xlsx").Activate


LstRowNew = Workbooks("Archives.xlsx").Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1


Workbooks("MyData.xlsm").Activate


LstRowOld = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row


Range("A2:F" & (LstRowOld)).Copy


Workbooks("Archives.xlsx").Activate


Worksheets("Sheet1").Range("A" & (LstRowNew)).Select


ActiveSheet.Paste


ActiveWorkbook.Save

ActiveWorkbook.Close


Workbooks("MyData.xlsm").Activate


Worksheets("Sheet1").Range("A1").Select


Else: Exit Sub


End If


End Sub


Ps: Feel free to make changes in the sheet name according to your workbook's sheet.


Let us know if this is fine.


Kaushik

 

[kaushik03]

I forgot to mention one thing. I have created that workbook (archives) in 2007. If u want it for 2003, it will be "Archives.xls"