Copy data from one workbook to other.

[Mandar Khedekar]

Write macro to copy data from Source File to assignment-2 file. Make sure you take care of following conditions

• Book with same name should not added to the destination, instead only its other fields should be updated

• Leave the Author column from source file; need not have to be copied.

• new book record found in the source file needs to be added at the last available row in Destination file.

• after copy of data, if I click on “Refresh Graph data buttons” then it should refresh data considering additional records.


please note:


Assignment2 file has table with three 4 columns Book name, Author, Number of copies, price.

 

[Hui]

Mandar


Is this for a Uni/School assignment ?


I have a moral problem doing people's homework, as the whole idea of School assignments is that you are meant to be learning to research and solve new problems.

If you ask about how to use a specific function that is different.


You will find a lot of similar posts here and I would suggest using the Google Custom Search box at the top right of the page

 

[Mandar Khedekar]

Hi Hui,


Thanks for reply sir, the above mention problem is just a smaller part and its not an school assignment. Let tell you what I had done till now.


Dim mychart As ChartObject

Dim wbk1 As Workbook


Public Sub Chartdisplay()


Set mychart = Worksheets("Sheet1").ChartObjects(1)


With mychart.Chart.Axes(xlCategory)

.HasTitle = True

.AxisTitle.Caption = "Book Name"


End With


End Sub


Public Sub button1_click()


Call Chartdisplay

Call refreshchart1

End Sub


Public Sub button2_click()


Call Chartdisplay

Call refreshchart2


End Sub


Public Sub CopyData_Click()

'Dim iLastRow As Integer

'Dim rng As Range

Dim filter

Dim file1 As Variant

Dim r As Variant


filter = "XLS Files (*.xls;*.xlsm;*.xlsx), *.XLS," & _

"All Files (*.*), *.*,"

Title = "Select source file"

file1 = Application.GetOpenFilename(filter, , Title)


Application.StatusBar = "Opening Soruce File"


'file1 = "C:Documents and SettingsAdministratorDesktopSource_File.xlsx"


Set wbk1 = Workbooks.Open(file1)


'iLastRow = Cells(Rows.Count, "C").End(xlUp).Row

'For Each rng In Range("C4:C" & iLastRow)

' If Not rng.Value = vbNullString Then

' rng.Select


' End If

' Next rng


With wbk1.Sheets("Sheet1")

Range("D4").Parse

Range("C4:C13, E4:E13, F4:F13").Copy

End With


ThisWorkbook.Activate


With ThisWorkbook.Sheets("Sheet1")


Range("D17").PasteSpecial Paste:=xlAll


End With


wbk1.Close (savechanges = False)


Call Chartdisplay

Call refreshchart1

Call refreshchart2


End Sub


Public Sub refreshchart1()


With mychart.Chart.Axes(xlValue)

.HasTitle = True

.AxisTitle.Caption = "Number of Copies"


End With


With mychart

.Chart.SetSourceData Source:=Range("E7:E24")


End With


End Sub


Public Sub refreshchart2()


With mychart.Chart.Axes(xlValue)

.HasTitle = True

.AxisTitle.Caption = "Price"


End With


With mychart

.Chart.SetSourceData Source:=Range("F7:F24")


End With


End Sub


Apologies for not providing correct informationa and asking question in an improper manner.