I have approx. 35 workbooks similar to the attached in a single directory.
Each workbook has 3 tabs named Help, Example and Template.
I need to copy the 36 Template tabs into one new workbook.
I would also like to rename each tab in the new workbook based on the text in cell A1 of the originating Template tab.
I have approx. 35 workbooks similar to the attached in a single directory. Each workbook has 3 tabs named Help, Example and Template. I need to copy the 36 Template tabs into one new workbook. I would also like to rename each tab in the new workbook based on the text in cell A1 of the originating Template tab. M
View 6 Replies View RelatedI have multiple excel files from which I would like to copy specific ranges to a master file. The row should add up to the previous rows. From this side I have no problems things work fine. The problem is to copy the header which remains the same data to the top of the master file. Currently the macro I use overwrite the first line of the master file. So I loose the date from one of the excel files.
View 3 Replies View RelatedThat does allow me to filter the output in Master, but ideally what I would want to do is not have to set aside 300 lines in the Master file for each of the Staff files. Conceptually, I'd like to have the spreadsheets Staff A, Staff B, etc. look for and export only lines which have data in them and then have Master bring those lines in automatically. This may not be possible, in which case your suggested solution is the best approach.
View 9 Replies View RelatedI have a typical scenario. I have a sheet in WB1 as Sheet 1 and I need to copy this sheet into multiple workbooks in a folder on the desktop and save the files too.
Is there a macro that would resolve this?
I have a constraint here, the macro should not be activated in any of the workbooks as I would using a tool which has VB as the backend and there would be a conflict when executing.
found the following code which works for 1 workbook at a time. I am trying to save 7 workbooks at 1 time. Is it possible?
Sub SaveAsCell()
Dim strName As String
On Error Goto InvalidName
strName = Sheet1. Range("V77")
ActiveWorkbook.SaveAs strName
It will work on the first sheet but none after that. I need to have each workbook saved with the value in cell V77. Also if that is possible, is it possible to change where the file is saved as well?
Right now I have a master workbook that will open the 7 other workbooks, paste data onto several pages in each workbook. I would like the macro to save the workbooks. The workbooks are named: 02 Tuesday, 03 Wednesday, 04 Thursday, 05 Friday, 06 Saturday, 07 Sunday, 08 Monday.
I have an attendance file w/least 30 sheets. 29 of the sheets are for each separate group. The other sheet is the main one where I need to collect information like "total participants in attendance" and "total members in group" Then I divide those two and get the percentage of attendance.
The sheets containing the individual groups info are set up like this... (1=they were in attendance)
GROUP 1
Name / January / February / March/
Jess________1________0________1
Ryan_______1________1________0
Joe ________1________0________0
----------------------------------
total P ____3________1_________1
members___3________3_________3
% _______100_______33________33
The main sheet looks like this
Group / Jan. Participation / Jan. Member total/
Grp 1 ________3_____________3
Grp 2 ________8_____________10
Grp 3 ________7_____________10
---------------------------------------------
_____________18____________23
% total-__________________78.2%
I know I can manually go through and link the sums of participation and total group size into the main sheet, but I have a lot of workgroups and need to do this every month, is there an easier way? I am willing to change the set up of the sheets.
I need a macro that would consolidate all data in multiple worksheets of multiple workbooks in one Master file.
All the workbooks will be in one particular folder. The macro should search for data in all the workbooks and consolidate it in one master excel workbook.
I am currently using both excel 2007 and excel 2010. This macro would really reduce manual work as currently consolidating data from 45 to 50 sheets takes an ample amount of time...
The following code won't let me copy from the first workbook. I get a run time 1004 error stating "That command cannot be used on multiple selections".
I would rather not have to copy this by column for each of the 4 workbooks
[Code].....
I want create a macro which will extract 6 values (see below) from a workbook tab called summary to an master workbook for reporting purposes. Each workbook has a unique file name e,g ACI1150.
Values on sheet SUMMARY:
A1
A2
A3
G21
G24
G26
I tried to adapt the below to get one item copied/extracted. However it would no work.
I am new to using macros
Sub GetG26s()
Dim MyDir As String, FN As String, SN As String, NR As Long
Application.ScreenUpdating = False
I have about 20 workbooks with different file names for different projects all saved in the same folder. Each workbook has about 10 worksheets and each worksheet is named in a similar fashion in each of the 20 workbooks (eg. revenue, cost, variance etc.). I want to pull out a worksheet named ' forecast' from each workbook into a master workbook so that the master workbook would contain the 20 forecast worksheets.
View 9 Replies View RelatedI've got folder of 44 files - all copies of each other apart from the data entered in the cells - which have 7 worksheets in them all named differently (Each one is a different day of the week - this is the work of someone else that I have to work on !).
I want to copy the data in the columns A-R of each worksheet from row 2 to the last one with data in column M into a single file so that I can then then work on all that data in a single file..
I have 400+ workbooks in a folder. If they are all .xlsm (macro-enabled) workbooks, how can I import a macro (.cls file) to a particular sheet (sheet1) in each workbook using VBA so that when a user on another machine opens one of them, the macro works? Right now, I have this code to loop through all workbooks; I only need the import function to go in the middle. Copying and pasting or importing manually 400+ times is just plain unrealistic!
VB:
Sub OpenfileUpdate()
Dim strFile As String
mFolder = "C:files1Files"
strFile = Dir(mFolder & "*.xlsm*")
Do While strFile <> ""
[Code]....
What I have is a master file that needs to pull info from other sources (a simple copy and paste)
those files are called Inventory_xxxx.xlsm . Inside the master file, there are cells with the number of the inventory.
What i need is a way to tell excel to check what number is in the cell, open the files with that number, get the info from the files and paste it back in the master file... and the part that i consider the trickiest, a way to loop it.
For example, lets say i have 2248 in cell A10, it should open Inventory_2248.xlsm copy the contents from cell N4, O4, P4, Q4, R4 and S4(Inventory File) into N10,R10,S10,T10 and U10 (Master file) respectively then in A11, there could be a 2250 so it should open the 2250 file get the info, paste it and so on.....
Now I don't even know if this is do-able or just impossible, been fiddling with various codes to no avail.
I am trying to write some VBA that will select a row of cells that each have links to cells in another workbook, and then either autofill the formulas down (or pastespecial them down) for 20 rows. I have that part down using either pastespecial or autofill, but -
Since the forumulas are links to another workbook, Excel wants to resolve that link to calculate the values at the time of the pastespecial or autfill. If the sourced workbook is not open, the "browse for file" popup displays. I don't want the popup to display and would rather just get the #REF in the pasted cells and let it resolve the next time the sourced workbook is open. The end result would be like clicking "cancel" to the "browse for file popup", which I'm tired of constantly doing... I would just like to skip that popup all together and get the #REF value.
I have 30 xlsx files in a folder and I want the first sheet of all that files to be merged to a new workbook. The thing is I don't want the macro to copy paste the value in to the same sheet of the new master sheet like Ron's excel merge tool does. I want a macro to create new 30 sheets on the master file and copy the data from source files. And I want the newly added sheets to be renamed as the source file name. This works well except the sheet renaming thing.
Sub Merge2MultiSheets()
Dim wbDst As Workbook
Dim wbSrc As Workbook
[Code].....
I have 1000 workbooks in a folder, each workbooks format is same.
as of now I open each workbook, and check if values in A10 is equal to B10, that is if the value in A10 is "100" I will check if B10 is also "100" if not I will make a note the file name.
I have more than 1000 workbooks in a folder, It is really difficult to open and check if the values are same. is there a macro, which will check the condition A10 = B10, if not get the file name in one sheet.
I am attempting to move data between two spreadsheets and have written a macro that initially copies one cell E2 then pastes it correctly into another workbook. I now need to modify the macro to include additional ranges. E2, E3, G2, H2, N1 to U3. I am stuck to the best method of selecting these ranges and then pasting them as I cannot get it to work. I have included a copy of the code below.
Sub getdata()
Dim filename, fileselected
Dim a1(1 To 20)
Dim WB As Workbook
ChDrive ("g:")
ChDir ("G:Folder name")
fileselected = Application. GetOpenFilename("Excel Files, *.xls", , "Select your Files", , True)
If StrComp(TypeName(fileselected), "Boolean", vbTextCompare) = 0 Then
ChDrive ("g:")
Exit Sub
End If.......................
we have a Workbook... in this workbook we want run a VBA or Macro which macro copy a specific sheets (Sheet Name "Abstract") this sheet but when we copy this sheet then in sheet name show file name where this sheet come..
for eg.
if Abstract sheet copy ABC.xls file then sheet name show in my workbook ABC
We have 205 file in the folder (folder store in E:yr 13-14) is it possible that in few files abstract sheet not found..
I am looking for an VBA solution to copy value from multiple workbooks in the same folder.
The master workbook will contain two columns: a list of date (col A) and value (col B) copy from other workbook.
Those multiple workbooks have the same structure which contain a date (A1 cell) and number value (B1 cell)
Basically I want to perform
i) Date matching A1 value in individual work with master workbook
ii) If entry date is matched, then copy B1 value to column B in master book
I have a database from which I extract 5 different excel files. For arguments sake, they are called File 1, File 2 etc. Each of these workbooks will have a sheet called 'Raw Data'. These will all be save in the one directory, C:Data, for example.
I am after a macro that can copy all the data from each of the workbooks, on the sheets called 'Raw Data, however the data in each of the workbooks will vary in length and width.
The workbook which I want them copied to is called "Template" and I would like the copied data onto separate sheets in this file. If possible I would like them copied to already named sheets, for example in the "Template" file, sheets may be called "Raw Data1", "Raw Data2" etc.
I found a great code for copying worksheets from multiple workbooks (up to 2000 workbooks) into one new workbook. It works perfectly for 99% of the copying/merging that i do. The only issue with the code is that it only copies from the active sheet in the other workbooks. I need it to copy from a specific sheet ("Travel") in all of the workbooks that i select. I've tried many ways to edit the code to change the activesheet to "Travel" but i cannot get it to work.
Option Explicit Sub CombineDataFiles() Dim DataBook As Workbook, OutBook As Workbook Dim DataSheet As Worksheet, OutSheet As Worksheet Dim TargetFiles As FileDialog Dim MaxNumberFiles As Long, FileIdx As Long, _ LastDataRow As Long, LastDataCol As Long,
[Code]....
I need to copy multiple workbooks of similar format into one new workbook called "ZZZ.xls". I have developed the below code but I got an error :
Run-time error '1004':
Paste method of worksheet class failed.
Sub create_intransit_stock_FOR_RCP()
Dim Wk As Workbook
Dim FileToOpen As Variant, i, j, k As Integer
Dim NewWBName As String, NewWBName1 As Workbook
Dim OutFileName As String
OutFileName = "C:My_DataZZ.xls"
'On Error Resume Next
FileToOpen = Application.GetOpenFilename(FileFilter:="Microsoft excel files (*.xls), *.xls", Title:="Press CTRL Key to Select Multiple Files", MultiSelect:=True)
If IsArray(FileToOpen) Then
For i = LBound(FileToOpen) To UBound(FileToOpen)..............
i have a group of workbooks all housed in the same folder 'ro24_ws' - who's path is -
C:UsersdavezDocumentseq
o24_ws
what i am looking for is some code to copy a worksheet named 'ro24' from each workbook within this folder into a single workbook
In a folder I have a group of approx 30 workbooks which each contain up to 200 worksheets. All the worksheets contain a set of formulas in the range FR1:FT30. I now need to update these formulas.
I have a precedent file called prec.xls & i need to copy the range FR1:FT30 from this file to all the worksheets in workbooks i have described above.
I have a master workbook that has three sheets: Application, Equipment, Storage. I have over 500 other workbooks that have those same three sheets in them. Those workbooks also have other worksheets that I do not want. How can I create a macro that will open every one of those workbooks and copy over the data from each one of those 3 sheets into it's counter part in the master workbook?
View 3 Replies View RelatedI have 2 workbooks:
- 1 contains tabs for each day of the month and each tab contains a couple of big tables.
- the other is where I want to gather certain data from the first workbook, selection should be made via a condition. I want to copy not the entire row that meets the criteria but lets say the first 4 cells of the row that meets the criteria (code below still copy's entire row, if you also have the code for my goal I would apreciate it very much).
The next part of code is what i've found on the internet and i'm trying to rebuild it to my situation, the biggest problem is the extarnal link to the 1st workbook sheet it seems to hang there everytime. If I copy a part of the table to a empty sheet in the 2nd workbook just linking to that sheet works so the problem is with the external linking what am I doing wrong?
Sub SearchForString()
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
On Error Goto Err_Execute
LSearchRow = 4
'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2
Sheets("[D:Sudden Death BestandenCHC NetherlandsFuelFuel2007januari.xls]1!").Select
While Len(Range("A" & CStr(LSearchRow)).Value) > 0.........................
Currently i am looking for a macro to combine information in different workbooks but on the same sheetname. In every single workbook i have a worksheet with a single row of information which has to be added to one big file. In stead of doing all 200 manually i would like vba to select this particular range, copy it and paste them under eachother in one single worksheet in a different workbook. Is this possible?
for examble book 1, sheet 1 has all the data in rang a1 till z1. this has to be copied to the masterfile under a1. this goes on from book 1 till book 200.
I need to be able to extract particular named columns from multiple files and then populate them in a new excel workbook. Ideally, this would allow me to specify the file directory, the column of interest (based on standardized names), and then run the macro. If this could be done but I would need to have the excel workbooks open to extract the data, that's not a problem (not sure on this). In the multiple workbooks, the data of interest are all on worksheets that have the exact same name, and the column names in Row 1 of those worksheets are all consistently named the same.
Ideally, in the new workbook I need the Row1 column names to contain the source file name from which the data in each respective column came from (with what I'm needing to do all of the extracted data will be the same parameter, e.g., water quality pH values stored in columns from 20 excel files all in the same file directory). Attached are two examples of the many files I have in this format. The worksheet containing the data of interest is called "Station_Comprehensive_Cleaned". In this case, an example column to be extracted from the latter named worksheet in each example workbook and pasted into a new workbook (hopefully/ideally with the respective source workbook file name as the column name) is Column 34 called "Solids_Dissolved"
As an answer to a post about vba script for consolidating workbooks based upon pre-defined cells,
Sub ConsolidateDate()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsDst As Worksheet
Dim wsSrc As Worksheet
Dim I As Long
Set wbDst = ThisWorkbook
Set wsDst = wbDst.ActiveSheet
With Application.FileSearch
.NewSearch
.FileType = msoFileTypeExcelWorkbooks
.LookIn = "C:Myfolder"
For I = 1 To .FoundFiles.Count
Set wbSrc = Workbooks.Open(.FoundFiles(I))
Set wsSrc = wbSrc.Worksheets("Data")
wsDst. Range("A" & I) = wsSrc.Range("A2")
wsDst.Range("B" & I) = wsSrc.Range("C6")
wsDst.Range("C" & I) = wsSrc.Range("D7")
wsDst.Range("D" & I) = Dir(.FoundFiles(I))
Next I
End With
End Sub
I was hoping someone could translate the meaning of this code for me, I am a beginner with using VBA and I am trying to write VBA script for a similar task I am working on. The references to cells A2, C6 and D7 were used as an example of cells to be copied.
