| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy a range from active cell
Hi!
I try to make the code so it copy the active cell and the next five cells below. The active cell copy works fine, but am not able to find a way to copy the hole range. The macro to loop through cells with contents: ----------------------------------------------------- Private Sub CommandButton1_Click() Sheets("Sheet2").Select Dim x As Range For Each x In Range("A5:M5") If Not IsEmpty(x) Then x.Copy Sheets("Sheet1").Select Run "kopiering" End If Next End Sub ------------------------------------------------------------- The macro that past the link value to the next sheet. Sub kopiering() Dim rngDestination As Range Set rngDestination = Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) rngDestination.Select ActiveSheet.Paste link:=True ---------------------------------- Thanks in advance Copy & paste developer |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy a range from active cell
You can use the Resize method to expand a range to the size you want.
E.g., ActiveCell.Resize(5, 1).Copy This resizes the range of the active cell to 5 rows and 1 column. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Thu, 8 Apr 2010 06:09:01 -0700, Axel wrote: Hi! I try to make the code so it copy the active cell and the next five cells below. The active cell copy works fine, but am not able to find a way to copy the hole range. The macro to loop through cells with contents: ----------------------------------------------------- Private Sub CommandButton1_Click() Sheets("Sheet2").Select Dim x As Range For Each x In Range("A5:M5") If Not IsEmpty(x) Then x.Copy Sheets("Sheet1").Select Run "kopiering" End If Next End Sub ------------------------------------------------------------- The macro that past the link value to the next sheet. Sub kopiering() Dim rngDestination As Range Set rngDestination = Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) rngDestination.Select ActiveSheet.Paste link:=True ---------------------------------- Thanks in advance Copy & paste developer |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy a range from active cell
hi
try this..... x.resize(5, 1).copy read up on "resize range" in vb help. Regards FSt1 "Axel" wrote: Hi! I try to make the code so it copy the active cell and the next five cells below. The active cell copy works fine, but am not able to find a way to copy the hole range. The macro to loop through cells with contents: ----------------------------------------------------- Private Sub CommandButton1_Click() Sheets("Sheet2").Select Dim x As Range For Each x In Range("A5:M5") If Not IsEmpty(x) Then x.Copy Sheets("Sheet1").Select Run "kopiering" End If Next End Sub ------------------------------------------------------------- The macro that past the link value to the next sheet. Sub kopiering() Dim rngDestination As Range Set rngDestination = Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) rngDestination.Select ActiveSheet.Paste link:=True ---------------------------------- Thanks in advance Copy & paste developer |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy a range from active cell
Thank you FSt1, that worked perfekt.
I also struggle with this: ActiveSheet.Paste link:=True, Transpose:=True When i record a macro and select "transpose checkbox", the "Paste Link" button become inaccessible in the "Paste Special" dialogbox. Anyone who now a different way to do this? -- Copy & paste developer "FSt1" wrote: hi try this..... x.resize(5, 1).copy read up on "resize range" in vb help. Regards FSt1 "Axel" wrote: Hi! I try to make the code so it copy the active cell and the next five cells below. The active cell copy works fine, but am not able to find a way to copy the hole range. The macro to loop through cells with contents: ----------------------------------------------------- Private Sub CommandButton1_Click() Sheets("Sheet2").Select Dim x As Range For Each x In Range("A5:M5") If Not IsEmpty(x) Then x.Copy Sheets("Sheet1").Select Run "kopiering" End If Next End Sub ------------------------------------------------------------- The macro that past the link value to the next sheet. Sub kopiering() Dim rngDestination As Range Set rngDestination = Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) rngDestination.Select ActiveSheet.Paste link:=True ---------------------------------- Thanks in advance Copy & paste developer |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy a range from active cell
Thanks Chip. Your solutions are always helpful.
-- Copy & paste developer "Chip Pearson" wrote: You can use the Resize method to expand a range to the size you want. E.g., ActiveCell.Resize(5, 1).Copy This resizes the range of the active cell to 5 rows and 1 column. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Thu, 8 Apr 2010 06:09:01 -0700, Axel wrote: Hi! I try to make the code so it copy the active cell and the next five cells below. The active cell copy works fine, but am not able to find a way to copy the hole range. The macro to loop through cells with contents: ----------------------------------------------------- Private Sub CommandButton1_Click() Sheets("Sheet2").Select Dim x As Range For Each x In Range("A5:M5") If Not IsEmpty(x) Then x.Copy Sheets("Sheet1").Select Run "kopiering" End If Next End Sub ------------------------------------------------------------- The macro that past the link value to the next sheet. Sub kopiering() Dim rngDestination As Range Set rngDestination = Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) rngDestination.Select ActiveSheet.Paste link:=True ---------------------------------- Thanks in advance Copy & paste developer . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy a range from active cell
hi
sorry for the delay getting back. i got hung up. could you explain more about what your are attempting to do with the code? Regards FSt1 "Axel" wrote: Thank you FSt1, that worked perfekt. I also struggle with this: ActiveSheet.Paste link:=True, Transpose:=True When i record a macro and select "transpose checkbox", the "Paste Link" button become inaccessible in the "Paste Special" dialogbox. Anyone who now a different way to do this? -- Copy & paste developer "FSt1" wrote: hi try this..... x.resize(5, 1).copy read up on "resize range" in vb help. Regards FSt1 "Axel" wrote: Hi! I try to make the code so it copy the active cell and the next five cells below. The active cell copy works fine, but am not able to find a way to copy the hole range. The macro to loop through cells with contents: ----------------------------------------------------- Private Sub CommandButton1_Click() Sheets("Sheet2").Select Dim x As Range For Each x In Range("A5:M5") If Not IsEmpty(x) Then x.Copy Sheets("Sheet1").Select Run "kopiering" End If Next End Sub ------------------------------------------------------------- The macro that past the link value to the next sheet. Sub kopiering() Dim rngDestination As Range Set rngDestination = Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) rngDestination.Select ActiveSheet.Paste link:=True ---------------------------------- Thanks in advance Copy & paste developer |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy a range from active cell
I changed the philosophy in the workbook and use past value insted of paste
link. Then the "Transpose" command work just fine. Selection.PasteSpecial Paste:=xlValue, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Thank's for your response. you made my day at the first question. -- Copy & paste developer "FSt1" wrote: hi sorry for the delay getting back. i got hung up. could you explain more about what your are attempting to do with the code? Regards FSt1 "Axel" wrote: Thank you FSt1, that worked perfekt. I also struggle with this: ActiveSheet.Paste link:=True, Transpose:=True When i record a macro and select "transpose checkbox", the "Paste Link" button become inaccessible in the "Paste Special" dialogbox. Anyone who now a different way to do this? -- Copy & paste developer "FSt1" wrote: hi try this..... x.resize(5, 1).copy read up on "resize range" in vb help. Regards FSt1 "Axel" wrote: Hi! I try to make the code so it copy the active cell and the next five cells below. The active cell copy works fine, but am not able to find a way to copy the hole range. The macro to loop through cells with contents: ----------------------------------------------------- Private Sub CommandButton1_Click() Sheets("Sheet2").Select Dim x As Range For Each x In Range("A5:M5") If Not IsEmpty(x) Then x.Copy Sheets("Sheet1").Select Run "kopiering" End If Next End Sub ------------------------------------------------------------- The macro that past the link value to the next sheet. Sub kopiering() Dim rngDestination As Range Set rngDestination = Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) rngDestination.Select ActiveSheet.Paste link:=True ---------------------------------- Thanks in advance Copy & paste developer |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy range using last active cell reference from 1 wkb to another | Excel Programming | |||
| Help: Macro Copy Active Cell to Range Name, loop | Excel Programming | |||
| need to Copy or Move to active cell from specified range | Excel Discussion (Misc queries) | |||
| To copy from a range in another sheet to the active cell | Excel Programming | |||
| Macro Copy Active Cell to Range Name, loop | Excel Programming | |||
Linear Mode
