Need Help to copy data from one worksheet to another

[dpatwary]

Hi,

I have a sheet with about 48 rows and columns with data from Row A to
Row CD. When The macro runs I want to create a new sheet and copy the
first 5 rows to the new sheet and give the user a way to choose the
other row to copy to the new sheet.


I am able to create the new sheet and to let the user select the rows
to copy, buth the new sheet is blank. Could Somebody please help.


Here is the code I have written.

Sub SpreadWorkAssgmnt()
Dim RowToBeCopied As Range 'This line of code is optional


Dim wSht As Worksheet
Dim ResultWSht As Worksheet


Dim shtName As String
Dim Last As Long


Set Rng = Application.InputBox(prompt:="Select Column to Work on",
Type:=8)


Rng.Select


shtName = Format(Now, "mmmm_yyyy")
For Each wSht In Worksheets
If wSht.Name = shtName Then
MsgBox "Sheet already exists...Make necessary " & _
"corrections and try again."
Exit Sub
End If
Next wSht


Set ResultWSht = ThisWorkbook.Worksheets.Add
ResultWSht.Name = shtName
Set wSht = ActiveSheet
wSht.Range("A1").Copy
ResultWSht.Range ("A1:CD5")
MsgBox "I am at the END"
End Sub


Thanks

 

[Mike Fogleman]

Your line 'Set wSht = ActiveSheet' is misplaced. Once you have added the new
sheet, it is now the ActiveSheet. What you did was set wSht = to ResultWSht
so you were copying the new worksheet to itself. Set wSht = ActiveSheet
needs to be after the name check loop but before adding the new sheet.

Sub SpreadWorkAssgmnt()
Dim RowToBeCopied As Range 'This line of code is optional
Dim wSht As Worksheet
Dim ResultWSht As Worksheet
Dim shtName As String
Dim Last As Long
Dim rng As Range

Set rng = Application.InputBox(prompt:="Select Column to Work on", Type:=8)
rng.Select

shtName = Format(Now, "mmmm_yyyy")
For Each wSht In Worksheets
If wSht.Name = shtName Then
MsgBox "Sheet already exists...Make necessary " & _
"corrections and try again."
Exit Sub
End If
Next wSht

Set wSht = ActiveSheet
Set ResultWSht = ThisWorkbook.Worksheets.Add
ResultWSht.Name = shtName

wSht.Range("A1").Copy ResultWSht.Range("A1:CD5")
MsgBox "I am at the END"
End Sub


Mike F

 

[dpatwary]

Mike,

Thanks for your help. Now I am able to copy the first 5 rows from the
old sheet to the new sheet.

When I try to copy the user selected row I am getting the following
runtime error.
Runtime Error 1004:
Method 'Range' of object '_Worksheet failed.

This is what I added at the end after the 5 rows are copied
Set wSht = ActiveSheet
wSht.Range(rng).Copy ResultWSht.Range("A7")

Could you pls help me with this error.

Thanks Again

 

[dpatwary]

Mike,

Thanks I got it working now.

This is the code I am using to copy the user defined range

'Set wSht = ActiveSheet
MsgBox "Range Selected is " & rng.Address

wSht.Range(rng.Address).Copy ResultWSht.Range("A7")

Thanks for your help.
dpatwary