• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Issue Management

Gulshan Hinduja

New Member
An excel sheet has list of issues as a line item in “DATA.xls”. In the column (1) has the unique ID for each issue (line) with a history workbook hypelinked to it.

Each history workbook is identical in all respect except the information in it. The history workbook will be updated by respective resources.


I want to update the information (a range of cells) from the “n” number of history workbooks into the “DATA.xls” sheet automatically (with the help of macro). The incomplete macro is as under:


Sub Macro ()

‘Activate DATA.xls

Sheets(1).Activate

‘Activate the range – column in which the file name of the history sheet is placed

‘ Here only one row has been taken, therefore only one cell has been taken as range

Range("A8").Select

Application.Workbooks.Open ("C:Issues" & "" & Sheets(1).Cells(ActiveCell.Row, 1).Value & ".xls")

‘The range in the history workbook which need to be copy-pasted in the DATA workbook

Range("AA2:AZ2").Select

Selection.Copy

‘History workbook is minimised

ActiveWindow.WindowState = xlMinimized

‘Select the range where the information from the history workbook to be pasted in the DATA workbook

Sheets(1).Cells(ActiveCell.Row, 27).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

End Sub


What I want is:

The macro loops through all the rows,

• opens each history workbook from the unique ID from the column 1 in the row,

• copy a range from the history workbook (AA1:AZ1) and paste it against the unique ID in the specified columns (AA:AZ)

• close the history workbook

Loop till all the rows have been updated
 
Hi Gulshan,


Use a Do While loop to work through all the rows in Data.xls.


I guess that you have the name of the history workbook on the same row, get the workbook and then open, copy paste and close as required.


kanti
 
Sub UpdateDATA2()

Do

Sheets(1).Activate

Range("A65536").End(xlUp).Select

If Cells(ActiveCell.Row + 1, 1) > 0 Then

Application.Workbooks.Open ("C:Issue folder" & "" & Sheets(1).Cells(ActiveCell.Row + 1, 1).Value & ".xls")

Range("AA2:AZ2").Select

Selection.Copy

ActiveWorkbook.Close SaveChanges:=True

Application.Workbooks.Open ("C:IssueDATA.xls")

Sheets(1).Cells(ActiveCell.Row + 1, 27).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

End If

Loop While Cells(ActiveCell.Row, 1) = "Stop"

End Sub

'nothing happends
 
Dear Kanti,

you are right the column A gives the name of the history work book corresponding to the row.

Please have a look at the macro above, where i have tried to use the Do-loop while but no result can be seen. The difference in this macro and the macro at the initial is that in the initial macro i minimise the history workbook but in the subsequent macro i close the history workbook (may be because i have closed the workbook before pasting in DATA workbook)

Can you please see what could have gone wrong.

Regards

Gulshan
 
Back
Top