Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > First effort
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 39
Default First effort
I want to to prompt the user for a range to copy, copy that range to a new
column, then change the original range to values. It gets hung up on the
line Range("OldRange1:OldRange2").Select. I tried prompting for the whole
OldRange(R1:T100) but couldn't get that to work so I tried what you see below
and got further down. I suppose I have an incorrect syntax, so would the
same answer apply to the Range("Old/NewRange").select statements that follow
it?
This is a modified Recorded macro so it's probably clumsy but you've got to
start somewhere.
Your help is appreciated.


Sub MonthlyAvailReport2()
'
' MonthlyAvailReport2 Macro
' Prepare monthly report new columns, values.
Dim OldRange1 As Range
Dim OldRange2 As Range
Dim NewRange As Range
Set OldRange1 = Application.InputBox _
(prompt:="Enter the first column of last month's figures in format
R1", Type:=8)
Set OldRange2 = Application.InputBox _
(prompt:="Enter the last column of last month's figures in format
T100", Type:=8)
Set NewRange = Application.InputBox _
(prompt:="Enter next column in format U1", Type:=8)
Range("OldRange1:OldRange2").Select
Selection.Copy
Range("NewRange").Select
ActiveSheet.Paste
Range("OldRange").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

End Sub
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 336
Default First effort
It may work if that statement read:
Range(OldRange1 & ":" & OldRange2).Select


"wal50" wrote:

I want to to prompt the user for a range to copy, copy that range to a new
column, then change the original range to values. It gets hung up on the
line Range("OldRange1:OldRange2").Select. I tried prompting for the whole
OldRange(R1:T100) but couldn't get that to work so I tried what you see below
and got further down. I suppose I have an incorrect syntax, so would the
same answer apply to the Range("Old/NewRange").select statements that follow
it?
This is a modified Recorded macro so it's probably clumsy but you've got to
start somewhere.
Your help is appreciated.


Sub MonthlyAvailReport2()
'
' MonthlyAvailReport2 Macro
' Prepare monthly report new columns, values.
Dim OldRange1 As Range
Dim OldRange2 As Range
Dim NewRange As Range
Set OldRange1 = Application.InputBox _
(prompt:="Enter the first column of last month's figures in format
R1", Type:=8)
Set OldRange2 = Application.InputBox _
(prompt:="Enter the last column of last month's figures in format
T100", Type:=8)
Set NewRange = Application.InputBox _
(prompt:="Enter next column in format U1", Type:=8)
Range("OldRange1:OldRange2").Select
Selection.Copy
Range("NewRange").Select
ActiveSheet.Paste
Range("OldRange").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

End Sub
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 318
Default First effort
Try this

Sub MonthlyAvailReport2()
'
' MonthlyAvailReport2 Macro
' Prepare monthly report new columns, values.
Dim OldRange1 As Range
Dim OldRange2 As Range
Dim NewRange As Range
Set OldRange1 = Application.InputBox(prompt:="Enter the first column of
last month's figures in formatR1 ", Type:=8)
Set OldRange2 = Application.InputBox _
(prompt:="Enter the last column of last month's figures in format
T100 ", Type:=8)
Set NewRange = Application.InputBox _
(prompt:="Enter next column in format U1", Type:=8)
Range(OldRange1, OldRange2).Select
Selection.Copy
'NewRange.Select
'ActiveSheet.Paste
'Application.CutCopyMode = False
'Range("OldRange").Select
'Selection.Copy
NewRange.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

End Sub

The main issue was in how to get the range from two corner cells.

Alok Joshi


"wal50" wrote:

I want to to prompt the user for a range to copy, copy that range to a new
column, then change the original range to values. It gets hung up on the
line Range("OldRange1:OldRange2").Select. I tried prompting for the whole
OldRange(R1:T100) but couldn't get that to work so I tried what you see below
and got further down. I suppose I have an incorrect syntax, so would the
same answer apply to the Range("Old/NewRange").select statements that follow
it?
This is a modified Recorded macro so it's probably clumsy but you've got to
start somewhere.
Your help is appreciated.


Sub MonthlyAvailReport2()
'
' MonthlyAvailReport2 Macro
' Prepare monthly report new columns, values.
Dim OldRange1 As Range
Dim OldRange2 As Range
Dim NewRange As Range
Set OldRange1 = Application.InputBox _
(prompt:="Enter the first column of last month's figures in format
R1", Type:=8)
Set OldRange2 = Application.InputBox _
(prompt:="Enter the last column of last month's figures in format
T100", Type:=8)
Set NewRange = Application.InputBox _
(prompt:="Enter next column in format U1", Type:=8)
Range("OldRange1:OldRange2").Select
Selection.Copy
Range("NewRange").Select
ActiveSheet.Paste
Range("OldRange").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

End Sub
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 39
Default First effort
Thanks. I wound up with the Sub below. The NewRange statement needed to
have both corner cells (I don't understand why) and I had to "uncomment" some
of the lines you sent. You got me going in the right direction, which is way
more helpful to me than just getting code fixed. Thanks again.

WAL50


Sub MonthlyAvailReport2()
'
' MonthlyAvailReport2 Macro
' Prepare monthly report new columns, values.
'
' Keyboard Shortcut: Ctrl+y
'
Dim OldRange1 As Range
Dim OldRange2 As Range
Dim NewRange As Range
Set OldRange1 = Application.InputBox _
(prompt:="Enter the first cell of last month's figures in format
R1", Type:=8)
Set OldRange2 = Application.InputBox _
(prompt:="Enter the last cell of last month's figures in format
T100", Type:=8)
Set NewRange = Application.InputBox _
(prompt:="Enter next column in format U1", Type:=8)
Set NewRange2 = Application.InputBox _
(prompt:="Enter next column in format w100", Type:=8)
Range(OldRange1, OldRange2).Select
Selection.Copy
Range(NewRange, NewRange2).Select
ActiveSheet.Paste
Range(OldRange1, OldRange2).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

End Sub
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,758
Default First effort
Maybe...

Option Explicit
Sub MonthlyAvailReport2()

Dim OldRange1 As Range
Dim OldRange2 As Range
Dim NewRange As Range
Set OldRange1 = Application.InputBox _
(prompt:="Enter the first column of last month's figures in format R1", _
Type:=8)
Set OldRange2 = Application.InputBox _
(prompt:="Enter the last column of last month's figures in format T100", _
Type:=8)
Set NewRange = Application.InputBox _
(prompt:="Enter next column in format U1", Type:=8)

With Range(OldRange1, OldRange2)
.Copy _
Destination:=NewRange
.Value = .Value
End With

End Sub

I removed some of the .selects and just worked on the ranges directly.

But you may want to add a few validation checks to make protect your macro from
user error:

Option Explicit
Sub MonthlyAvailReport2()

Dim OldRange1 As Range
Dim OldRange2 As Range
Dim NewRange As Range

Set OldRange1 = Nothing
On Error Resume Next
Set OldRange1 = Application.InputBox _
(prompt:="Enter the first column of last month's" _
& " figures in format R1", Type:=8).Cells(1, 1)
On Error GoTo 0
If OldRange1 Is Nothing Then
'user hit cancel
Exit Sub
End If

Set OldRange2 = Nothing
On Error Resume Next
Set OldRange2 = Application.InputBox _
(prompt:="Enter the last column of last month's" _
& " figures in format T100", Type:=8).Cells(1, 1)
On Error GoTo 0
If OldRange2 Is Nothing Then
'user hit cancel
Exit Sub
End If

'oldrange1 and oldrange2 have to be in the same workbook & worksheet
If OldRange1.Parent.Parent.Name < OldRange2.Parent.Parent.Name _
Or OldRange1.Parent.Name < OldRange2.Parent.Name Then
MsgBox "Please select the two ranges on the same worksheet!"
Exit Sub
End If

Set NewRange = Nothing
On Error Resume Next
Set NewRange = Application.InputBox _
(prompt:="Enter next column in format U1", Type:=8).Cells(1, 1)
On Error GoTo 0
If NewRange Is Nothing Then
'user hit cancel
Exit Sub
End If

With Range(OldRange1, OldRange2)
.Copy _
Destination:=NewRange
.Value = .Value
End With

End Sub

I also changed the oldrange1 and oldrange2 and newrange to just the first cell
of each selection.



wal50 wrote:

I want to to prompt the user for a range to copy, copy that range to a new
column, then change the original range to values. It gets hung up on the
line Range("OldRange1:OldRange2").Select. I tried prompting for the whole
OldRange(R1:T100) but couldn't get that to work so I tried what you see below
and got further down. I suppose I have an incorrect syntax, so would the
same answer apply to the Range("Old/NewRange").select statements that follow
it?
This is a modified Recorded macro so it's probably clumsy but you've got to
start somewhere.
Your help is appreciated.


Sub MonthlyAvailReport2()
'
' MonthlyAvailReport2 Macro
' Prepare monthly report new columns, values.
Dim OldRange1 As Range
Dim OldRange2 As Range
Dim NewRange As Range
Set OldRange1 = Application.InputBox _
(prompt:="Enter the first column of last month's figures in format
R1", Type:=8)
Set OldRange2 = Application.InputBox _
(prompt:="Enter the last column of last month's figures in format
T100", Type:=8)
Set NewRange = Application.InputBox _
(prompt:="Enter next column in format U1", Type:=8)
Range("OldRange1:OldRange2").Select
Selection.Copy
Range("NewRange").Select
ActiveSheet.Paste
Range("OldRange").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

End Sub

--

Dave Peterson
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,624
Default First effort
One way:

Public Sub MonthlyAvailReport3()
Dim OldRange1 As Range
Dim OldRange2 As Range
Dim NewRange As Range
Set OldRange1 = Application.InputBox(prompt:= _
"Enter the first column of last month's figures in format R1", _
Type:=8)
Set OldRange2 = Application.InputBox(prompt:= _
"Enter the last column of last month's figures in format T100 ", _
Type:=8)
Set NewRange = Application.InputBox(prompt:= _
"Enter next column in format U1", _
Type:=8)
With Range(OldRange1, OldRange2)
NewRange.Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
End Sub

This eliminates all the selections and copying through the clipboard.


In article ,
"wal50" wrote:

I want to to prompt the user for a range to copy, copy that range to a new
column, then change the original range to values. It gets hung up on the
line Range("OldRange1:OldRange2").Select. I tried prompting for the whole
OldRange(R1:T100) but couldn't get that to work so I tried what you see below
and got further down. I suppose I have an incorrect syntax, so would the
same answer apply to the Range("Old/NewRange").select statements that follow
it?
This is a modified Recorded macro so it's probably clumsy but you've got to
start somewhere.
Your help is appreciated.


Sub MonthlyAvailReport2()
'
' MonthlyAvailReport2 Macro
' Prepare monthly report new columns, values.
Dim OldRange1 As Range
Dim OldRange2 As Range
Dim NewRange As Range
Set OldRange1 = Application.InputBox _
(prompt:="Enter the first column of last month's figures in format
R1", Type:=8)
Set OldRange2 = Application.InputBox _
(prompt:="Enter the last column of last month's figures in format
T100", Type:=8)
Set NewRange = Application.InputBox _
(prompt:="Enter next column in format U1", Type:=8)
Range("OldRange1:OldRange2").Select
Selection.Copy
Range("NewRange").Select
ActiveSheet.Paste
Range("OldRange").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

End Sub
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,758
Default First effort
In the first version:

Replace this:
With Range(OldRange1, OldRange2)
.Copy _
Destination:=NewRange
.Value = .Value
End With
with:
With Range(OldRange1, OldRange2)
.Copy _
Destination:=NewRange.cells(1,1)
.Value = .Value
End With

Excel will figure out the correct size based on copied range.

But I'd use the second version...


Dave Peterson wrote:

Maybe...

Option Explicit
Sub MonthlyAvailReport2()

Dim OldRange1 As Range
Dim OldRange2 As Range
Dim NewRange As Range
Set OldRange1 = Application.InputBox _
(prompt:="Enter the first column of last month's figures in format R1", _
Type:=8)
Set OldRange2 = Application.InputBox _
(prompt:="Enter the last column of last month's figures in format T100", _
Type:=8)
Set NewRange = Application.InputBox _
(prompt:="Enter next column in format U1", Type:=8)

With Range(OldRange1, OldRange2)
.Copy _
Destination:=NewRange
.Value = .Value
End With

End Sub

I removed some of the .selects and just worked on the ranges directly.

But you may want to add a few validation checks to make protect your macro from
user error:

Option Explicit
Sub MonthlyAvailReport2()

Dim OldRange1 As Range
Dim OldRange2 As Range
Dim NewRange As Range

Set OldRange1 = Nothing
On Error Resume Next
Set OldRange1 = Application.InputBox _
(prompt:="Enter the first column of last month's" _
& " figures in format R1", Type:=8).Cells(1, 1)
On Error GoTo 0
If OldRange1 Is Nothing Then
'user hit cancel
Exit Sub
End If

Set OldRange2 = Nothing
On Error Resume Next
Set OldRange2 = Application.InputBox _
(prompt:="Enter the last column of last month's" _
& " figures in format T100", Type:=8).Cells(1, 1)
On Error GoTo 0
If OldRange2 Is Nothing Then
'user hit cancel
Exit Sub
End If

'oldrange1 and oldrange2 have to be in the same workbook & worksheet
If OldRange1.Parent.Parent.Name < OldRange2.Parent.Parent.Name _
Or OldRange1.Parent.Name < OldRange2.Parent.Name Then
MsgBox "Please select the two ranges on the same worksheet!"
Exit Sub
End If

Set NewRange = Nothing
On Error Resume Next
Set NewRange = Application.InputBox _
(prompt:="Enter next column in format U1", Type:=8).Cells(1, 1)
On Error GoTo 0
If NewRange Is Nothing Then
'user hit cancel
Exit Sub
End If

With Range(OldRange1, OldRange2)
.Copy _
Destination:=NewRange
.Value = .Value
End With

End Sub

I also changed the oldrange1 and oldrange2 and newrange to just the first cell
of each selection.

wal50 wrote:

I want to to prompt the user for a range to copy, copy that range to a new
column, then change the original range to values. It gets hung up on the
line Range("OldRange1:OldRange2").Select. I tried prompting for the whole
OldRange(R1:T100) but couldn't get that to work so I tried what you see below
and got further down. I suppose I have an incorrect syntax, so would the
same answer apply to the Range("Old/NewRange").select statements that follow
it?
This is a modified Recorded macro so it's probably clumsy but you've got to
start somewhere.
Your help is appreciated.


Sub MonthlyAvailReport2()
'
' MonthlyAvailReport2 Macro
' Prepare monthly report new columns, values.
Dim OldRange1 As Range
Dim OldRange2 As Range
Dim NewRange As Range
Set OldRange1 = Application.InputBox _
(prompt:="Enter the first column of last month's figures in format
R1", Type:=8)
Set OldRange2 = Application.InputBox _
(prompt:="Enter the last column of last month's figures in format
T100", Type:=8)
Set NewRange = Application.InputBox _
(prompt:="Enter next column in format U1", Type:=8)
Range("OldRange1:OldRange2").Select
Selection.Copy
Range("NewRange").Select
ActiveSheet.Paste
Range("OldRange").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

End Sub

--

Dave Peterson

--

Dave Peterson
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
duplicate effort hi Excel Discussion (Misc queries) 1 June 5th 07 08:52 PM
A little more effort... darno[_7_] Excel Programming 4 February 20th 04 07:51 PM


All times are GMT +1. The time now is 02:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement