cut and paste macro

  • Thread starter Thread starter wally
  • Start date Start date
W

wally

I have 30 rows of numbers the range is: d6:g35, j6:m35 (with h6:i35
blank). Each week I copy and paste one row of numbers to c59. Can you
provide a macro that will copy and paste the one row only for one week.
Then, when I run the macro again the next week it will copy the next
row down and paste it to cell c59. Thanks.
Wallyb
 
Not knowing where to start or how to tell which week is which, how about a
routine that will copy one of the rows when you double-click on a cell in
column D (in the rows from 6-35)?

This needs to go into the sheet's event code for _BeforeDoubleClick()
for help in getting it in there, if you need it:
http://www.jlathamsite.com/Teach/WorksheetCode.htm

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
'a double-click in column D from row 6 to row 35 will activate this
Dim anyRange As Range

Set anyRange = Intersect(Target, Range("D6:D36"))
If anyRange Is Nothing Then
Exit Sub
End If
Cancel = True
Application.EnableEvents = False
Range(anyRange.Address & ":M" & anyRange.Row).Copy
Range("C59").PasteSpecial xlPasteAll
Target.Select
Application.CutCopyMode = False
Application.EnableEvents = True

End Sub
 
Not knowing where to start or how to tell which week is which, how about a
routine that will copy one of the rows when you double-click on a cell in
column D (in the rows from 6-35)?

This needs to go into the sheet's event code for _BeforeDoubleClick()
for help in getting it in there, if you need it:
http://www.jlathamsite.com/Teach/WorksheetCode.htm

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
'a double-click in column D from row 6 to row 35 will activate this
Dim anyRange As Range

Set anyRange = Intersect(Target, Range("D6:D36"))
If anyRange Is Nothing Then
Exit Sub
End If
Cancel = True
Application.EnableEvents = False
Range(anyRange.Address & ":M" & anyRange.Row).Copy
Range("C59").PasteSpecial xlPasteAll
Target.Select
Application.CutCopyMode = False
Application.EnableEvents = True

End Sub
 
I am a novice about the procedure you are telling me about. I am not
sure exactly what I am supposed to do. Are you saying I would double
click on a cell, i.e., D6, or d7 or d8, etc and it would copy and paste
the row of numbers to cell d59?. A double click sounds like a viable
solution. But, how do I get this into the macro mode? I looked at the
web site referenced but it is way over my head even to begin with.
Thanks,
 
Wally,
Exactly right - double-click in any cell from D6 to D36 and all the
information on that row from D on out to where ever it ends will be copied
down to C59. What is at C59 will be overwritten.

Here is the easy way to insert the code:
Go to the sheet where your information is at.
Right-Click on the sheet tab (right on the name) and a list will come up,
choose [View Code] from the list. The Visual Basic Editor will open up and
show you a pretty much blank white page.
Cut and paste the code below right in to it. Don't copy from the earlier
post of mine, the first line will cause an error, I've rewritten that line
here so that the run over into a second line won't cause you any problem.

Then just close the VB Editor like any window- X at upper right. And you'll
be ready to use it.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
'a double-click in column D from row 6 to row 35 will activate this
Dim anyRange As Range
Set anyRange = Intersect(Target, Range("D6:D36"))
If anyRange Is Nothing Then
Exit Sub
End If
Cancel = True
Application.EnableEvents = False
Range(anyRange.Address & ":M" & anyRange.Row).Copy
Range("C59").PasteSpecial xlPasteValues
Target.Select
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub
 
Works great!! Thanks for your prompt and informative reply.
Wally said:
Wally,
Exactly right - double-click in any cell from D6 to D36 and all the
information on that row from D on out to where ever it ends will be copied
down to C59. What is at C59 will be overwritten.

Here is the easy way to insert the code:
Go to the sheet where your information is at.
Right-Click on the sheet tab (right on the name) and a list will come up,
choose [View Code] from the list. The Visual Basic Editor will open up and
show you a pretty much blank white page.
Cut and paste the code below right in to it. Don't copy from the earlier
post of mine, the first line will cause an error, I've rewritten that line
here so that the run over into a second line won't cause you any problem.

Then just close the VB Editor like any window- X at upper right. And you'll
be ready to use it.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
'a double-click in column D from row 6 to row 35 will activate this
Dim anyRange As Range
Set anyRange = Intersect(Target, Range("D6:D36"))
If anyRange Is Nothing Then
Exit Sub
End If
Cancel = True
Application.EnableEvents = False
Range(anyRange.Address & ":M" & anyRange.Row).Copy
Range("C59").PasteSpecial xlPasteValues
Target.Select
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub

wally said:
I am a novice about the procedure you are telling me about. I am not
sure exactly what I am supposed to do. Are you saying I would double
click on a cell, i.e., D6, or d7 or d8, etc and it would copy and paste
the row of numbers to cell d59?. A double click sounds like a viable
solution. But, how do I get this into the macro mode? I looked at the
web site referenced but it is way over my head even to begin with.
Thanks,
Click to expand...
Click to expand...
 
Glad to hear it. Thanks. Just be aware that sometimes, if you're slow on
the double-click, Excel won't see it as a double-click, just as 2 clicks.

wally said:
Works great!! Thanks for your prompt and informative reply.
Wally said:
Wally,
Exactly right - double-click in any cell from D6 to D36 and all the
information on that row from D on out to where ever it ends will be copied
down to C59. What is at C59 will be overwritten.

Here is the easy way to insert the code:
Go to the sheet where your information is at.
Right-Click on the sheet tab (right on the name) and a list will come up,
choose [View Code] from the list. The Visual Basic Editor will open up and
show you a pretty much blank white page.
Cut and paste the code below right in to it. Don't copy from the earlier
post of mine, the first line will cause an error, I've rewritten that line
here so that the run over into a second line won't cause you any problem.

Then just close the VB Editor like any window- X at upper right. And you'll
be ready to use it.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
'a double-click in column D from row 6 to row 35 will activate this
Dim anyRange As Range
Set anyRange = Intersect(Target, Range("D6:D36"))
If anyRange Is Nothing Then
Exit Sub
End If
Cancel = True
Application.EnableEvents = False
Range(anyRange.Address & ":M" & anyRange.Row).Copy
Range("C59").PasteSpecial xlPasteValues
Target.Select
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub

wally said:
I am a novice about the procedure you are telling me about. I am not
sure exactly what I am supposed to do. Are you saying I would double
click on a cell, i.e., D6, or d7 or d8, etc and it would copy and paste
the row of numbers to cell d59?. A double click sounds like a viable
solution. But, how do I get this into the macro mode? I looked at the
web site referenced but it is way over my head even to begin with.
Thanks,
JLatham (removethis) wrote:
Not knowing where to start or how to tell which week is which, how about a
routine that will copy one of the rows when you double-click on a cell in
column D (in the rows from 6-35)?

This needs to go into the sheet's event code for _BeforeDoubleClick()
for help in getting it in there, if you need it:
http://www.jlathamsite.com/Teach/WorksheetCode.htm

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
'a double-click in column D from row 6 to row 35 will activate this
Dim anyRange As Range

Set anyRange = Intersect(Target, Range("D6:D36"))
If anyRange Is Nothing Then
Exit Sub
End If
Cancel = True
Application.EnableEvents = False
Range(anyRange.Address & ":M" & anyRange.Row).Copy
Range("C59").PasteSpecial xlPasteAll
Target.Select
Application.CutCopyMode = False
Application.EnableEvents = True

End Sub

:

I have 30 rows of numbers the range is: d6:g35, j6:m35 (with h6:i35
blank). Each week I copy and paste one row of numbers to c59. Can you
provide a macro that will copy and paste the one row only for one week.
Then, when I run the macro again the next week it will copy the next
row down and paste it to cell c59. Thanks.
Wallyb
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Prevent Cut and Paste 3
Cut and Paste #2, vb 4
Macros 2
Cut & Paste Conditional Formatting 1
Macro Help 1
Copy/Paste Doesn't Carry Formats 2
Offset Formula or Better Way 1
Macro to find, copy, and paste until value change 4

Back
Top