macro to import specific sheets with value in a file

A

aravindhan_31

Well-known Member
Joined
Apr 11, 2006
Messages
672
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi,

I need your help again, I have some 25 files in a folder like File1,File1,File3,File4 etc. each file has many sheets, I am trying to create a new master file from the sheets of these files.

I take some sheets from these 20 files and create my master file.
everytime I open each and every file move sheets to my master file then do a copy paste values.

Is there a way in macro where I will give all 20 file names with the path and the sheet names from those file on the code so each time I run the macro the sheets are imported to my master file.

I have attached a excel file with details.

Cross post: http://www.excelforum.com/excel-pro...ort-specific-sheets-with-value-in-a-file.html
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
Hi
Save the following codes in master file and save it inside the folder with 20+ files.Col B will have file names with .xls extension and col C -worksheet names to be copied.
Code: 
Sub aravin()
Dim e As Long
Dim f as string
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
    For e = 2 To Range("B65536").End(xlUp).Row
f= cells(e,3)
    Workbooks.Open Filename:=Cells(1, 2) & Cells(e, 2)
    Worksheets(f).Copy
    ActiveWorkbook.Close False
    Sheets.Add.Name = f
    Sheets(f).Range("A1").PasteSpecial
    Next e
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox "collating is complete."
End Sub
ravi
 
Upvote 0
Hi,

Thanks for your reply, however I am getting an error message saying

"Cannot rename a sheet to the same name as another sheet, a re4ferenced obuect library or a workbook referenced by visual basic"

regards
Arvind
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Macro to load 10 files from different folder into different sheets in Master File
Replies
2
Views
146
joxyin
J
D
  • Question
Retrieve values from workbooks saved in another drive
Replies
8
Views
245
mumps
mumps
K
  • Solved
VBA to copy column/data from two .csv files to one combined file.
Replies
6
Views
548
kbilly70
K
GMC The Macro Man
  • Solved
VBA Script - Return to named file
Replies
6
Views
171
mumps
mumps
M
  • Question
Macro - Merge all files into one sheet
Replies
2
Views
315
MountainFog
M

Forum statistics

Threads
1,215,261
Messages
6,123,939
Members
449,134
Latest member
NickWBA

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top