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
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