I am attempting to create a macro that opens a file named "shrinkage-billing.xls", searches for a variable "PTOSH" in column A, copies the adjacent cell and pastes the data in another worksheet named "Shrinkage Report 2009.xls"
I tried searching for code for each part of the task separately and trying to piece together multiple macros, that did something similar, but Iím not have a lot of luck, so Iíll break it down into two treads, I hope this will follow the rule of one question per post, but if I should have separated this request even farther as two treads, please let me know and Iíll be happy to comply.
I was given a spread sheet with a macro that loads data from a SQL Data base (not mine), it returns anywhere from one row to 100ís of rows data. Since itís not my spread sheet and macro, I copied this sheet to my destination workbook. The Data loads in 7 columns starting in B4:H4, with the column headings titles: Acct#, Id#, Name, Qtr1 Totals, Qtr2 Totals, Qtr3 Totals, and Qtr4 Totals. Data cells in spreadsheet not formatted (general)
What Iím trying to do: part 1: I need a macro to copy the data only for columns Id# thru Qtr4 Totals (C5:H5 down thru the last row with data) to another sheet. The destination sheet range starts with rows B13:G13, with formulas below and to the right in columns H13, and J13:N13 (column I is blank).
The formulas below the range are a subtotal of the copied columns (D:G). They could be remove and totaled after theyíre copied, so there's nothing below the copied range? Since I need the column totals as a subtotals, my thought was to have the row heading and formulas already on the destinations sheet and just insert a blank rows between as each row was copied?
part 2: As each row of data is copied from the source sheet, I need the macro to copy the formulas to the right of the copied range (H13, and J13:N13) on the destination sheet down for each new row of data copied from the source sheet
ws5 and ws7 are defined and working due to other parts of the code already using them. ive copied the syntax from some of my other copy destination codes the only difference here is instead of ("A2:J10") ive got "(Cells(2,1):Cells(iRow, 10)" as the row number for this is variable, and it is calculating the correct value for iRow and iRow2. its probably something stupid im missing but i cant figure it.
I have a NAMED RANGE of data consisting of 4 columns and as many rows as is selected. Name it CompTable1.
I loop through all of the 4 columns of data to format it (as shown below). This snippet is for column 1 of CompTable1 (a public variable as string) and I basically have the same situation for each column of 4 but replacing each respective x in cells.(n,X) & offset(0,-x) to take into account of where I wish the data to go as I loop through. Ultimately 4 (For Next) loops.
The new range with 5 columns is as follows: Column 1 from CompTable1 = Column 1 of new range Column 2 of new range is blank for something later Column 2 from CompTable1 = Column 3 of new range Column 3 from CompTable1 = Column 4 of new range Column 4 from CompTable1 is not used in the new range but is formatted Column 5 of new range is blank for something later
This certainly works very well and with my amateur VBA status am quite proud of how far I have come in a few months. Is it efficient? Is it free of possible errors? Likely not. I wish to rid myself of the "Select" each cell plaque though maybe using a resize, offset or copy destination operation but can't seem to get it working. Once I do get the data in the new range, I format everything without selecting quite well with my methods described above.
I'm currently working on a problem that I'm having with Hyperlinks. Basically I want to copy the destination data that the hyperlink points to onto another sheet.
The main problem I'm having here is that I cant seem to find a suitable method to do this....I had the idea of using the hyperlink.follow function and then copying the data once I'm there and then moving back to the source hyperlink. My only problem with that is that I dont know how to find the cell address that the hyperlink is in, once I have found that hyperlink.
Therefore my question is two-fold:
- Is there a way to copy the destination data using some sort of hyperlink function? - If not, is there a way to find the cell address within a worksheet, once a hyperlink is found on that worksheet?
I've attached the spreadsheet below to give a better idea of what Im doing, along with the "findHyperlinks" macro,within that workbook, that I am currently working on.
Iīm writting a macro. It works find until a certain point. When I want to change some outputs of the macro without changing the syntax, it display an error mesage while runing the macro. It says Else without If. Which is quite disturbing because the Else was not creating any problem before. Here is my macro before I changed the conditions (this one work nicely)
Sub Copy_Sheet_Beta() Set wba = ActiveWorkbook On Error Resume Next If IsWorkbookOpened("Projekt.xls", "C:Documents and SettingsfrederikSkrivebordRedd Barna") Then Workbooks("Projekt.xls").Activate 'In case open, just activate "Projekt" Else Workbooks.Open Filename:="C:Documents and SettingsfrederikSkrivebordRedd Barnaprojekt.xls" End If Set wb = Workbooks("Projekt.xls") wb.Activate If Not SheetExists(wba.ActiveSheet. Range("C1").Value) Then MsgBox "overall doesn't exist!" Else........................................
I have data that is on a separate .txt file (the source file) that resembles this;
A B C D E F
1 Case District Pct Division Level1 Level2
2 305035 0 20 72
Using VBA from within the destination workbook, I would like to use an input box that allows the user to request/specify what numerical number (0 through 55) in Column B of the txt file (the source file) will be used to determine what data to copy to the destination file. The cells to copy to the destination file would be from Column A through Column G of the source file onto Sheet1, E2 of the destination file.
Column B of the Source file will only contain numbers. However there will be numerous identical numbers in Column B. All of which will need to be copied onto the destination workbook.
From the sample above, if the user were to enter '15' in the input box, the desired result on the destination sheet beginning at E2 would look like this;
Got a wee problem with some syntax for a copy function. At the moment the code i have copies the range and pastes to another range which is defined by a cell number. But i need it to paste special (values only).
I thought I had this cracked last month when I ran a set of reports using some code dependent on using advanced filters to first create a unique list to loop through and then to create a list of all entries in a list for each unique entry. The idea being that I extracted all the data for each unique entry in the list and saved that to a new workbook, named after the unique entry.
However, this month I'm totally flummoxed again. I can't seem to get even the first part, creating a unique list, to run.
I have a sheet with several thousand rows of data with maybe 25 columns. I have the following code to try to extract the unique entries in the 2nd column, column B and store them in the same worksheet in column AD, to use when looping through the list.
VB: With ActiveSheet .Range("AD1").EntireColumn.Delete .Range("A1").CurrentRegion.Columns(2).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet1.Range( _
I am wondering if using an "if" statement or a case statement would be good ways to check two worksheets in two different workbooks to see if their names match? I could use some help correcting my syntax too:
For 1 to ws.count If Worksheet("one").Name = Worksheet("two").Name Worksheet("one").Range("A1:G84").Copy Worksheet("two").Range("A1:G84").Paste Endif Next ws.Count
My goal would be to click on a cell within a range of cells (in a column) and have the value copied to a specified destination cell. There would be a few different columns with source cells and two destination cells. Each column would to copy to a specific cell. My ojective is basically to deal with different processing times (days, weeks, months).
Here's a little visual, clicking on C4 for example would copy the value "5" to cell B2. Clicking on D4 would copy "12" to A2.
row/col A B C D E
1 Days Weeks Processing time X (weeks) Processing time Y (in days) Processing time Z (in weeks)
I can't get Destination: or Before: to get the desired result and can't find any info on the variables. What would be the best way to copy from one workbook to another when the data has to be transfered to the correct/corresponding worksheet?
I have the following code that was achieved using the macro recorder. The only problem I am running into is that during the autofill command, there are never the same amount of rows to autofill. It could be as few as 5 rows and as many as there are rows on the spreadsheet. When I recorded the macro there were 953 lines, and unfortunately I cannot figure out how to change the specific destination of 953 to a variable destination determined by when data ends. Here is the code I currently have:
I am trying to do is when a certain criteria is met I want to pull some information from the net using a web query, and then pasting it into a variable location. I have this code -
Sub Check_Matches() Dim XLoop As Integer, XPlay_Row As Integer, Loop_Length As Integer Dim Match_Ref As String, Team_Addr As String, Match_ID As String, Import_Addr As String XLoop = 1 XPlay_Row = 5 Loop_Length = Range("j1").Value + 1 Do While XLoop < Loop_Length Team_Addr = "S" & XPlay_Row 'A variable that stores the reference to the cell containing the team ID.....................
I would like a user of my program to choose the destination folder where a .txt file will be saved. I have code below which allows the chooser to browse through folders and choose a file:
Dim f1 As Variant, s1 As Variant s1 = "#1: Choose the .txt file which contains montly data for the stock price."
f1 = Application _ . GetOpenFilename("TextFiles(*.txt),*.txt", , s1) txt2.Text = f1
I want the user to browse folders and subfolders like they can with the above code. However the user will select a file path not a particular file. In the case of the code above, the text of Text Box txt2 would be set to the file path (i.e. "C:Documents and SettingsAdministratorDesktop")