Splitting Workbook With Cells As Filenames?
Jan 21, 2014
I am attempting to split my workbook and save the sheets as individual files. Simple enough? yes. The issue I am running into is that when the sheets export I want their filename to be pulled from the value of a cell on a master control sheet. While I am pretty sure these can be done separately, not sure how to really do them as a whole within a one click button. Also an issue I am running into is that when I click my export button on my control page, the sheets that are saved have lost some of the page formatting.
SHEET1 is my control sheet
Here is the code that I was using to export the sheets.
[Code] ......
View 9 Replies
ADVERTISEMENT
Jan 18, 2006
I am working on a document at work with multiple worksheets. It would be most useful for me to be able to split the screen so that I can work on both worksheets at the same time without (a) having to switch back and forth, (b) modifying the workbook so that everything is on one sheet, or (c) opening the file twice and sizing the windows to my screen.
My goal is to be able to see the information on each sheet while modifying one sheet or the other.
View 9 Replies
View Related
Jun 6, 2006
I have pricelists tables with 300 and more rows and about 50 columns. Three letter codes in first column cells are in strings separated by separator (,), I need to split this strings so that each three letter code would be in its own cell in first column, at same time all other cells in same row as original string data should be copied to rows near appropriate 3 letter codes. In attached xls file I have made small example how data looks before and how it should look after applying macro. Table should not move to other place on worksheet or on new worksheet, in attached example I moved it just to show what kind of form result should have.
View 2 Replies
View Related
Jul 28, 2014
How to split the data in my cells
Currently it looks like this.
1 - 2 - 3,4,5
A - B - C
I would like it to look like this.
1 - 2 - 3
1 - 2 - 4
1 - 2 - 5
A - B - C
View 6 Replies
View Related
May 28, 2009
I know how to use concatenate but is there anything that will do the opposite. I want to take one cell that has both a date and time in it, and make the date in one cell and the time in another.
View 3 Replies
View Related
Oct 14, 2011
spliting a cell and moving them to 2 other cells.
Column D contains date and time in the format: 15/09/2011 08:01:01
I need them to be split into Date in column V and Time column W
Need the code to loop until there are no more values in column D.
Forgot to mention that the Column D is a date format and they need to be in there respective date and time format when split!
View 5 Replies
View Related
Feb 9, 2012
I'm trying to break up text in excel based on certain criteria and would prefer using a formula.
Criteria one: Must be 35 characters or less in length
Criteria two: Must include full words
So for example:
Cell A1: Sally bought a new purse
End Result:
Cell A1: Sally bought a new purse
Cell B1: Sally bought a (assume 35 characters)
Cell C1: new purse (assume 35 characters)
As mentioned earlier the only caveot is that it cannot break individual words IE: "Sally bought a ne"
View 9 Replies
View Related
Nov 14, 2008
I have question I need a macro which splits strings to multiple cells.
Here is how my data looks like.
Here how I want it:
PS: I prefer macro instead of formula. T
View 9 Replies
View Related
Jan 8, 2009
I'm having trouble splitting some data.
I have account codes like 1201.170170 that I must split into two cells containing 1201 and 170170
When I use "left" and "right" functions, it cuts off the zero on the far right. Assuming base data in cell F2...
=Right(f2,6) returns .17017 rather than the intended 170170
I've tried formatting the cells as text, but that doesn't seem to work either.
View 9 Replies
View Related
Oct 12, 2009
I've had a quick look at past posts and there is plenty on splitting cells but I haven't been able to find anything to help me with the following:
I've pulled address details from a database which have populated into a single field with each line separated by 2 'square' characters. I need to split each line into a separate cell for a mail merge. I've seen these characters before and always assumed they represent a space or 'return' and I believe is an ASCII character?
If I select a cursor in the cell the lines drop as they should with 1 square character left at the end of each line and it stays this way when I leave the cell.
I've tried CLEAN & TRIM functions; the first removes the characters, the second doesn't recognise the character at all. The most promising seems the 'Text to Columns' function, setting it as a delimited string, but I don't seem to be able to enter this particular character as the delimiter under the 'other' option.
View 8 Replies
View Related
Jan 18, 2013
Cell A3: Abbott, Robert M.,MD
Cell A4: Bussiere, Laura P., ARNP
Cell A5: Bradford, Melinda ARNP
I have this data in Column A and I'd like each person's last name to appear in Column A and the first name to appear in Column B and the credentials to appear in Column C like this:
A3 B3 C3
Abbott Robert MD
A4 B4 C4
Bussiere Laura P. ARNP
A5 B5 C5
Bradford Melinda ARNP
Also I'd like to be able to do the same with addresses:
In Row Q I have:
Q35: Jacksonville, FL 33204
Q37: St Augustine, FL 32092-1850
I would like this to appear in the sheet as follows:
Q35 R35
Jacksonville 33204
Q37 R37
St Augustine 32092-1850
I don't need the state in any cell. It can disappear as well as any commas. Is any of this possible?
View 1 Replies
View Related
Jan 28, 2014
VBA code to split the the date values into various cells. For example 8-Feb-14 in cell A2 will be splitted into three cells (C2, D2 and E2) to have the values:
Cell C2: 8
Cell D2: Feb
Cell E2: 2014 (Note that 20 has been added to the 14 to make it 2014)
I have attached a spreadsheet to understand what I mean. The results are in red and the raw data is in black.
test12.xlsx
View 9 Replies
View Related
Jan 22, 2009
I have a column that has data with multiple values seperated by commas. I need to seperate out the values and add them to a new column.
Example:
Column A
good, cheap, fast
slow
good
cheap
cheap,good
I would like them to be added to column B as:
good
cheap
fast
slow
good
cheap
cheap
good
View 7 Replies
View Related
Aug 13, 2014
I want to split a cell with a formula and not use text to columns. I will have a cell a1 for example with 1d 4h 36m 34s in and want to have 4 formulas 1 in
a5
a6
a7
a8
.. so the result will be
1
4
36
34
I know you can split with left, mid and right but don't know how to do it with the desired results.
View 7 Replies
View Related
Jan 19, 2014
Was just wondering if it was possible to 'split' these cells (highlighted in the link below) into 2 columns without affecting the layout of the invoice.
[URL]
View 12 Replies
View Related
Feb 13, 2014
I basically have a five column spreadsheet containing address information. While most cells only contain one piece of information, I have quite a few cells that have multiple lines of data in them because the individual has more than one address.
Here is an example. Note that Rows 1-2 contain single address info for each individual person. However, row 3 contains an individual with 3 pieces of address information in the four right hand columns:
Row 1: Unique ID | Name | Street Address | City | State | Zip Code
Row 2: Unique ID | Name | Street Address | City | State | Zip Code
Row 3: Unique ID | Name | Street Address | City | State | Zip Code
[carriage return in street address, city, state and zip cells]
--------------------------------- |Street Address | City | State | Zip Code
[carriage return in street address, city, state and zip cells]
--------------------------------- |Street Address | City | State | Zip Code
Row 4: Unique ID | Name | Street Address | City | State | Zip Code
Row 5: Unique ID | Name | Street Address | City | State | Zip Code
Please note that some individuals have 2-4 different addresses.
I would like to manipulate the 5 rows of example data above so that when I am done I have the following:
1. 7 rows of data (5 individuals with 7 total addresses)
2. Automatically copy and paste the unique id number and name into the two new rows that were created so that I have the following:
Row 1: Unique ID - 001 | Joe Chang | Street Address | City | State | Zip Code
Row 2: Unique ID - 002 | Joey Chung | Street Address | City | State | Zip Code
Row 3: Unique ID - 003 | John Smith | Street Address | City | State | Zip Code
Row 4: Unique ID - 003 | John Smith | Street Address | City | State | Zip Code
Row 5: Unique ID - 003 | John Smith | Street Address | City | State | Zip Code
Row 6: Unique ID - 004 | Jane Derry | Street Address | City | State | Zip Code
Row 7: Unique ID - 005 | Julie March | Street Address | City | State | Zip Code
View 9 Replies
View Related
Jun 17, 2006
My cell contents looks like this.
H1111111111111
I want it to look like this..
H11-111-111111-11
I know this can be done by formula but i dont know it. This info is in col B.
View 2 Replies
View Related
Feb 19, 2007
I tried too much to split one cell Containing more than one lineI need to put every line in different cell.
My small example can describe better ..
View 8 Replies
View Related
Dec 2, 2009
I have a spreadsheet that has been given to me by someone else, and unfortunately it has merged cells in it that are causing some trouble. I need to have a way to unmerge the merged cells, but for the data in the merged cell to then be copied into each cell that it splits into.
I've attached two spreadsheets - unfortunately I can't really attach the real one since it has research data in it, so I've reduced the size of the file and replaced some of the text. The first file shows how the file starts off, and the file named "result" shows how I would like it to be.
My real file contains some 10,000+ lines of data, and the merged cells are of varying sizes, with no consistency to the size, so I'd like a way to automate this instead of copy and pasting into every empty cell after I unmerge them.
View 2 Replies
View Related
Nov 12, 2009
I have names that look like this:
SmithJohn and i want to seperate into two cells so it looks like
Cell 1: Smith
Cell 2: John
View 9 Replies
View Related
Nov 14, 2009
I want to schedule a report to run overnight when I'm not at work, and it needs to save as a filename with the previous day included in it
For example, the filename needs to be saved as
C:Test_variable_ver1.xls where variable is equal to day(today()-1) in excel terms.
View 3 Replies
View Related
Feb 9, 2009
I have a folder which contains a number files. What I would like to do is for my spreadsheet to look into that folder and write the filenames to the sheet. And everytime i run it, the file names are just added on.
View 4 Replies
View Related
Jul 20, 2006
I have about 50 CSV files in a directory and would like to list them in a column that I can use them to populate a box so that a user can select the file they want in the box and open it in a new spreadsheet.
I have tried several ways to do this but nothing I do works.
At present I am using an imput box for the user to type in the filename, but if they type incorrectly, or type in a file name that doesnt exist the program just exits because of my error trap.
View 3 Replies
View Related
Sep 22, 2010
I need tht How to rename the Filenames in an Folder using macro.. Is it possible, How we can do it..
Example: In an Folder 200files are there with extension .jpg/xls/bmp/txt/doc files, I want to rename tht those files names,
Example1:
Source File: TestImage.jpg
I want rename as
Destination file : image.jpg.
Not only Single file rename..if I want to rename Bulk means?...
View 14 Replies
View Related
Dec 3, 2008
I need to know how to read a filename from my local drive c: ? for example in my local drive i have some 100 files, i need to pick there filename, date and time stamp .. etc.
View 7 Replies
View Related
Jun 3, 2006
I'm trying to retrive files from a given directory into excel.
I already have this file from another posted message:
Sub GetFileNames()
Dim F As Long
Dim FileName As String
Dim TheNames As Variant
ReDim TheNames(1 To 1)
FileName = Dir$("*.*")
Do While Len(FileName)
F = F + 1
ReDim Preserve TheNames(1 To F)
TheNames(F) = FileName
FileName = Dir$()
Loop
Cells(1, 1).Resize(F, 1).Value = Application.Transpose(TheNames)
End Sub
But I want to be able to set the path myself
View 11 Replies
View Related
Dec 19, 2013
Have an array that contains file names similar to these:
\serversharesmysharesomefolder3_s6_macro_cycle22_2Ft10p75.txt
\serversharesmysharesomefolder1_s6_macro_cycle22_2Ft10p75.txt
\serversharesmysharesomefolder25_s6_macro_cycle22_2Ft10p75.txt
\serversharesmysharesomefolder32_s6_macro_cycle22_2Ft10p75.txt
I need to sort the array in ascending order based on the file name. The file name is after the last “” and starts with a number(s). From my example above I would like the resulting sort order to look like this:
\serversharesmysharesomefolder1_s6_macro_cycle22_2Ft10p75.txt
\serversharesmysharesomefolder3_s6_macro_cycle22_2Ft10p75.txt
\serversharesmysharesomefolder25_s6_macro_cycle22_2Ft10p75.txt
\serversharesmysharesomefolder32_s6_macro_cycle22_2Ft10p75.txt
I want to use the Application.GetOpenFilename to populate the array and then process the filenames.
I assumed you data starts in "A1"
Results start "F1"
Code:
Sub MG19Dec38()
Dim varFiles As Variant ' used to hold array of file to open
Dim ray
Dim j As Long, i As Long
Dim Temp1 As Integer
Dim Temp2 As Integer
Dim Tem As String
' ray = Application.GetOpenFilename("Txt Files (*.txt), *.txt", , , , True)
View 2 Replies
View Related
Sep 2, 2009
I have a userform which prompts a user to select two files from the directory. Once these two files are open I will extract data from one into the other, the only problem is these filenames will change and I need to be able to determine what their names are.
I have this code below:
Private Sub CommandButton1_Click()
FiletoOpen = Application.GetOpenFilename _
(Title:="Please choose a file", _
FileFilter:="Excel Files *.xls (*.xls),")
If FiletoOpen = False Then
MsgBox "No file specified."
Exit Sub
Else
Filename1 = FiletoOpen
TextBox1.Value = Filename1
End If
End Sub
If I could somehow extract the exact filename, not full path, from the variables Filename1 and Filename2 I would be fine I guess, I just don't know how to do that.
View 9 Replies
View Related
Sep 21, 2006
I took this code from another post and modified it slightly to look in my directory "C:cherylpostal" and list the names of all the files contained. I then have them displayed in a combo box. They are all CSV files, but I would really like them to appear without the .CSV extension. For example, my COQ4 file appears as COQ4.CSV but I would like it to just display COQ4. I can't seem to make it work however.
Sub Macro1()
Dim rngOut As Range
Dim strPath As String
Dim strFile As String
Set rngOut = Range("F1")
strPath = "C:cherylpostal"
strFile = Dir(strPath & "*.*")
If strFile = "" Then
MsgBox "No files matching criteria can be found in " & strPath, vbExclamation
Exit Sub
End If
Do While strFile <> ""
rngOut = strFile
Set rngOut = rngOut.Offset(1, 0)
strFile = Dir
Loop
End Sub
View 5 Replies
View Related
May 5, 2006
I have done a search in the forum, and there are many results on browsing directory and opening files, but they are not what I have in mind.
What I have is different folders for different months (e.g. January, February, March and so on), and in each folder I have the same file names (e.g., in January folder, I have files called A, B, C. In February, I also have files A, B, C but these are different from the files in other folders)
Basically I need a macro which can open a specific list of files.
So first step, the user should be able to browse the directory, then select a folder (no need to open, not sure if it makes any difference though). I need the user to be able to select different folders because the macro should work for the different months in different years.
For this, I have found this
Function GetFolderPath() As String
Dim oShell As Object
Set oShell = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please select folder", 0, "c:\")
If Not oShell Is Nothing Then
GetFolderPath = oShell.Items.Item.Path
Else
GetFolderPath = vbNullString
End If
Set oShell = Nothing
End Function...........
View 9 Replies
View Related
