Copy Range Using Row & Sheet Name Variables

Jun 12, 2008

I'm trying to copy multiple cells on the same row, with the row number to be copied stored in a variable (x). Right now i've been trying to use the Range method, but this copies all of the values between "A" & (x) and "H" & (x). I only want the values of the cells in "A", "F" and "H".

(copy_sht) is the name of the sheet to be copied from
(paste_sht) is the name of the sheet to paste into
(copy_row) is the name of the row to be copied from
(paste_row) is the name of the row to be copied to
(x) and (y) - I'm not sure f these are necessary, I've followed a lot of examples to get here, so i'm a bit confused.

Function copyrow(row As Long, copy_row As Long, paste_sht As String, copy_sht As String) As Boolean
'Is passed the names of the sheets, the rows, and copies the data
Dim x As Long
Dim y As Long
x = copy_row
y = row
Sheets(copy_sht).Select
Sheets(copy_sht).Range("A" & x, "F" & x, "H" & x).Copy
Sheets(paste_sht).Select
Sheets(paste_sht).row(y).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Function

To recap, this code will copy all the cells between A(x) and H(x), but i only want the values of A(x), F(x), and H(x).

View 3 Replies


ADVERTISEMENT

Copy Table Values To Array Variables And Use Them In Another Sheet?

Nov 20, 2012

I have a query about using Array Variables in excel VBA. I have a set of lookup tables and a main data table. The data table will be downloaded everyday. I need to replace the ID's in the main table with actual data from the Lookup tables.

e.g.
Main Data Table
Color Operator
005--325 005
004--326 004
003--327
001--328
002--322

Lookup Color
001 - Red
002 - Blue
003 - Green
004 - Violet
005 - White

Lookup Operator Table
323 - Operator 1
324 - Operator 2
325 - Operator 3
326 - Operator 4
327 - Operator 5
328 - Operator 6

I have a huge amount of data in the main table. So I can't use a lookup formula for automation. Rather I would like to use VBA to create array variables, scan and copy the lookup data into the array and replace the ID's in the main table.

Only that I am unable to achieve this using arrays. I am very basic in executing code related to Arrays.

View 2 Replies View Related

VBA Select Range To Copy Based On Two Variables

Jan 13, 2014

I have a Macro that already works great but now I need to look for another variable to sort it down even further. I think it can be done by setting up a "Case" but still not sure how to do that. It already looks through the range to find all the rows that return a value of "True". Now I need it sort those results so it only returns what has a specified value in Column "AJ". I am looking to do several choices (Listed Here). So the end result will be all the items that return "TRUE" In Column "AI" and are Items that return one of these values per Case "Starters, Appetizers, Soup, Salad, Entree, Dessert, Special" with then copy over in the exact format that is listed in the code below.

For example All the items that are selected with "TRUE" can be narrowed down to all of those that are "Salad" as well - Then copied over in the exact format that is listed in the code below.

What I am doing is breaking out all the different course of food and putting a Header in-between each (That is already built and working) - So I can select all the food from a master list and then put it in order on the "Catering BEO" Sheet with Headers in between

Code:
Sub BEOA4()
Application.ScreenUpdating = False
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim FoundX As Range
Dim FirstFound As String
Dim lastrow As Long

[code]....

View 3 Replies View Related

When Data File Close Sheet To Sheet Range To Range Copy

Sep 28, 2008

i 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

View 9 Replies View Related

Copy A Selected Range Of Cells On Sheet One To A Range On Sheet Three

Aug 7, 2008

Hi. Does anyone know a formula to copy a selected range of cells on sheet one to a range on sheet three when a check box in checked. Ex. copy range a4:j4 on sheet one into a4:j4 on sheet three once the check box for on sheet one is checked?

View 9 Replies View Related

Application.inputbox: Range To Copy And Paste The Range's Link And Format To A Different Sheet

Oct 7, 2009

Need a code using application.inputbox to get a range, then use that range to copy and paste the range's link and format to a different sheet? The specifics don't matter, I just can't figure out the syntax. Here is what I have currently:

View 2 Replies View Related

Copy Range From Sheet A Then Find The Next Empty Row In Sheet B

Jun 2, 2014

I want to do is add data into Sheet A each day then press a button which will copy the data in a range and paste it into Sheet B, but I want to create a list of all data so I need it to find the next empty cell and start the paste from there (if that makes sense).

I want it to create a data base on one sheet from a daily import, I have a code to copy one cell to next empty cell but dont know how to duplicate it to a range.

I hope I have given you enough info this is what I have done so far

Public Sub CopyData()
Dim ws As Worksheet, bi As Byte, vData(1 To 1)
Set ws = Sheets("A")
For bi = 1 To 1
vData(bi) = Application.Choose(bi, ws.Range("A1"))

[Code]....

View 7 Replies View Related

Copy Column Value From One Sheet To Another With Path Of Sheet Specified In Range?

Feb 26, 2014

I need to copy columns b,g & h from workbook1 to workbook 2 c ,e, f

workbook1 is the path mentioned in cell A1

Workbook2 is the path mentioned in cell A2

View 1 Replies View Related

Copy Range From Sheet - Dialogue Box To Ask For New Sheet Name Then Paste

Mar 11, 2014

I have a sheet named "Fittings Summary" with an export button. I need some code that will copy range A1:G38 of the fitting summary sheet then open a dialogue box asking for the following "Please Enter Tag No" once a tag number is entered I would like a new sheet to be created in the same workbook named with the tag no entered previously then the copied cells pasted into it. I would like to keep the formatting of the cells but not the formulas within.

View 10 Replies View Related

Copy Specific Range From One Sheet And Paste To Next Available Row Of Different Sheet

Jan 20, 2009

I would appreciate if someone can help me figure out a macro that will copy range (A3:T112) from "Step 1" sheet to next available row in "Step 2" sheet.

View 8 Replies View Related

Search Range In One Sheet - If Any Cell In Range Is Greater Then 0 Copy That Row

Sep 24, 2013

Basically I have three sheets. MAIN, Sheet 1 and Sheet 2

Sheet 1 and 2 are in the same format

A3 down is a list of country names and then B3:I71 contains the data im interested in.

I've been trying to create a function that looks at B3:i71 to see if any cell in that range contains a value greater then $0.00. If it does then the row that contains the cell with a value greater then $0.00 (between col A to K) should be copied to sheet MAIN from cells B3 down. This should ultimatley produce a list of data for any row containing a value greater then $0.00. This process should then be repeated on Sheet 2 and should join the list below sheet 1.

View 3 Replies View Related

Copy Immediate Range, Paste To Another Sheet & Name Pasted Range

Feb 19, 2008

I have been struggling for a while to copy data from one worksheet to another and reset the target range.

The copy bit is cool, the range resetting bit is not. I have tried various methods, but none seem to work.

For example, the below code generates an error: "Compile error: Argument not optional"

I have stuck the particular command button script below to let you see what I am trying to do:

Private Sub cmbFilter_Click()

Dim sCriteria As String

On Error Resume Next

View 4 Replies View Related

Loan Processing-Copy Range From 1 Sheet To Same Range On Another

Jun 22, 2008

I'm developing a loan processing system for members of a club. When an applicant asks for a loan, the club will calculate 10 % of that interest and the applicant will have to pay it back in 5 successive fortnightly instalments. If he asks for a loan in the first fortnight (1), for example, he will have to start paying instalments in fortnights 2,3,4,5,6 to pay it all back.

The system currently has 4 worksheets. The first sheet is a the loan application form. The cells outlined in thicker border, are the cells in which details must be input. Once it is input, the data will be automatically placed in the Processing worksheet using IF and VLookup functions (See spreadsheet attached), which is used as a basis for the loan schedule Worksheet. What I need is a macro that will copy the range filled in the Processing worksheet, and copy it to the exact same location in the Loan schedule worksheet (The cells with the same fortnight columns and the same member name. This is how the loans are to be filed.

View 2 Replies View Related

Copy Range From Other Sheet

Aug 27, 2013

Here is my issue: in cell B99 the formula is =B98+SUM('SAV Daily'!B25:B29)-SUM('SAV Daily'!C25:C29) I want to copy/paste the formula to B100 and I get =B99+SUM('SAV Daily'!B26:B30)-SUM('SAV Daily'!C26:C30)

But I want this result=B99+SUM('SAV Daily'!B30:B34)-SUM('SAV Daily'!C30:C34)

How can it be done without doing VB

View 3 Replies View Related

Range Copy To Another Sheet

Oct 2, 2013

Im trying to get the data from on sheet to another when I activate a sheet. No problems in a formula but I need to use code to keep the sheet safe.

For example, Transfer/copy the data from Sheet 1 Rangge A3:A10 to Sheet 2 Range A4:A11.

View 2 Replies View Related

Copy Range From 1 Sheet To Another

Mar 6, 2008

I have some code to copy a range from one sheet to another... this working code is in a userform... I copied this code to the sheet and used a button to call it, but I now get a 400 error. Here's the working code from the userform;

Private Sub CommandButton5_Click()
Unload UserForm1
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("N").Activate
Range("A12:B2000").Copy (Sheets("Floors").Range("A12"))
Sheets("Floors").Activate
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

And this is what I have in the sheet;.......................

View 6 Replies View Related

Copy Range To Last Row On Another Sheet

Mar 17, 2008

Sheet 1 has a Invoice template in which i need few cells like Invoice no, Date, Client name and amount to be copied into sheet 2 in a single row on click of a button. Again when i change the data and click the same button the data should go to sheet 2 and next available empty row.

I will print the invoices once its done i keep changing the same again. Basically, I want to make a log of invoices in the sheet 2.

View 9 Replies View Related

Copy Range To Another Sheet Range IF

Dec 15, 2011

I want to put this formula on sheet2 in cell Q11 but not sure how to do the copy command in the formula?

If(Sheet1!Q11="True", copy Sheet1!B11:O11 to Sheet2!B:11:O11

View 3 Replies View Related

Copy Active Range To Another Sheet?

May 9, 2014

Looking for a macro that will copy a highlighted range to another sheet. I can get an row to move but not 3 rows.

View 5 Replies View Related

Copy Range From Active Row To New Sheet?

Jun 3, 2014

My VB looks like this at the moment

[Code]....

What I want to do is when a button is clicked, the range of data in columns 2-9 of the active row gets copied. Then a new sheet is created and the data is then pasted to a range in that new sheet.

The VB I have done so far works fine but its specific to a single range - it doesnt take account of the active row.

View 7 Replies View Related

Why Won't This Selected Range Copy To Another Sheet?

Feb 23, 2007

I have a button on the Players worksheet that has this
Why would it not work?

Range("NewData").Range("B5:B" & Cells(Rows.Count, "B").End(xlUp).Row).Copy
Sheets("Players").Range("A3").PasteSpecial Paste:=xlValues

I am trying to copy Column B starting at B5 down to the last used cell in column B. into the players sheet starting at cell a3.

I try it and it highlights 4 cells and that is it???
Michael

View 9 Replies View Related

Copy Range In New Sheet By Criteria

Jul 23, 2008

I want to copy a range of data in a new sheet using a criteria. First i would like to choose a column, and second the criteria.If criteria in this column is meet , copy all rows in the range that meet that criteria in a new sheet named after the criteria .

View 9 Replies View Related

Copy Range Of Cells To Another Sheet

Aug 24, 2007

I have a workbook with 18 sheets. on each sheet there is a column titled "quantity". I've created a 19th sheet to display all rows with a quantity of greater than zero. This is a prototype of a positive test for a value other than zero, and copy the data to the 19th sheet. Here is what I've written.

Sub Test1()

Sheets("SING PLY").Select
Dim row As Integer
row = 31
Dim column As Integer
column = 2
If Sheets("SING PLY"). Cells(row, column) <> 0 Then
Sheets("Buy Out").Range(Cells(31, 1), Cells(31, 6)).Value = Sheets("SING PLY").Range(Cells(31, 1), Cells(31, 6)).Value

End If

End Sub

The error is

Run-time error '1004":
Application-defined Or object-defined error

I'm using the Cells object because this is eventually going to run as a loop and I need a way to increment the values.

View 9 Replies View Related

Copy Column Range To Another Sheet

May 8, 2008

I'm trying to write a DO, LOOP that simply starts at a cell, goes down the column and copies one by one each cell into another sheet until the next cell isempty.

here's my

Sheets("source").Select
Range("A41").Select
Application.CutCopyMode = False
Do Until IsEmpty(ActiveCell.Value)
Selection.Copy
Sheets("active orders").Select
Range("D1").Select
ActiveSheet.Paste

Loop

It currently just loops infinitely because it isn't selecting the new row down (A41 is always full). I'm not sure how to offset the row within the doloop as well as offset the rows to paste accordingly in the new sheet....

View 4 Replies View Related

Simple Copy Of Named Range To Another Sheet?

Feb 11, 2013

I have a named range on one sheet, and I want to show this as a reference on other sheets. I thought this would be simple, but maybe it's just not the way named ranges are used.

Attached is a sample spreadsheet. First tab shows the table defined; in practice it would be much larger. Second tab shows how I want it to appear. I see that I can do this by copying each cell reference. But what would be nice is to simply say "Put the named range block of cells right here."

View 3 Replies View Related

Copy And Pasting A Dynamic Range From One Sheet To Another?

Aug 5, 2014

I'm trying to copy a range of column headers from one worksheet and paste them in another using a macro
The source range starts at Q1 and runs along row 1 for a variable number of columns each time.The destination cells start at B1 and will run along row 1 for the same number of columns.

I've got as far as counting the number of source columns

PHP Code: 

Dim LastDate As Long

LastDate = Sheets("Edited data").cells(1, Columns.Count).End(xlToLeft).Column 

how to paste to a changing number of columns

PHP Code: 

 Sheets("Edited data").Range("Q1", cells(1, LastDate)).copy Destination:=Sheets("Variety Total").Range("B1", ????) 

View 2 Replies View Related

Copy Range In Another Sheet And Create Border

Feb 14, 2009

to copy column C and D, from row 3 to the end, from sheet2,in sheet1, column A and B, starting with row 2 and after that I need to create a border to the copied values and I need to create a border to the empty cell from column C.

open attached file

View 14 Replies View Related

Copy Cells To Different Sheet Based On Range

Mar 22, 2012

I need to make a VBA Macro (Assigned to a button) that will copy the information under the "Cut Length Worksheet" and paste it into the appropriate columns in our "Bill Of Materials Template." Any way to copy the cells, across different rows, into the proper cells.

I have several different Macros and Workbooks made up now, but I am lost here.

I also need the Cut Lengths to add certain info to the cell when it is copied:

8 | 7-15/16 ---> 8'- | 7-15/16"

Here is a Link to the Excel page.

View 3 Replies View Related

Range Selection Programmatically And Copy To Another Sheet

Apr 15, 2014

I want to select a range of cells (not together I.e. b2, c3,c4,g7 etc) and copy them to another sheet but I need to check which cell has been selected as I am using a check box to set a cell as true or false if ticked or not, so if ticked a certain cell will be added to the selection set to be copied.

View 7 Replies View Related

Copy Same Range From 4 Sheets To A Total Sheet

Oct 23, 2007

I need to combine data from 4 tabs with employee names, onto a tab called total. The tab named total is hidden. Is there a way to do this using code?

In my sample I need the name then dates and data below in the yellow areas. As long as Column a has data above cell A22.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved