Excel Macro, merge sheets

0
votes

I have a excel file which contains 5 sheets in it. The 1st sheet contains hyperlinks form A1 to A4 cells with some names pointing to the respective 4 sheets in the workbook. I need to write vba function which should consist of a while loop, loop through each cell in sheet 1, then after reading 1st cell in sheet 1 it should go to the connected sheet copy all the data from it and put it in a different workbook and again go to next cell in sheet 1 copy the data and appened the data to the new workbook and repeat the process till it finds an empty cell. Please provide me a sample as i am new to vba programming. Thank you in advance

1
excelvba
Have you tried anything so far? Also, what would be an example of a link -- to a range of cells or..? – jonsca
A link would be pointing a specific cell say A2 in a sheet. – S..
There's actually an easy way for you to get the code for this. You can record the macro (while you are performing the actions), stop the recording and view the code. You'd just have to generalize the steps into your while loop. Give that a try and I'll help your through the trouble spots. – jonsca
@jonsca....thnx a ton....u made my life easy :) – S..
@S.. no problem :) I learned something about the hyperlinks. – jonsca

1 Answers

1
votes

For demonstrative purposes, since the OP had originally asked for a sample. This will take 4 cells defined in the first sheet of a workbook and collect selected cells' data from the second and third sheets and paste it into the 4th.

The first sheet:

     A
1  Sheet2!C8    (these are all hyperlinks)
2  Sheet2!G15 
3  Sheet3!C10
4  Sheet3!D13

The macro code:

Sub Test2()
    Dim cell As Range
    Dim sel As Range
    Dim index As Integer
    index = 1
    Set sel = Range(Cells(1, 1), Cells(1.1).End(xlDown))
    For Each cell In sel            
        cell.Hyperlinks(1).Follow 
        ActiveCell.Copy
        Sheets(4).Select
        Range("A" & index).PasteSpecial
        index = index + 1
    Next

End Sub

Obviously a lot of it hard-coded to this situation, and I'm sure there are more effective ways to do it.