[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 509
  • Last Modified:

combine several workbooks and all sheets into a new workbook with one sheet

excel 2010/2003
vba routine needed

access 2010
vba code needed
excel /2003/2010

I need to loop through each workbook and each worksheet(in each workbook) in the 4 subfolders. and in the data range specified below.

I have 2 columns in the new worksheet that have to be updated also.

Supervisor = the name of the each subfolder Column A
Associate = the name of the excel file  Column U



main path
C:\LOG\

SUB FOLDER NAMES:(supervisor names)

W_Team_Werve
SE_Team_West
NE_Team_Theodorovich
MW_Team_Cavalier

SEVERAL WORKBOOKS IN EACH FOLDER(associate names)
usually like this format:
W - SOTELO Louis - Productivity Log 2012.xls
MW - hull bill - Productivity Log 2012.xls


DATA RANGE: for each worksheet
B2:T32

I only need the data in this cell range.
to go into new worksheet.

however, in the new worksheet
column A  = supervisor(FOLDER ANME)
column U =  Associate (WORKSHEETNAME)


Thanks
fordraiders
0
Fordraiders
Asked:
Fordraiders
  • 6
  • 4
1 Solution
 
SteveCommented:
OK, example workbooks please (dummy data is fine)..

Couple of the starting books, and mock up of desired result.
May be possible to do this in MS Query, or full on code.
Will need examples to determine best method.
0
 
FordraidersAuthor Commented:
barman
here r 3 wrokbooks
EXAMPLE-FILES.zip
0
 
FordraidersAuthor Commented:
EXAMPLE OUTPUT WORKBOOK
USING FOLDER NAME
(ASSUMING ALL FILES WERE IN THIS FOLDER)
W_Team_Werve

OUTPUT NEEDS TO BE IN   .XLSX  PLEASE

I ONLY SHOW 2 SHEETS OF DATA BUT, ALL SHEETS NEED TO BE ADDED TO FINAL OUTPUT
again,
the only thing /range i'm looking for is:

DATA RANGE: for each worksheet
B2:T32

I only need the data in this cell range.
to go into new worksheet.

however, in the new worksheet
column A  = supervisor(FOLDER ANME)
column U =  Associate (WORKSHEETNAME)


Thanks so much for looking at this please
EXAMPLE-OUTPUT-EE.xlsx
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
FordraidersAuthor Commented:
barman ? any luck
0
 
SteveCommented:
sorry, been a tad busy recently, will take a look this week.
0
 
FordraidersAuthor Commented:
ok Thanks
0
 
SteveCommented:
OK, so all sheets? do they have to be ordered / sorted?
Should we be skipping blanks?
Are you too fussed about Formatting?
Output needs to be .xlsx... so a seperate file to the one with the macro?
0
 
SteveCommented:
OK, attached is a "rough and ready" bit of code in a file.
It will create a file called 'output' upon running the macro.
Take a look and see if this does the job.
If it does, comments are welcome, I can then tidy it up.
DoMacro.xlsm
0
 
FordraidersAuthor Commented:
ok...doin ok except.
1. Leaves every workbook open it gets data from

so changed
wbF.Close    to
wbF.Close False

and it worked fine...
Thanks so much !!!!!!
0
 
FordraidersAuthor Commented:
beautiful Thanks !
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now