Help to loop a task for copying and pasting data from multiple workbooks

[snowcrash79]

Hi All,

I have created the below macro using macro recorder, and need some help to loop the task until now more links in column C.

Task:
Open up a workbook via a hyperlink in column "C" on a master file, in the newly opened workbook copy a row of data in a hidden sheet and paste this data into the master file on the same row as the hyperlink, then close the file. and repeat this for the next hyperlink in the column until no more hyperlinks


c d e f g
4 hyperlink1.xlsm copied data from hyperlink1.xlsm copied data from hyperlink1.xlsm copied data from hyperlink1.xlsm copied data from hyperlink1.xlsm
5 hyperlink2.xlsm copied data from hyperlink2.xlsm copied data from hyperlink2.xlsm copied data from hyperlink2.xlsm copied data from hyperlink2.xlsm
6 hyperlink3.xlsm copied data from hyperlink3.xlsm copied data from hyperlink3.xlsm copied data from hyperlink3.xlsm copied data from hyperlink3.xlsm





using macro recorder


Sub Consolidation()


Application.ScreenUpdating = False
Application.DisplayAlerts = False

Windows("Masterfile.xlsm").Activate
Range("C4").Hyperlinks(1).Follow


Windows("hyperlink1.xlsm").Visible = True
Sheets("hiddensheet").Visible = True
Sheets("hiddensheet").Select
Range("B4:E4").Select
Selection.Copy
Windows("Masterfile.xlsm").Activate
Range("D4").Select
ActiveSheet.Paste
Windows("hyperlink1.xlsm").Activate
Sheets("hiddensheet").Select
ActiveWindow.SelectedSheets.Visible = False
ActiveWindow.Close savechanges:=False

Windows("Masterfile.xlsm").Activate


Application.DisplayAlerts = False
Application.ScreenUpdating = True

End Sub

[PFDave]

Untested but this shouldn't be far off.

Question really is where your data starts (I've assumed row 4) and if you data in hiddensheet is always the same range to be copied

Please Login or Register  to view this content.

[snowcrash79]

works great thankyou.

I still have one complication.
The "DisplayAlerts = false" does not prevent the microsoft prompt "some files can contain viruses.................. Would you like to open this file?"

I have tired adding the site to trusted locations, searching to change the trust settings some are restricted to me on this computer. and i can't bypass it, meaning instead of letting the macro run, i would have to click during each loop when the prompt comes up. Is there another way to bypass this, or a code that would select ok when the prompt appears?

[sintek]

Hi snowcrash79

Welcome to the Forum.
In future please view the forum rules before posting. Your post does not comply with rule #3.

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing.

So, do the following:
Edit your post, highlight your code and click the [#] button at the top of the post window.

[PFDave]

works great thankyou.

I still have one complication.
The "DisplayAlerts = false" does not prevent the microsoft prompt "some files can contain viruses.................. Would you like to open this file?"

I have tired adding the site to trusted locations, searching to change the trust settings some are restricted to me on this computer. and i can't bypass it, meaning instead of letting the macro run, i would have to click during each loop when the prompt comes up. Is there another way to bypass this, or a code that would select ok when the prompt appears?

https://support.microsoft.com/en-gb/...007-office-pro