I used a posted code to copy web query data from sheet2 to sheet1. Then a friend modified the code in order to Copy it to a new row in Sheet1 instead of a new column. The code works perfectly but I really wish that when the data is copied from Sheet2 to Sheet1 it will be only Values in order to keep my formulas and formats.
I read about .PasteSpecial xlPasteValues but I don't seem to find where exactly I should write it within the code.
Normally when a code copies a cell value from Workbook-A, closes Workbook-A and then pastes the value into Workbook-B it works flawlessly. However, i noticed that this doesn't work in all case, for example when a code repeats itself with the "For - Next" function then when the code copies from one workbook to the other it causes an error.
Here is my code and i made the part of the code that is red is where the issue lies.
I have a simple code that opens 2 workbooks and then copies and pastes a cell from one to the other. The cell that's being copied is a formula by the way.
My problem is that the copy and paste keeps returning a "0" value, instead of the correct number. I am thinking it's probably because the file from which it is copied is very large and because the operation barely takes a second, it doesn't allow it enough time for the formula int he cell that is being copied to calculate the value.
I have a data sheet and I usually copy manually the values from different columns. I would like to paste them into another worksheet so that when I paste the values in Column A, I should not surpass Row100, and if so then the code should automatically shift to column B and start pasting. This should apply to all the columns till column F. Once column F is full till row100 then the procedure should start again from column A.
I am looking to create some vba code that looks for a specific value in column 'm' (Z-AUD) for example and where it occurs within my data range copies and pastes the values in columns O,P and Q in to columns K,L and M.
I have two worksheets to combine. One of them have list of student numbers and their names, and the other has student number and their grades. I have to make another sheet that shows their name matched with each of their grades(grades are organized by date)
My thought process in this is to match the student numbers in two sheets, then combine name and grades and paste to another sheet.
I have a workbook with 2 sheets. Both the sheet contains Column "Name,Avg,Max". Compare both the sheets and paste the Avg values in Sheet3(Avg) and Sheet4(Max). I have attached the sample file below.
I think I have a very straight forward problem, I'm copying about 400 values from one workbook to another (from vertical range to horizontal range) and I currently have about 400 lines of code in order to do this. Below I've pasted the code I'm using now but the macro takes an estimated 30 seconds to run. I figure if I can reduce the number of lines the macro will run a lot faster
RowCount = openWb.Sheets("Library Raw Shear Rates").Range("A3").CurrentRegion.Rows.Count With openWb.Sheets("Library Raw Shear Rates").Range("A3")
I have written a short VBA code to create a Paste Special values keyboard shortcut.
Is there a way or a place that I can put this code so that every time I open any workbook in Excel, or open Excel itself that this VBA code will be active and I can use the keyboard shortcut?
I have a little bit of problem with lookup function. When i paste values from another worksheet (paste special, values) in a cell which is lookup value i get #N/A. These values are numbers. When i put '7 for example i get the values i want from lookup table. I have a lot of these cells and its tedious job to put ' in front of every value. Is there a quicker solution?
I am trying to import data from Yahoo, about half the time it works well, but the other half I get the Run-time error '1004' below.
Run-time error '1004': The file could not be accessed. Try on of the following: .. Make sure the specified folder exists. .. Make sure the folder that contains the file is not read-only. .. Make sure the file name does not contain any of the folloing charaters: < > ? [ ] : | or * .. Make sure the file/path name doesn't contain more than 218
with code in which I import data from the web via a query then try to use end(xlUp).row to find the last row of the query. Right now, the code continues while the worksheet still displays "Getting Data...", so the last row always comes out as row 1. After the data is finished importing, however, the last row is most definitely not 1.
If I put a break point after the query and wait for the data to finish importing, then let the code finish, it runs perfectly. But how can I have the code automatically wait to move on until the data is finished importing?
I looking for a solution to change a Access Query Criteria using a VB code in Excel, without opening the Access DB and changing the criteria manualy?
My normal Job is it: 1) I Work all the time in Excel 2) At the end of the work in Excel, I need to open Access 3) Open a query (Called "MyQuery") 4) Change one parameter the Date: Between 01/10/2008 and 30/10/2008 (This for each month) 5) Run the query
I looking to get rid of points 2) to 5) by replacing with a Command button on an Excel sheet
Sub Change_Criteria_And_Run_Query()
Dim mydbase As Object Set mydbase = CreateObject("Access.Application") mydbase.OpenCurrentDatabase ("C:My doucmentsDB1.mdb")
mybase."MyQuery"."Date Criteria" = Between (Worksheet("Sheet1").Range("A1").value) and (Worksheet("Sheet1").Range("A2").value)
>>>>>The above line of code I have problems to get right!
I am currently working on a project that uses Excel to parse a .txt document. Its working quite well for me. It functions by having two worksheets. In the first worksheet I use the " import external data" menu to import my .txt file. In another worksheet I have set up fields that show only the important information from the .txt file and leave the junk behind.
I’m looking to improve the functionality of this by adding a button to automatically clear the data in the first worksheet so that new data can be added quickly.
I have searched the forum and found a couple of threads on "QueryTables". After reading up on those, I have made a simple button with the following code.
After I load a .txt file and parse it using the formulas set up, I copy my needed information and then press this button. The cells clear, and the QueryTables are "reset" (maybe not the right word). Now a different .txt file can be imported and the process starts again.
The problem I am having is that. If there is no "QueryTables.Item(1)" to delete, I get a run time error. (Run-time error '9': Subscript out of range).
Would anyone know how to make my button conditional to having a "QueryTable" active? I.E. If I press it when there is no data loaded it doesn't do anything or give me that error.
In the attached file the „«All the data is available in the details sheet „«Input field is B20. „«There are 6 Rate Schemes. All the input fields are present in all „«Again there are 3 Subline of Business
Need .. when I put a input field and select a C15 and C17 the Exposure Curve, Flexa, Other should get displayed. Hope I made the scenario understandable.
Attached is my code, pay attention to the bold part. I want the sourceSheet to be copied as a sheet and pasted in the targetSheet (the Sheet2 of "NewBook") but I want it pasted asvalues. Here is the specific part which needs to be looked at...and below is the full code.
VB: Set sourceBook = Application.Workbooks.Open(sourceFilename) Set sourceSheet = sourceBook.Sheets("Current") Set targetSheet = NewBook.Sheets("Sheet2")
Basically I have a web query. In this web query the url has a part which changes to direct it to a different page with similar formatted data.
These variables that change are all in column A of my excel sheet.
So say it was http://www.google.com/search?hl=en&q=*VARIABLE*
and Variable changes according to the cell value in A1, A2 etc etc.
Right so say i set up a macro to get the data from the website, and i do some stuff to this data e.g. cop and paste it to B1. I need it to automatically change the urls variable to that of the cells below and then do the copy and pasting to the cell below (B2) it copy and pasted before(B1). Then again it change (the variable at the end of the url) to the value of Cell A3 and after its obtained that data copy and paste it below B3
Im not great with vb, like i can understand it if its infront of me but at this stage i cant write it.
Im guessing though i have to use like a counter that increases makes the variable pick it from the cell below.
Oh and i need it to stop at say when the variable reaches cell A360.
I'm tracking weeks by weeknumber in the format YY-WW. At the moment I am entering the weeks manually in a general cell, preceeded by a '. I would prefer to have the sheet work out the weeks for me as we move the sheet every week. I would like to format the initial cell as follows:
09-08 (or '09-08 if I must) then have the next cell in sequence read 09-09, 09-10, and so on. With the initial week in cell A3, I have tried to accomplish this with:
=LEFT(A3,3)&RIGHT(A3,2)+1
However, this does not work for weeks 01 through 09 because excel kills the leading zero in the second part of the formula, regardless of how I format the cell (format 00-00 does not work.) How can I force Excel it to keep the leading zero in the week number?
I have a spreadsheet with a pivot table that is based on a table within an SQL Server database. Every month a stored procedure is run creating a new table. Using the pivot table wizard I can modify the underlying query to access the new table. The pivot table functions properly with the new data.
I also have a macro that I created that will cycle through all the combinations of page field pivot item values, setting the page field currentpage, do some calculations, and save the results.
This is bombing because it seems that some old date values from the previous month's table are still in the pivot items and when my macro attempts to set the page field's currentpage to one of these values it generates Run-time error '1004' Unable to set the _Default property of the PivotItem class.
I have tried putting a pivotcache.refresh in but that doesn't seem to help.
I am trying to count the occurences of combinations within a range defined by contiguous cell values in one column. My problem lies with setting the value of variables that are queried within the defined range. My macro should;
1)Set ComboValue1 & ComboValue2 values to A1 & B1 cells values respectively 2)define range to be searched by how many contiguous values there are in column D 3)search column E for 2 figure combination (defined by A1 & B1). If present increment counter by 1 and add to column C (to be visible in worksheet). If no match carry on 4)define new range by next set of contiguous values in column C 5) step 3 6)when all possible contiguous ranges defined and searched from column c, perform again on next combination down columns A & B with refreshed occurence counter 7)finish when all combinations are searched for
Sub CountComboOccurence() Dim ListCounter As String Dim ProgCounter As String Dim Counter As Integer Dim ComboValue1 As String Dim ComboValue2 As String Dim Rng As Range Application. ScreenUpdating = False ListCounter = 0 ProgCounter = 0 Counter = 0 ComboValue1 = 0.......................
Paste can this be set to default to Paste Special Values only ?
I have a sheet with a number of lists validated drop down boxes. The sheet is networked and works fine.
Problem other users as they are entering information into the required cells they are copying and pasting. Then the inevitable occurs they paste data into the wrong cells the validation from the original cell is pasted as well. Is there a way I can set paste special values only to be the default for the full sheet.
Using MS Query in Excel, I've created a simple query that pulls its records from an SQL dbase. Here's the statement:
SELECT uvVisit.FacilityListName, uvVisit.DoctorListName, uvVisit.Date, uvVisit.PatientVisitId, uvVisit.PatientLast, uvVisit.PatientFirst FROM CPS.dbo.uvVisit uvVisit WHERE (uvVisit.Date Between ? And ?) ORDER BY uvVisit.FacilityListName
The query runs fine and prompts the user to enter beginning & ending date ranges for the visit date when executed. So far...so good...but, this requires me to manually insert a line in Excel above the 1st record and type in something like: "For Date Range: MM/DD/YYY - MM/DD/YY" to denote the date range that the qualified records fall into (something the user wants to see).
However, I'd like to find a way automatically preface and display in the report's output (perhaps as the 1st line of the report in Excel??) something similar to what I'm already typing, and have it pull the beginning and ending MM/DD/YY values from those supplied by the user in the parameter.
I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.
I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.
Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.
If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?
Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.
Need the query parameters which takes the date from the cell into the query. How should I modify my query if it needs to take the date from a cell?? The bold one date should be picked from one of the cell in sheet 2.
My query is this WEB 1 http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=12
I have a csv file on another drive on the network that i need to query. I believe that ms query would be the best way. I know that a DSN needs to be setup but this macro will be used by various users who wont know how to do that. thus I would like to create one via VBA every time the task needs to be run. I haven't a clue how to do this and i need it to be explained to me in general terms with words of one syllable!