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 have the following VBA code that will copy data from one workbook to another.
Sub copytoanotherworkbook() Dim smallrng As Range Dim destrange As Range Dim destWB As Workbook Dim Lr As Long Application. ScreenUpdating = False If bIsBookOpen("P&WM Estimate Tracking Sheet.xls") Then Set destWB = Workbooks("P&WM Estimate Tracking Sheet.xls") Else Set destWB = Workbooks.Open("N:Estimate SheetP&WM Estimate Tracking Sheet.xls") End If Lr = LastRow(destWB.Worksheets("Tracking Sheet")) + 1 Set sourceRange = ThisWorkbook.Worksheets("Links").Range("A1:L1") Set destrange = destWB.Worksheets("Tracking Sheet").Range("A" & Lr) sourceRange.Copy destrange.PasteSpecial xlPasteValues, , False, False Application.CutCopyMode = False Application.ScreenUpdating = True End Sub
The problem I am having is that when it copies the data to the last line of the destination workbook, I have a formula in column I of the destination workbook that calculates days remaining. How would I go about creating an autofill to fill the formula to only the last row of data? I've read some posts on this, but I don't think they deal with what I need.
The cells A2:A25 are merged in my excel sheet and I would like to create subsequent merged cells below the A2:A25 set; however, I would like the user to determine the number of merged cell boxes.
The code I have written is as follows:
Dim lr As Long lr = Application.InputBox("How many days was the monitor deployed?", Type:=2) If lr = 0 Then Exit Sub Else lr = lr
Range("A2:A25").Select Selection.AutoFill Destination:=Range("A2:A" & lr * 24), Type:=xlFillDefault Range("A2:A" & lr * 24).Select
End Sub
So I am creating a macro where the user opens a message box and types in a value representing the number of days. That value is then calculated into an autofill equation but I keep getting an error.
I have worksheets that refernce values in one column off columns in others and do this on daily basis. each day there are blank amounts in random fields which I inturn use a macro to delete rows with blank amounts
I then want to autofill the numbers from 0001 to the bottem line of the sheet where the word "END" always is, as the position of the end word is random each time and autofill always needs a definite range how do I get my macro to autofill down to the word "end" ?
here is the code that doesnt work for me and I've been trying to fix
Sub Macro1() For counter = 1 To 30 Set curCell = Worksheets("Sheet1").Cells(counter, 6) If curCell.Text = "END" Then Range(Cells(1, 6), Cells(counter-1, 6)). _ Selection.DataSeries Rowcol:=xlColumns, Type:=xlAutoFill, Date:=xlDay, _ Trend:=False Next counter End Sub
I am creating a loop that will autofill 16 cells down. It copies the formula from one cell (Z230), pastes that 16 cells down (cell Z246), then changes the lock properties of the cell (Z246) before autofilling down 16 (to cell Z261). At which point the loop starts over again. (copies cell Z246,pastes it to Z262, fills down 16 , etc)
Because the cell I am copying from changes in each loop, I set a variable to grab the address of the starting cell and last cell of the copy.
I then put this into a String so it would return something like this
Range("Z245:Z259")
Problem is I get extra quotes around Range("Z245:Z259") - it comes out as string "Range("Z245:Z259")" This gives me a problem when I try to replace
What I need to be able to achieve is a function in my macro that will
a: Count the number of rows in the active sheet b: Allow me to use this info in other functions, such as subtotals, autofills
After searching many sites I'm pretty certain the function I need to use is "rowcount".
I've managed to incorporate a loop using this function, which is fine when running the macro but not so good when you are stepping through 5000+ rows
RowCount = ActiveSheet.UsedRange.Rows.Count For x = 2 To RowCount Cells(x, 42).Select ActiveSheet.Paste Next x
So for me now it seems as though "x" should represent the number of row that has been counted in the above code. But when I try to use "x" in functions the macro falls over
need to autofill collum C with a formula related do collum B. If I do this by hand I just type the formula in C 1, and click on the right-down corner of the cell selection, and the formula goes until the last line (last value on collum B). But I want to create a macro to this function, and the problem is that the files that I will apply the macro have different lengths. I want to modify the macro to be able to run from C1 until the end of the values on collum B. I don't want to freeze the last value.
In other files C4819 will not be the last value, could be 5345 for example, but its impossible to do this, my macro should do: "autofill collum C with a formula until the last value on collum B"
Just a niggling problem, I've got lstRow as a Long and it contains the value of the last row offset by (1, 0). The problem is i'm trying to add it into a range
Like that.. Except i've tried a million different combinations of " and & in different places to try and get it to compile. It refuses too. It always gives the error "Expected list seperator or )" I know I could just do it individually from C to K but I'd like to learn how to do it this way as well.
I'll do what I can to explain this mess I want to clear up...I have a series of excel reports I have to download and work thru daily and I need to see if there is a code to autofill down thru a column, based on multiple statements throughout the column..here goes:
Regardless of the actual f cell, I always will need to fill the Blank ** cell w/ the values from the previous cell....Hope this makes sense...& thanks in advance.... have_a_cup@cox.net
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
I am trying to write a macro which will autofill specific columns. The macro will set the range from the start of my autofill to the end of my autofill as a constant range.
The problem I need to get around is the end of my range can always change each time I run the macro. For instance, the first time I run the macro I may only need to autofill from row 4 to row 15. The next time, I may only need to autofill from row 4 to 23 (because of user updates). How can I make the end of my range not be a constant address but variable?
I would like to Copy the cell value from A1 into the next available empty cell in a column (in this case sheet2 A) so that I can create a list of values from A1 over time and graph it.
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 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"
How do I change the destination Cell in a workbook from data entered in a macro?
As an example If I press the command button the first time the Textbox1.value is sent to Cells(11, 5). The next time I press the Same command button the data Textbox1.value is sent to Cells (12, 5)
Mainly is the rows I want to change not the coloums!
Struggled with this for weeks now which I am sure is a simple issue to code.
I am struggling to make people understand the problem though so I hope this works!
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")
There is a big range of cells with normal numbers (ex. 100, 150 .. etc), but I need to convert them in the following formulas that give the same numbers as a result: for example if the cell value is 100, I need to convert it in =if(iserror(100);0;100) and so on with all other values. Is it possible this to be done automatically for all cells?
I want to prevent other users when pasting data, to overwrite the formatting of the destination cell.
Since, it is a shared file, i don't want to bother the people explaining use paste special.
I want this to happen automatically.
I don't know VB, and I tried copying what I found in the internet for similar codes, and none have worked. I only copied as they said either in Thisworkbook, and this is all I can do.
I need very easily explained if there is something more than just copying the code in VB view.
I am sure this is simple but cant get me head around it (tried paste & paste link).. I am wanting to copy formulas in file (A) to file (B) with data source linked in the file (A).
i want is when i copy the formulas to file (B) sheet 2 and it should retain the original path say H:excelfile A[sheet1]$G$1+....xls and source from which the data is being used.
For a Datastream request table, I need to set up a Data Destination in a blank worksheet. Instead of rows, I need columns with headers. For the rows I can drag-down the cells, but Excel doesn't seem to get the row drag-down:
For rows:
Sheet1'!$A$1 Sheet1'!$A$2
"select & drag-down"
Sheet1'!$A$3 Sheet1'!$A$4 Sheet1'!$A$5 etc.
But if I want to use columns I get this:
Sheet1'!$A$1 Sheet1'!$B$1
"drag-down"
Sheet1'!$A$2 Sheet1'!$B$2
instead of Sheet1'!$C$1 Sheet1'!$D$1 Sheet1'!$E$1 etc.
It doesn't get that I want to use columns, not rows again.
