Hamond
09-03-2009, 06:15 AM
Hello, I've created the basic structure below for a macro to copy information from mutiple sheets however I'm struggling with the rest and hope someone can help me out!
Basically I want to copy information from mutiple sheet in a workbook and summarise in a sheet called Summary. But there three aspects I am struggling with.
1) The line "Range("IV2").End(xlToLeft).Select" finds the last occurance of information that I want to copy but there are many others separated by blank columns before it it that I also want to extarct. So using the code above A02 finds the the last entry which I want to copy and paste into the summary sheet. But I need to go back to the same sheet to find the next populated column from the last cell copied in the sheet
2) I need to populate the sheet name from which each entry is extracted from. So in my current set up, column A in the summary sheet would contain the actual values and column B would contain the name of the sheet from which value is extracted
3) Don't know how to exlcude the summary sheet from the sheet loop. Only want to take information from the other sheets only!
I've attached a basic example with only three sheets to copy.
Sub LoopThroughSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Range("IV2").End(xlToLeft).Select
ActiveCell.Copy
Sheets("Summary").Select
Range("A1000").End(xlUp).Select
Selection.PasteSpecial Paste:=xlPasteValues
Next ws
End Sub
Hope someone can help.
Thanks,
Hamond
Basically I want to copy information from mutiple sheet in a workbook and summarise in a sheet called Summary. But there three aspects I am struggling with.
1) The line "Range("IV2").End(xlToLeft).Select" finds the last occurance of information that I want to copy but there are many others separated by blank columns before it it that I also want to extarct. So using the code above A02 finds the the last entry which I want to copy and paste into the summary sheet. But I need to go back to the same sheet to find the next populated column from the last cell copied in the sheet
2) I need to populate the sheet name from which each entry is extracted from. So in my current set up, column A in the summary sheet would contain the actual values and column B would contain the name of the sheet from which value is extracted
3) Don't know how to exlcude the summary sheet from the sheet loop. Only want to take information from the other sheets only!
I've attached a basic example with only three sheets to copy.
Sub LoopThroughSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Range("IV2").End(xlToLeft).Select
ActiveCell.Copy
Sheets("Summary").Select
Range("A1000").End(xlUp).Select
Selection.PasteSpecial Paste:=xlPasteValues
Next ws
End Sub
Hope someone can help.
Thanks,
Hamond