Copy Variable Range & Copy Formulas From Destination
Mar 2, 2008
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
View 10 Replies
ADVERTISEMENT
Dec 23, 2008
Trying to copy a range from Sender.xls (sheet) Lists backstage
onto Userform.xls (sheet) Behind the Scenes
When trying to copy the values within a multicell range, the destination cell range (same size) becomes blank.
View 2 Replies
View Related
Jun 19, 2014
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
[Code] ..........
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;
E
F
G
H
I
J
1
Case
District
Pct
Division
Level1
Level2
2
502046
15
1
3
[Code] ............
View 9 Replies
View Related
Oct 18, 2013
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)
2
3
10
110
3
4
11
111
4
5
12
112
5
6
13
113
View 1 Replies
View Related
Oct 17, 2008
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"
The code follows ...
View 8 Replies
View Related
Apr 20, 2012
Since you can copy to someplace with one line of code like this:
Sheets(1).Range("A1:Z5000").Copy Destination:=Sheets(2).Range("A1")
is there any way of doing this with formatting, or are things like paste special features only possible by selecting something?
Ie: Values, column widths, formats, etc. Does the copy destination argument only allow simple copying?
View 2 Replies
View Related
May 1, 2012
Is there any way to use the formula below and send only the values?
FY07.Copy Destination:=Sheets("Age Roll Up").Range("A1")
I know that I can copy and paste special, but was wondering if I could tweak the above line in any way to?
View 7 Replies
View Related
Feb 14, 2008
I have the following code to copy a range to another destination. I would like only the value to go to the destination. How to implement this?
Set rngtocopy = Nothing
On Error Resume Next
Set rngtocopy = Range(rngdata(2, 9), rngdata(lngrows - 2, 9)).SpecialCells(xlCellTypeVisible)
On Error Goto 0
If Not rngtocopy Is Nothing Then
rngtocopy.Copy Destination:=Cells(5, 14 + cc + (t - 1) * 3 + (i - 1) * 17)
View 3 Replies
View Related
Jan 15, 2008
I'm working on a large set of data, so rather than read/writing lots of times to the sheet, I've followed the tip of reading a range to a variant array, processing the array, and then writing the whole thing back to the sheet (and it is indeed much much faster).
Dim vdata As Variant
vdata = Range("SummaryOutput")
'do a bunch of logic
Range("SummaryOutput") = vdata
To make things simpler (fewer ranges/variants to manage) I've defined a large range which includes non-data cells (blanks, labels, SUM formulas, etc..). The problem is when I read the range into variant array, it only copies the cells' values -- which means that once I write it back to the sheet, the formulas are lost.
So what I'm looking for is some way to read range into a variant whilst keeping the formulas (which I would see just being a string value in the variant array?). Does anyone know how this could be done?
View 3 Replies
View Related
Feb 5, 2008
I have a large workbook with LOTS of large formulas. However they dont contain $ characters before the ranges as I haven't needed to manipulate them til now. Now I am changing the sheet and I cant seem to move or copy these cells without the ranges whithin the formulas changing! Is there a way to do this? If not is there a way in VBA that I can check through selected cellls and enter a $ before all the ranges?
View 2 Replies
View Related
Dec 10, 2009
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.
View 7 Replies
View Related
Oct 1, 2009
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.
View 7 Replies
View Related
Dec 21, 2012
I have a range of purchase order rows, with the formulas stored in the first row (TemplateRow) which is hidden. The users may add any number of rows to this range, depending upon the number of different products being purchased.
Code:
'Copy the template row into the first newly inserted row
Rows(TemplateRow).Copy Destination:=Rows(insertionPoint)
'fill down from the inserted row down to the last new row
With Rows(InsertionPoint & ":" & NumberOfInsertedRows.Rows.Hidden = False
.FillDownEnd With
The problem is copying the formulas down to the new rows can take terribly long (minutes) in scenarios of thousands of products. Is there a faster method of copying down my formulas?
View 9 Replies
View Related
Jan 3, 2013
Is there any way to copy a column that has a specific range
=COUNTIF(B10:BE10,"*p")
=COUNTIF(B12:BE12,"*p") etc
to copy that to a column lets say 5 columns to the right without my range changing?
=COUNTIF(G10:BJ10,"*p")
=COUNTIF(G12:BJ12,"*p")
I need the same range in all columns, and I cant seem to accomplish this.
View 3 Replies
View Related
Jul 6, 2007
I have below code (option 1) I was hoping would do what I need except instead of copying formula from A1 it copies values... (option 2) copies formula but does not preserve references...any ideas how to copy exact formula so that references are not changed?
Assumptions:
A1 formula = SUM(B2:E2)
in A5 I would like to copy exact formula to keep references to row 2...
I agree I could use $ in original formula but that would complicate other requirements.
OPTION1:________________
Sub test_var_object()
Dim vRange1 As Range
Set vRange1 = Range("A1")
Range("A5") = vRange1
End Sub
OPTION2:________________
Sub test_var_object()
Dim vRange1 As Range
Set vRange1 = Range("A1")
vRange1.Copy
Range("A5").PasteSpecial
End Sub
View 9 Replies
View Related
Aug 8, 2006
A text file is imported to Excel Worksheet. I use this code to Select/Copy a section of that import;
Selection. Offset(4, 0).Resize(15, 9).Select
Selection.Copy
'The number 15 would mean there are 15 rows of items.
But there are not always 15 rows. There might be 18 rows, but because the code is set to 15 it will miss selecting 3 rows preceding, or if the code is set to 12 it will not select the full item list. However, whatever the amount of rows of items, be it 15,2 18 or 100 for example, directly under the item amounts is a "marker" purposely put in the file before the import for other search functions.
Above that marker is the total Item count! So I need a code to search for that marker, then move up 1 cell and whatever that number is, if "15" would then determine the correct row count to Resize
The "marker" is : ^3^
So the full code is:
Macro1 ()........................
View 8 Replies
View Related
May 13, 2008
I want to be able to add new accounts in the New Account Input sheet (consistent of names and number) and then push a button to paste them in the next free row on Account master.
I do know how to assign a macro to a button - its just the actual vb coding Im struggling with.
View 6 Replies
View Related
Jan 29, 2014
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.
[Code] ......
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.
View 1 Replies
View Related
Nov 10, 2009
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.
View 9 Replies
View Related
Nov 8, 2012
I think I am closer but how do I get all of the data from A2 in the origination worksheet and then copy it to the last blank cell in column A in the destination sheet?
Code:
Sub test4()
Dim lastrow As Long
Set StartSheet = ActiveSheet
'MFG PART NUMBER
StartSheet.Range("R2").Copy
Workbooks.Open Filename:="D:\_DoxaMobiusServtrax Import Template"
[Code]...
View 6 Replies
View Related
Sep 27, 2013
I have this code that copies existing folders to a new destination path. How do I change the code so that the From path and To path is based on cell value rather than writing it into the code?
Code:
Sub Copy_Folders()Set Fobj = CreateObject("Scripting.FileSystemObject")
Fobj.CopyFolder "C:UsersHengDocuments1243-01234photos", "C:UsersHengDocumentsCasework1243-01234photos"
End Sub
View 9 Replies
View Related
Jan 30, 2007
i am writing a custom copy/paste routine which will paste data from a selection if the cell being pasted to is not locked.
The user will select the text for coping and then launch the routine.
The routine will query for destination cell and then process the paste.
This is my routine so far:
Sub testCopy()
Dim c As Range
Dim MyRange As Range
Dim Dest As Range
Set MyRange = Selection
Set Dest = Application.InputBox(prompt:="Select a cell", _
Title:="Paste Destination", Type:=8)
For Each c In MyRange
If Dest.Locked = False Then
Dest.Value = c.Value
End If
'dest = ?
Next c
End Sub
What I need to know is how to increment the dest reference so it is in the same 'relative' position from the initial dest that c (from the selection) is in or is there a better way to do this?
View 3 Replies
View Related
Oct 12, 2011
I wish to copy a range which has the variable adderss in a cell.
Example the address is located in D2 and the address in that cell is B4:R113
I want to select the information in D2 for copying.
View 4 Replies
View Related
Aug 15, 2007
I am trying to write a very basic macro to copy and paste, but when I record the keystrokes, step 4 (see below) seems to be recording absolute cells and not relative (?). At any rate, I can't seem to figure out how to get it to select the text the way I want it to. These are the keystrokes I want:
1. Ctrl-c in the current cell <copy the current cell>
2. DownArrow <move down one cell>
3. Shift(hold)-End-Down <select the blank spaces from here down until the next occupied
cell>
4. Shift(hold)-Up <move the selection up one from the bottom to exclude the occupied cell>
5. Ctrl-v <paste>
6. End-Down <move down to the last occupied cell>
I have set the record tool to record "relative" references.
Below is the code that was recorded. It's line 5 that's not working - how do I fix it? What is the command to tell the program to press the shift key and an up arrow?? Such a simple thing, and yet I can't find the answer anywhere! Things sure have gotten complicated since the old Lotus 1-2-3 macro language...
Sub Fill()
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:A17").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
End Sub
View 8 Replies
View Related
Oct 27, 2011
I have a source data sheet that has around 50 columns. I want to copy and past selected columns to a destination sheet.
View 1 Replies
View Related
Dec 3, 2013
I would like to copy a cell in v2 to from v3 to end of column v where there are values in column u.
View 4 Replies
View Related
Jul 16, 2009
I am using the code below to copy a range and paste it over a variable range.
View 4 Replies
View Related
Apr 14, 2013
I want to fill all a column bottom-up but without specify cells in format "A1500:A2" like
VB:
Range("H1578").Select
Selection.AutoFill Destination:=Range("H2:H1578")
{H1578 have a formula so...}
I have data in the first 7 columns, with a variable(Range) for the first 5 columns
VB:
Dim oRange As Range
Set oRange = ActiveSheet.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(89, 4))
{The first cell in it is A1}
My question is : How to fill Range("H2:H1578") using my ''oRange'' variable?
I try with OffSet but my code didn't work. I need to do the same with the 6 columns to the right and then plot in a chart.
View 4 Replies
View Related
Apr 2, 2014
Basically, i have a common workbook template that is used by multiple users across the business to request a cost for numerous new products.
Within the template, there is a common section at the top, where specific project information is entered. There is also a table beneath where 1 or many products can be entered, with specific information relating to that product in the same row.
All the submitted requests are uploaded via an email attachment, to a particular sharepoint directory.
What i would like to do in the master workbook is the following:-
1. Open in turn every uploaded workbook within the sharepoint directory and copy the following cells into the master workbook, each in it's own row (or next available), with the data in adjacent cells.... 1st cell to enter data is $B6.
Cells to copy from each sheet:
Common info contained within cells:
$DG$2,$N$11,$N$12,$N$19,$N$13,$AO$7,$AO$8,$AO$9,$AO$10,$AO$11,$AO$12,$AO$12,$AO$13,$AO$14,$BO$8,$BO$11,$BO$14
Product specific info: $U37, $AD37, $AH37, $DH37, $C37, $O37
Depending on the number of products requested, we need to repeat (loop?) until it finds the next blank row in the table. I have hidden a blank row in the table, so there will always be one!
All of the common information needs to be included for each product specific entry.
For each file, once the upload has been completed, i would like the file to be moved to another "archive" directory.
I have attached the template for information. The master workbook is still in development so can't share currently.
View 12 Replies
View Related
Mar 5, 2007
I am trying to copy formulas and no matter what I do the cell only displays the result from the cell I am copying. I have tried to copy in different ways, autofill, copy and paste, paste special, and then tried rebooting, everything I know of.
View 9 Replies
View Related
