Sheet Name Reverts To File Name?
Nov 5, 2011I have a .csv file I want to rename the sheet name, but when I save the file, as a .csv the sheet name automatically reverts to the file name.
How do I prevent this?
ADVERTISEMENT
Dynamic Range In Chart Reverts To Static Value?
Jun 7, 2012I created a named range linked to a slider.
Code:
=OFFSET('Slides'!$B$2,0,0,6,'Slides'!$AB$1+1)
Problem is after I put in the range as the chart data range it reverts to -
Code:
='Slides'!$B$2:$H$7
Pivot Table Reverts To Earlier State When Clicking Refresh?
Feb 5, 2014when I refresh data in a pivot table. Each time I click 'Refresh All' it alternates between a refreshed version (up to date with source) to an earlier set of data. This is concerning because operators will not be aware that they are not using the most up to date data 50% of the time.
View 3 Replies View RelatedExcel 2003 :: VBA Radio Button Reverts To Default State
Mar 13, 2014I have an excel file containing 50 sheets which we use as an accreditation test. Each sheet contains one question to which there are several possible answers. These answers (for example, A, B, C and D) are option buttons and when a user selects one, that selection is recorded as their response which is then marked. After answering a question they click a NEXT button to move on and as they move on the previous sheet (question) is hidden so they only ever see the current question sheet.
This has worked well for us for a few years, but we have just noticed some odd behaviour on ONE SPECIFIC sheet. The option button the user selects on that sheet (it is actually question 13 in the sequence) is not stored after the test is completed. However on every other sheet, the selection is stored fine - just this one sheet. The properties of all objects on the rouge sheet are the same as the other sheets. I have duplicated a working sheet and tried replacing the question 13 sheet to try that, but the problem persists.
I tried deleting that Q13 sheet and running the test without it and the same problem occured but this time on the preceeding sheet (question 12) which suggests it is not the actual sheet that is causing the problem.
This is the vb code we have on each sheet
Option ExplicitDim Response As StringDim q13 As IntegerPrivate Sub cmdQ13_Click() q13 = 0
If optq13a.Value = False And optq13b.Value = False And optq13c.Value = False And optq13d.Value = False Then
Response = MsgBox("You must answer Question 13 by selecting one of the options!", 16, "More Information Needed !")
[Code] .....
So in this example there are 4 option buttons and 1 command button. It is no different to the other 49 sheets
Opening Excel File From List Of File Names Located In Single Sheet?
May 31, 2013loop and range function to apply in the below code through which I can avoid writing code for all the rows.
I am trying to open excel files located in single folder from files name (along with the path) in single worksheet (Column B and Row 1 to 500).
I have created follwing code which opens the file and then runs a macro in it.
a Sub Test()
Dim strFName As String
strFName = Sheet1.Range("B2").Value
[Code].....
Extract Data And File Names From File In Folder And Paste Into One Sheet?
Jan 13, 2013The code below looks at file names in column A and then goes to a folder and opens and copies the data in range c2 -lastrow from each file and pastes the data into sheet2.
how I could add to the code so that it also inserts the file name in column c?
It would make it easier to track the data in column B.
VB:
Sub CopyFromFile()
Dim fPath As String
Dim lRow As Long
[Code].....
I have attached a sample workbook. The list of file name is in sheet 1. An example of the output is in sheet 2. The data in column A is dummy data generally spans 100's of rows not just 10 as in the example.
The purpose of the code is to be able to put a list of file names in column a in sheet 1 and extract data from those files in a folder. The data extraction works fine. The reson for adding the file names is so that I know what file the data came from.
Automatically Open File In Specific File Path When Another Sheet Is Opened
Mar 20, 2014I want to open a specific sheet and refresh only said sheet when i open another sheet for example x.xls
So opening x.xls will automatically open y.xls
I've tried this in the workbook code area but it doesn't do anything.
[Code] .....
Copying .xls File Numerous Times And Rename Each File From .xls Spread Sheet
Nov 24, 2009I have an excel template that needs to be copied multiple times and each sheet needs be named according to a list in an excel spread sheet. I also have a formula in the template that needs the value copied instead of the formula.
I got this script from an site and tried it. It runs but I don't see any spread sheets.
strComputer = "."
Set objWMIService = GetObject ("winmgmts:\" & strComputer & "
ootcimv2")
Save Each Worksheet As PDF File And Name Each File Based On Cell Value In Each Sheet
Jul 10, 2014I have a Workbook that already has a macro in it that will generate multiple sheets based on certain criteria.
For each of these newly generated sheets (numbered 1-6 in the attached example), I need to be able save each of them to a PDF file based on a unique name contained in a certain cell (in this case, each named is referenced in cell Q1 of each sheet). As such, I should end up with 6 pdfs based on the attached sample files.
The PDFs would ideally need to be landscaped and should be just 1 page per sheet.
In my attached sample workbook, you'll notice that I have a "PracticePrint" macro that doesn't quite get the job done. One other criteria involves not printing any sheet with the characters "1010" in it (my PracticePrint macro is setup to do that, but it fails to do much else).
Macro Error - First Sheet In Each File To Master File
Mar 7, 2012I am receiving an error at
Set CopyRng = Wkb.worksheet1.Range(Cells(RowofCopyworksheet, 1),
Cells(Cells(Rows.Count, 1).End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))
I am trying to copy the first sheet in each file in the designated folder and paste it into a master worksheet.
Below is the code.
'Description: Combines all files in a specific folder to Format File for Upload.xls
Sub MergeMultipleFiles()
Dim path As String, ThisWB As String, lngFilecounter As Long
Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet
Dim filename As String, Wkb As Workbook
Dim CopyRng As Range, Dest As Range
Dim RowofCopySheet As Integer
[Code] ....
Drop CSV File Into One Sheet And Copy Data To Another Sheet?
Jul 8, 2014update code based on a dynamic range of cells, which worked! I want to drop a csv file into one sheet, and copy the data to another sheet. However, if I drop a new csv file in, and there are fewer rows, the old rows aren't deleted. For example, if my first set of data had 10 rows, and the new has 8, the extra two rows are still there.
[code]
With Sheets("Raw Data")
.Range("A10", .Range("A" & Rows.Count).End(xlUp)).Copy ActiveSheet.Range("A12")
.Range("B11", .Range("B" & Rows.Count).End(xlUp)).Copy ActiveSheet.Range("B12")
.Range("E11", .Range("E" & Rows.Count).End(xlUp)).Copy ActiveSheet.Range("C12")
.Range("F11", .Range("F" & Rows.Count).End(xlUp)).Copy ActiveSheet.Range("D12")
.Range("H11", .Range("H" & Rows.Count).End(xlUp)).Copy ActiveSheet.Range("E12")
End With
[code]
Importing CSV File To Hidden Sheet Without Selecting Sheet
Mar 14, 2014I have this code that first selects a sheet (data) and then imports a csv-fil to the data sheet.
I want to hide the data sheet and the the code doesn't work.
How do I change the import code so that I do not select the sheet?
[Code] ......
I believe it should be something like this: With Sheets("DATA").QueryTables. Add..... but I am not aware of the syntax.
Extract Each Sheet To Text File & Save As Sheet Name .txt`
Jun 9, 2007provide me a code for extracting data and outputting it into individual worksheets situated within the excel workbook file. I am wondering what the code would be if the data were extracted to individual text files, individual workbooks or *.prn files?
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
ws.Name = wsraw.Cells(i, "D")
With ws
.Range("A1") = wsraw.Cells(i, "D")
.Range("A2").Resize(2) = wsraw.Cells(i, "A")
.Range("B2").Resize(2) = wsraw.Cells(i, "B")
.Range("C2") = 0
.Range("C3") = 1000
.Range("A4") = -999
End With
Next
Last Day Of Month Reverts To First Of Month
Oct 27, 2006I'm doing a web query that brings down stock dates/prices. The dates come down as mm/yyyy. I then format the column as mm/dd/yy which changes the date to mm/01/yy. Then I do a loop that converts the dates to the date of the last weekday of the month. I see the changed dates written back to their cells, but by the time I leave the called conversion subroutine and hit the break on the next line of code, the dates have all reverted back to the first of the month.
In a case like this, I use the Step mode in VBE to step through the code while I
have the worksheet the code is acting on visible. That way you can actually see
which line in the code converts the value back to the original value or if indeed
it does put the eomonth date in the active cell. By the way, I'd suggest you use the equivalent of the eomonth worksheet function directly in the macro rather than pasting the date to another cell and reading the eomonth from another cell containing the formula for eomonth.
When Data File Close Sheet To Sheet Range To Range Copy
Sep 28, 2008i have a main.xls file and two data file dat1.xls and dat2.xls
mail named file have ar - br- cr- dr- er -fr sheets
dat1 named file have ar-br-cr sheets
dat2 named file have dr-er-fr sheets
and all this files data source is
colomn source a - fd
row source 29-4000
i want to make two commandbutton to main file first for dat1 second for dat2 file and i need a code to use at this buttons to make
when dat1 and dat2 close
main file user when click first button
copy dat1 file ar sheet colomn source a - fd row source 29-4000 cells to main file ar sheet colomn source a - fd row source 29-4000 cells
copy dat1 file br sheet colomn source a - fd row source 29-4000 cells to main file br sheet colomn source a - fd row source 29-4000 cells
copy dat1 file cr sheet colomn source a - fd row source 29-4000 cells to main file cr sheet colomn source a - fd row source 29-4000 cells
Embed Wav File In Sheet?
Jan 4, 2008I have a need to embed a wav file into a workbook. This is fairly simple to do through the insert>>object>>create from file, but I see no way to do this through VBA. I want the entire process to be simple so the user just has to click a button, pick their file from explorer, then play the sound file through the little speaker icon that is generally created.
In the end the one sheet (filename.xls) is the only thing sent through email and it must contain the wav embedded inside without using the insert>>object>>create from file route.
Using Current File Name And Sheet Name?
Mar 8, 2014for someone that might have files they need to process named the same with an index. How can I set this up so when they open the file to run the macro to grab that specific filename they opened and the sheet name? I need to set the filename to a string and the sheetname to string but I dont know how to get that to work.
example...
filename: download.csv, sheetname: download
filename: download (1).csv, sheetname: download (1)
filename: download (2).csv, sheetname: download (2)
Code:
Set wb2 = Workbooks("DOWNLOAD.CSV")
cnt = wb2.Sheets("download").Cells.SpecialCells(xlCellTypeLastCell).Row
strReportCount = (CStr(cnt))
Save Sheet As A File
Mar 16, 2008I have excel file which has got 3 sheets.
I renamed the 3rd sheet as "Audit Sheet"
I want a macro to save only this sheet3 "Audit Sheet" as a new file in "my documents folder" but the name of the file should be as follows
The sheet3 has this values..
Cell B1 has value " George"
Cell D1 has the value "Week 1"
The name of the file should take value enterd in B1 ( that is George) and value in D1 ( Week 1") so the file name should be "George Week 1".
Copy Sheet To New File
May 1, 2009I have a macro that copies sheets from 4 source files to one master file.
I have a distribution file that has many sheets, each sheet represents a master file. On each sheet, there are four columns A:D, that have lists of sheet names from the 4 source files. The macro scrolls through List A on the first sheet, opens the source file, and copies each tab listed in column A to a new file, it then copies all the sheets listed in column b (from source file #2) to the newly created file and repeats for columns C & D. The new file is saved using the worksheet name on the distribution file. The whole process repeats for each sheet in the distribution file.
Here's the problem, if there are more than 42 sheets being copied to the new file, the macro stops copying them over. I have an on error continue next in the macro, so I don't know if it is erroring out, but when I F8 through, it acts like it's copying the file over, it just doesn't.
The problem does not occur with fewer than 35 sheets being copied.
The most sheets in a workbook would be 60, so I don't think I'm running out of sheet room.
Trying To Reference A Sheet From Another .xls File.
Jun 29, 2006I am not very experienced with excel VBA and I am trying to populate a blank sheet in a file called Agent.xls by referencing a file called Agent_Export_03.xls I tried playing around with a few things and I don't know why it isn't working, I guess I don't know how to reference to a separate file. I need the macro to look at cells in C1 an depending on if they are different or not, populate my new excel file accordingly, but my reference doesn't work.
Sub Agent()
x = 2
counting = 2
countitem = 2
Do While [Agent_Export_03.xls]Sheet1!RxC1.Value <> ""
b = x - 1
If [Agent_Export_03.xls]Sheet1!RxC1.Value <> [Agent_Export_03.xls]Sheet1!RbC1.Value Then..............
Embed Wav File In Sheet
Jan 4, 2008I have a need to embed a wav file into a workbook. This is fairly simple to do through the insert>>object>>create from file, but I see no way to do this through VBA. I want the entire process to be simple so the user just has to click a button, pick their file from explorer, then play the sound file through the little speaker icon that is generally created. In the end the one sheet (filename.xls) is the only thing sent through email and it must contain the wav embedded inside without using the insert>>object>>create from file route.
View 3 Replies View RelatedMatch / Index With Between Sheet (same File)
Feb 22, 2014I am looking for some formula index/match with sheet "source" as input data and sheet "output" as report...
How can do it??
See workbook attached : lookup another sheet_john.xlsx
Macro To Name Sheet With Imported .CSV File Name
Nov 4, 2008I'm working on a sheet to analyse data in .CSV format, sofar I can import the .CSV file correctly, however I would like the Macro to name the sheet with same name as the .CSV file.
As i'll be importing multiple .CSV files.
The code I have sofar is:
Convert Sheet To PDF File Using Loops
Aug 14, 2009Using a macro, how can i convert one excel sheet to one pdf file using loops etc.
e.g. Go to sheet named "sheet1"
save this sheet as "sheet1.pdf"..
Go to "sheet2"
save this sheet as "sheet2.pdf"
Save File Name Based On Sheet Name
Feb 10, 2010I am currently using a simple macro to save my spreadsheet.
View 14 Replies View RelatedHow To Convert Excel Sheet To PDF File By VBA
Dec 8, 2010How to convert Excel sheet to PDF file By VBA code.
View 9 Replies View RelatedSave To File Single Sheet Only
Mar 21, 2012There are multiple tabs in my workbook ("Template", "Database", and "Summary"). I would like to add a "Save to file" button on the "Template" sheet and it will dump a copy of this sheet only using the data on cell C4 and C7 as filename, not the entire workbook.
View 9 Replies View RelatedCopying Sheet From Closed File Via ADO?
May 5, 2012I want to copy the sheet named "data" from closed workbook (xlsm, xlsx, xls) via ADO.The closed workbook will be choosen by user via dialog box. The code must check the existence of "data" sheet. If there is no, then the code must give a warning. Futhermore, which is able to give a warning against the mistake of selecting the file itself.
View 1 Replies View RelatedSave File To PDF - Only One Sheet Appears?
Mar 3, 2014I have six sheets in the work book, but one of them spills on to more than one sheet. If i print it or use print preview it displays all the sheets but when converting to pdf with below code only one sheets worth appears, then it displays the next sheet in the workbook, how to amend this so that if it spills over it all converts to pdf
The worksheet in question has 2,000 plus rows, and depending on what the end user selects, it can contain anything between 15 and 300 of those rows (it is a picking list). i currently have it set so that it prints to paper with five different sections for various product types and a summary page, each section has a named range and i have assigned macros to buttons such as immediately below.
Sub printoutsummary()
Range("printsummary").PrintOut
Range("printreturn").Select[code]......
Copy Word File Into Sheet
Apr 14, 2014i have a directory of words file with same format, how can i insert this files to excel sheet?
for example attach tow files,
Code: [URL]...
Code: [URL]....