Obtaining data from closed workbooks

[shakeregg]

Hey all,

For some reason this is one that I'm really struggling with and I can't seem to get it right.

I have a main workbook (Main) which contains a worksheet called "Main Sheet". I also have two other workbooks called Sales1 (worksheet is "Sheet1") and Sales2 (worksheet is "Sheet2").

Both Sales1 (sheet1) and Sales2 (Sheet2) have text A2:C100.

What I'm trying to do is to find a Macro that is contained within the main workbook ("Main") and when activated obtains the data from Sales1 (sheet1) and Sales2 (sheet2) which are closed and pastes them into "Main" (Main Sheet) with one set of data going in A2:C100 and another in D2:F100.

If anyone can help it will be appreciated!

 

[Trevor G]

Have you tried recording a macro to open the workbooks and copy the data and then close and repeat for the second book and then stop recording that will give you a method.

 

[shakeregg]

Yeah I tried that but when I run the macro it seems quite messy as it actively opens up the workbooks and shows the process in action. Is there a macro that wouldn't show this process happening?

 

[Trevor G]

You can add to the code to stop seeing the updates, this example switches the application updating, warnings and auto calculation off at the beginning of the code , and then switches them all back once the code has run, it should also make the code run faster:

Sub clearUp()
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With
'Rest of your code goes here
With Application
.ScreenUpdating = True
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
End With
End Sub

 

[shakeregg]

Works a treat............. Cheers Trevor. Really appreciate it mate!!

 

[Trevor G]

Happy to help and thanks for letting me know it helped.