Posts by zxox
-
-
I'm not sure why, but I can't upload my Excel spreadsheet or a jpeg.
1. I want to take data lines and then add 3 rows below each record.
2. Insert a "NEW CODE" column anywhere (it's at the right end right now)
3. Use vlookup to populate the "NEW CODE" based on Code 1
4. Populate the new blank cells based on the "NEW CODE"Is this doable?
Thanks!
-
Re: Merging One Sheet from Multiple Workbooks into One Master
Thank you, but since I'm on a network at work I can not get these to work. The error I receive is something to do with hidden folders and not being able to execute the commands. UGH! SO FRUSTRATED! I can NOT unhide the folders no mater what I do. I even checked with the IS&T folks to no avail.
-
I will have about 100 files to merge together that are in one directory. Is it possible to merge all workSHEETS named "Bob" from each workBOOK and end up with just one master file?
Thanks!
I found this code on this web site (forgive me for not sighting a proper reference!). It doesn't work for me. Yes, I changed the directory and it still didn't work. I will have 12 columns (A:L) and differing # of rows in each "Bob" worksheet.
Code
Display MoreSub g_CombineMultWB_AllXLSFiles() ' This Will combine all EMALL XLS files located in the ' S:\DMSMS\POMS\Master POMS NIIN Data\Master EMALL Data Files\EMALL Excel Folder ' into a single worksheet in a newly created (or previously existing) workbook ' LOCATION OF FILES (ACTUAL): ' S:\DMSMS\POMS\Master POMS NIIN Data\Master EMALL Data Files\EMALL Excel Folder Dim i As Integer Dim wbResults As Workbook Dim wbCodeBook As Workbook Dim f As Worksheet, t As Worksheet, nNew As Long, nOld As Long Application.ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False On Error Resume Next ' Change path To suit Const MYFOLDER = "S:\DMSMS\POMS\Master POMS NIIN Data\Master EMALL Data Files\EMALL Excel Folder\" ' Const MYFOLDER = "G:\CDMO\Combine Multiple WB (Emall Data)\Test Data 2\" ' Reset to "Combined EMALL Data.xls" workbook and make sure it is open Set wbCodeBook = Workbooks.Open(MYFOLDER & "Combined EMALL Data.xls") Set t = wbCodeBook.Sheets(1) 'The sheet to hold all of the data t.Rows("2:" & Rows.Count).Clear 'CLEAR ANY OLD DATA nOld = 2 'Row to copy data to (starting AFTER the header row) With Application.FileSearch .NewSearch .LookIn = MYFOLDER .FileType = msoFileTypeExcelWorkbooks If .Execute > 0 Then 'See if workbooks exist in target folder For i = 1 To .FoundFiles.Count 'Loop through all workbooks in target folder If .FoundFiles(i) <> wbCodeBook.FullName Then Set wbResults = Workbooks.Open(.FoundFiles(i)) Set f = wbResults.Sheets(1) 'Worksheet to process nNew = f.Cells(Rows.Count, 1).End(xlUp).Row 'Last row with entry in column 1 If nNew > 1 Then 'This means we have some data If nOld + nNew - 2 <= Rows.Count Then 'This means there's room to copy f.Rows("2:" & nNew).Copy t.Cells(nOld, 1) nOld = nOld + nNew - 1 Else MsgBox "There is not enough room to copy the data from " _ & .FoundFiles(i), vbCritical, "Out of Room" Exit For End If End If End If wbResults.Close False 'Close workbook without saving Next i End If End With ' Pretty up the worksheet Columns("A:D").Select Range("A6873").Activate Selection.ColumnWidth = 12 Range("A1").Select On Error Goto 0 Application.ScreenUpdating = True Application.DisplayAlerts = True Application.EnableEvents = True Application.CutCopyMode = False ' Clear last copy End Sub
-
I was able to get help to solve this from a co-worker!
This is a form of transposing, but a little more involved. For example, if I have a table of 10 rows x 12 columns and use the Transpose function, then I get another matrix. However, what I need is the output to be 120 rows and 3 columns.
-
Re: Combine Tabs into One Master Sheet
Will do! Thanks again!
-
Re: Combine Tabs into One Master Sheet
Quote from royUK;658351I can't see any point in doing this.
I completely understand this statement! HA! But, trust me this helps immensely! A MILLION THANK YOUS FOR WORKING CODE! :yourock:
-
Re: Combine Tabs into One Master Sheet
All in one sheet -- don't care what it's called. See the summary sheet of my upload.
-
Re: Combine Tabs into One Master Sheet
See attached sample. I have too many files with tabs to do copy paste 1000x. I want to be able to manipulate the data from one master. I need the tab name to be able to reference back to it.
-
Re: Combine Tabs into One Master Sheet
I will make a new spreadsheet for you as I can not provide you with the original information. I'd get fired! I will post shortly.
-
Re: Combine Tabs into One Master Sheet
Thank you for the warm welcome and the replies. Neither of these worked for my data.
I am essentially trying to copy and paste all data rows from B11:Q61 into one sheet. I have about 100 files (each with at least 5 tabs) that are in this same format and would like to get them into one master file.
Maybe you have a better way to accomplish this? Thanks again for your help!
-
I have the following code; however, I need it to be tweaked.
1. I would like the unique tab names to appear in Column A (currently unused).
2. Paste ALL rows, even blank from b2:q61.All sheets are the same with the exception of the code
Code
Display MoreSub CopySheetsToSummary() Dim ws As Worksheet Dim wsDest As Worksheet Dim RangeToCopy As Range Dim RangeToPaste As Range Set wsDest = Worksheets("Summary") For Each ws In ActiveWorkbook.Worksheets If ws.Name <> wsDest.Name Then Set RangeToCopy = ws.Range("a11", ws.Range("a65536").End(xlUp)).EntireRow Set RangeToPaste = wsDest.Range("a65536").End(xlUp).Offset(2, 0) If RangeToPaste.Address = "$A$3" Then Set RangeToPaste = Range("a11") RangeToCopy.Copy RangeToPaste End If Next End Sub