pasting non-contiguous range of cells to new row, same cell locati

I need a paste macro that will paste a non-contiguous range of cells (ie 
"A1:B1,G1")
to the current row in the same column locations (ie "A5:B5,G5"), assuming 
current row is 5th row.

Does anyone have code for this need that you can share?
0
macros1 (3)
4/3/2006 8:39:02 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
605 Views

Similar Articles

[PageSpeed] 38

Try this

Sub test()
Range("A1:B1").Copy Cells(ActiveCell.Row, "A")
Range("G1").Copy Cells(ActiveCell.Row, "G")
End Sub


-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"Not excelling at macros" <Not excelling at macros@discussions.microsoft.com> wrote in message 
news:3498796E-7DC1-4CDD-A351-6B1DF6FFEDE0@microsoft.com...
>I need a paste macro that will paste a non-contiguous range of cells (ie
> "A1:B1,G1")
> to the current row in the same column locations (ie "A5:B5,G5"), assuming
> current row is 5th row.
>
> Does anyone have code for this need that you can share? 


0
rondebruin (3790)
4/3/2006 8:57:47 PM
This works great for known source/target rows, but I need it to be dynamic.  
So, I click the current row and use a copy macro to select those specific 
cells.  Then, I need to click on the target row and use a paste macro to 
paste to those specific cells.

Your suggestion works great, I just need one more tweak.  Any suggestions?

Thanks Ron!

"Ron de Bruin" wrote:

> Try this
> 
> Sub test()
> Range("A1:B1").Copy Cells(ActiveCell.Row, "A")
> Range("G1").Copy Cells(ActiveCell.Row, "G")
> End Sub
> 
> 
> -- 
> Regards Ron de Bruin
> http://www.rondebruin.nl
> 
> 
> "Not excelling at macros" <Not excelling at macros@discussions.microsoft.com> wrote in message 
> news:3498796E-7DC1-4CDD-A351-6B1DF6FFEDE0@microsoft.com...
> >I need a paste macro that will paste a non-contiguous range of cells (ie
> > "A1:B1,G1")
> > to the current row in the same column locations (ie "A5:B5,G5"), assuming
> > current row is 5th row.
> >
> > Does anyone have code for this need that you can share? 
> 
> 
> 
0
4/3/2006 10:29:02 PM
Try this tester

Sub test()
    Dim arr() As String
    Dim N As Integer
    Dim cell As Range
    Dim destrow As Range
    Dim selectionrow
    Dim I As Integer

    If Selection.Rows.Count > 1 Then Exit Sub
    selectionrow = Selection.Row
    N = 0
    For Each cell In Selection
        N = N + 1
        ReDim Preserve arr(1 To N)
        arr(N) = cell.Column
    Next cell
    On Error Resume Next
    Set destrow = Application.InputBox("select a cell in the Destination row", Type:=8)
    If destrow Is Nothing Then
        On Error GoTo 0
        Exit Sub
    Else
        For I = 1 To N
            Cells(destrow.Cells(1).Row, Val(arr(I))) = Cells(selectionrow, Val(arr(I)))
        Next I
    End If
End Sub


-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"Not excelling at macros" <Notexcellingatmacros@discussions.microsoft.com> wrote in message 
news:698B0A79-FAC4-4B66-9A48-5168489208C9@microsoft.com...
> This works great for known source/target rows, but I need it to be dynamic.
> So, I click the current row and use a copy macro to select those specific
> cells.  Then, I need to click on the target row and use a paste macro to
> paste to those specific cells.
>
> Your suggestion works great, I just need one more tweak.  Any suggestions?
>
> Thanks Ron!
>
> "Ron de Bruin" wrote:
>
>> Try this
>>
>> Sub test()
>> Range("A1:B1").Copy Cells(ActiveCell.Row, "A")
>> Range("G1").Copy Cells(ActiveCell.Row, "G")
>> End Sub
>>
>>
>> -- 
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "Not excelling at macros" <Not excelling at macros@discussions.microsoft.com> wrote in message
>> news:3498796E-7DC1-4CDD-A351-6B1DF6FFEDE0@microsoft.com...
>> >I need a paste macro that will paste a non-contiguous range of cells (ie
>> > "A1:B1,G1")
>> > to the current row in the same column locations (ie "A5:B5,G5"), assuming
>> > current row is 5th row.
>> >
>> > Does anyone have code for this need that you can share?
>>
>>
>> 


0
rondebruin (3790)
4/4/2006 7:57:22 PM
Reply:

Similar Artilces:

How to pick every third cell?
Hi, I have a spreadsheet with a column in which I keep dates. The date appers in every third cell of this column and the other two cells are blank. I have the another sheet in this workbook in which I want those dates to appear in the column in consecutive order. I'm using cell reference comand right now but have to do it manually every time I have a new entries. So in the other sheet there is a column with cells having references to every third cell in the first sheet. I trided to high-lite the multiple bunches of cells and drag-copy it down - but it's not working. Any ideas??? Tha...

Outlook, HTML & Paste Special...
Can somebody please give me a valid reason for why you can't 'Paste Special...' into HTML e-mails? TIA, Andy Haven't checked on earlier versions but the command is available with Outlook 2003 when using Word as the editor. "Deuchars" <sorry@nospam.com> wrote in message news:TkbNb.15439$LT6.168986776@news-text.cableinet.net... > Can somebody please give me a valid reason for why you can't 'Paste > Special...' into HTML e-mails? > > TIA, > > Andy > > > Unfortunately I'm not a fan of using Word as my e-mail editor...

Width of Active Cell Name drop down combo list
Is there any way to make the width of the drop down combo box wider in Excel XP. The one I'm referring to is the one in the top left (to the left of the formula bar) which shows the active cell name. I tend to use long names for my named ranges so, when I click the drop down arrow, it only shows the first part of the name. Ideally, I would like it twice as wide. John. It doesn't look like you can change its size; however, you can get a longer field to work with by clicking Insert/Name/Define on Excel's menu bar. Rick "John Google" <JohnGoogle@hotmail.co.uk>...

Change the formatting of row by select a single cell & Editing should be working
Hi All; I require such a sheet code which can help me in Changing the formatting of row (like A1:A25) of selected cell (A4 is Selected) and allow me to use editing (Cut, Copy, Paste, Undo etc). Currently I'm Using following code but this does not allow me to use editing (Cut, Copy, Paste, Undo etc) and I can not decide the range for working this sheet code. Any kind help is appreciated. Private Sub Search_Click() Sheets("Quick Search").Visible = True ActiveWindow.SelectedSheets.Visible = False Sheets("Quick Search").Select End Sub Private Sub Worksheet_...

Paste from Web Form without auto formatting
I am using Excel 2000, but I am pretty sure I have observed this behaviour before in other versions: How can you paste a table of values into Excel without the program interpreting the values as anything except text? Here is a good example table: http://sports.espn.go.com/mlb/standings/grid When I copy and paste a table such as this one into Excel, the cell values consisting of two numbers separated by a dash get formatted as a date values, even though they are not dates. (For example: the season series between Baltimore and Boston is now 4-4. Excel pastes it as 4/4/2005.) Pre-formatting...

Creating Hidden Range Names
I have a spreadsheet with some workbook wide range names that do not appear in the Insert|Name|Define dialogue box I assume this is because these are 'hidden' range names, which I have not come across before Can anyone advise - how I can get a list of hidden range names in a workbook - how are hidden range names created Many thank KGB KGB, The following routine will show(1st) or delete(2nd) all the hidden names in your (active) file : I don't know what more will create hidden names, but I do know that pivottables is one of the causes of hidden names. Sub ListHiddenNames()...

Given a Row and a Column I would like the Range reference
I'm inserting a custom user defined function into a cell. As a parameter to the function is a cell which is the cell in which the function is located i.e. in cell A5 I have the following function =myCustomFunction(A5) Now, I want to insert this function automatically into that cell but the way I have that cell refrenced is in terms of Rows and Columns, that is A5 = Row 5 Column 1 Is there a way I can do this Cells(5,1) = "=myCustomFunction("& 'convert Row 5 Column 1 to A5 somehow' & ")" Except in very special circumstances, a cell cannot contain...

Shortcut Key or Macro for (Insert -> Entire Row)
Is there a shortcut key or macro I could do that would automatically insert a new row? I'm getting tired of Right Click -> Insert -> Entire Row... Press these keys in succession: [alt] I R That's the [alt] key (which activates the menu) then the letter I (which is the underlined letter in Insert) then the letter R (which is the underlined letter in Row_ Does that help? *********** Regards, Ron XL2002, WinXP "akumaxyz@gmail.com" wrote: > Is there a shortcut key or macro I could do that would automatically > insert a new row? I'm getting tired of Right...

range name
Hi everyone Suppose this example: a1;a2;A3 equals 5, 7, 9 b1;b2;b3 equals 2, 3,8 Name range for first range abc Name range for second range def There's a formula on cell c1 : Sum(abc) What i would like is : on cell d1, i input abc or def or another range name and on cell c1, without any macro but a formula, it sums what i input on cell d1, abc or def. Can you help me please? Tks in advance Pedro Hi you already have received some answers to your question. PLEASE don't multipost. If you have probelms with the offered solutions please stay in the thread and post back in the original th...

VBA Cell.find
Hello, I need to design a VBA macro that would find "paid" in Column A and would select in value in column H. Ex: Column A Column H Paid $100.00 I need to select the $100.00 Regards Hi why not use application.vlookup? -- Regards Frank Kabel Frankfurt, Germany Jeff wrote: > Hello, > > I need to design a VBA macro that would find "paid" in Column A and > would select in value in column H. > Ex: > Column A Column H > Paid $100.00 > I need to select the $100.00 > Regards Hi, Technically, I'm going ...

Sheet Name Refering to Cell Value
I want to name a sheet to the cell value in A2. Cell A2 has an Alt-enter in the cell. I want the sheet name to be the cell value of the first line only. Can anybody help. TIA Greg One way: Option Explicit Sub testme01() Dim myStr As String Dim vbLFPos As Long With ActiveSheet myStr = .Range("a2").Value vbLFPos = InStr(1, myStr, vblf) If vbLFPos > 0 Then myStr = Left(myStr, vbLFPos - 1) On Error Resume Next .Name = myStr If Err.Number <> 0 Then MsgBox "couldn...

multiple value choices for If range().value = "xxx"
The macro below runs on worksheet change and works properly. However, I have 5 values to examine in column H. The values are: Split, Payday 1, Payday 2, Payday 3, and Payday 4. Any of these choices should result in unlocking and clearing the cells in that row (first part of if statement). Otherwise, the range 'pasterage' is copied to that row and it is locked again. I can't find the proper syntax (if there is any) to say something like is possible in SQL (if ...value in ("Split","Payday 1",...)) Please advise on the best way to go about this. ...

Newbie Formula Question
I have an excel s/s which shows in one column a reading of a central heating gas usage , In an adjacent column I want it to automatically show the increment in this gas usage reading when I enter a subsequent reading here. I have inserted the formula :- =IF(C16="","",C16-C15). This calculates the difference between C16 & C15 entries but does not do anything for an entry in C17 - the formula does not repeat as I move down the C column. I've forgoten how to do this - can someone remind me again , please ? B.N. Look at the formulas in those cells ...

Determine if a linked cell contains data
Using vba I'd like to change the formatting/colours on a particular cell if the cell contains text. So for example: If Application.WorksheetFunction.IsText(Cells(i, ColNum)) Then Cells(i, ColNum).Font.Bold = True Cells(i, ColNum).Interior.ColorIndex = 40 End If This works fine for cells which do contain text but not for cells which contain 'linked text'. I have some cells which are linked from a 2nd sheet and in the 2nd sheet they contain text. The code above does not work because there the cell contains ='Sheet2'!B93 How can I get ...

Address of Selection Prior to Clicking on a Cell
If I have cell b5 selected, and I click on cell e5, is there a way for VBA to identify that it came from cell b5 before the click? Code such as this can be set to be triggered when you click on a cell, but can it tell you what cell it came from? Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("B5")) Is Nothing Then _ Range("B5").Value = 10 End Sub Thank you, Chris Try the below.. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static pRange As Range If Not pRange Is Nothing Then MsgBo...

Want Table cells in Word to format like in Excel
Version: 2008 Operating System: Older version (Leopard 9.1) <br> I am trying to do up an invoice in Word and am using tables within it. I want to be able to utilise the Cells for various formats/categories, such as, time, numbers with varying decimal places and accounting ($0.00), like in Excel. Is there a way to do this as I am currently stumped? <br> I don't really want to use Excel for invoicing as most of our invoices are emailed and I don't want the whole 'worksheet' viewable. Unless of course there is a way to make an Excel Spreadsheet to ONLY be one pag...

Paste Special #2
Is there any way to paste more than one attribute at a time e.g. Column width, formats and formulas & number formats rather than having to doing a 'Paste Special' for each attribute? Good question! I think there is no natural way in Excel. However, there are "Excel add-ins" out there that expand Excel's capability and will offer you options such as "paste numbers and formats" etc. My suggestion is to record a macro (Tools > Macros > Record New Macro) whilst doing a paste operation of your choice. and then assign the macro to a custom button on you...

Colour cell to return date
Hi all, this is what i wish to have happen; i have created a list of activities B5:B25 say, next to them i have two column Start & Finish D & E, next to them i have columns G to J, where G1:J1 has date fields in cells 8th Jan 10, 15th Jan 10, to 29th Jan 10 week between them. The question is how can i retrurn a date in cell D5 if i populate G5 (or fill with a colour)? Hi, see Chip explanation Color Change And Calculation Excel normally calculates the formula in a cell when a cell upon which that formula depends changes. For example, the formula =SUM(A1:A10) is reca...

Displaying large numbers in a cell
In a worksheet that one of my co-workers uses, she needs to input numbers that are up to 16 characters long. However, Excel for some reason is changing the last number entered to a 0. For example, if she entered 4223980067104216, Excel would change it to 4223980067104210. If she entered 4223980067104212, Exel will change it to 4223980067104210. I have tried changing the column widths, cell formatting, check the options under Tools, but I am not able to find anything to keep this from occuring. I have noticed that Excel does not make this change if only 15 numbers are entered into a ...

non-local users cannot be given rights on this server
Getting this error when trying to add user back as delegate. Both users have been migrated to Outlook 2007. ...

remove paddings in cells when pasting from excel to word?
Hi, Whe pasting formatted cells from Excel to Word, space paddings fill in most of the cell. This makes the celles extra long in Word, and I have to do a replace all to delete the spaces in order to resize my tables. This weasn't happening until I switched to Office 2003. How can I set up Excel/Word so that there is no padding in formatted cells? Thanks! ...

Selecting only cells with formulas
Is there a quick way to select only those cells that have a formula i them - I want to lock the cells a worksheet that have formulas - bu selecting them one at time is too time consumin -- mycroft77 ----------------------------------------------------------------------- mycroft777's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3114 View this thread: http://www.excelforum.com/showthread.php?threadid=51273 Edit>Goto>Special, check Formulas -- Kind regards, Niek Otten "mycroft777" <mycroft777.23a1wm_1140018000.4073@excelforum-nospam.com&g...

Inserted Row has no formulas in it
When you insert a row to a sheet with formulas (that proceed down a column, e.g. a 'running total' for example), the formula doesn't appear in the inserted row? I know you can copy/paste the formula manually, but since the formula applies to each row, why doesn't the program put it there automatically? I am using 2000, build 9.0.6926 SP-3 Thanks, Lee Bowman Here's a workaround: copy one of the rows that has the formulas. Go to the row above which you want the insert, and select the entire row. Press Ctrl and the + key on the keypad. This inserts a complete copy of ...

Max cell height for text
We need to insert text from a Word document into a cell. The text is about 300 words. After we do the insert, we can only see a portion of the text in the cell. We tried stretching the row boundary, but it stopped stretching towards the bottom of the window. Even the print image doesn't show all the text. How can we increase the cell height to see all the text? Normally pasting into the formula bar the row will cause it to fit automatically but you could try Format>Row>Auto fit, ...then the same with column if needed, but one wonders why you would want to do that as it would ...

new features in office 2.1.1 update
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC I was just wondering if there is a place somewhere that tells you what the new features, fixes and updates are in the new Microsoft update(2.1.1)? I always do the upgrades/updates, but can never find a rundown of what's changed. I'd love to have something like - Entourage - fixed the sounds, word, fixed compatability added borders - or whatever the noodley little changes are. ( i just made that list up as an example..except for the sounds being fixed YAH!) Does that exist somewhere., ive poked around and sea...