Excel Macros help -

Belloni

Belloni

Belloni

Soldato
Joined
17 Feb 2009
Posts
3,001
Hi guys, need something doing for work.

Basically, we have a main spreadsheet where I want to collate data from 4 different work sheets we recieve from other countrys in the exact same format but with their relevant data on it.

I'll be putting their work into a shared drive, how do I create the macro, to read the shared drive, chose the excel file, copy the information needed and paste it into the main spread sheet?

Hope this makes sense!
 
Here's an example for you:-

On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
mydir = ("Directory to search")
.LookIn = mydir
.FileType = msoFileTypeExcelWorkbooks
'Optional filter with wildcard
.Filename = "*.xls"
If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
Source = wbResults.name
Destination = ThisWorkbook.name


Windows(Source).Activate
If ActiveSheet.name <> "Sheet Name" Then
Sheets("Sheet Name").Select
End If
lastrow = Range("A" & Rows.Count).End(xlUp).Row
If lastrow > 1 Then
Range("A2:IV" & lastrow).Select
Selection.Copy
Windows(Destination).Activate
If ActiveSheet.name <> "Sheet Name" Then
Sheets("Sheet Name").Select
End If
lastrow = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A" & lastrow).Select
ActiveSheet.Paste
End If

The important point is to use the lastrow count so that any pasted data goes underneath existing data. That way you can easily concatenate all spreadsheets.
 
The tables on each sheet will be static, so from cell A129 to I129 I need all the data within that copied to a master sheet.

Where abouts would I enter that into the code? I'm so new to this macro stuff :(
 
If you can send me some dummy data in trust, I'll write the code for you.

A really good way to learn macros is to run one and then do what you are planning, i.e. opening up another spreadsheet, copying then pasting the data back into your first spreadsheet.

If you then stop the macro, and go into VB (Alt & F11) the saved macro will have been added to a module, and you can view the code that has been created.
 
You must log in or register to reply here.
Back
Top Bottom