• 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


  • 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


‘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


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



‘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


Active Member
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.


Gulshan Hinduja

New Member
Sub UpdateDATA2()




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

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



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

Gulshan Hinduja

New Member
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.