Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help to copy data from one worksheet to another
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help to copy data from one worksheet to another
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 wrote in message ups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help to copy data from one worksheet to another
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 Mike Fogleman wrote: 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 wrote in message ups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help to copy data from one worksheet to another
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy from B worksheet to A worksheet with NO repeated data | Excel Discussion (Misc queries) | |||
copy data in a cell from worksheet A to worksheet B | Excel Discussion (Misc queries) | |||
Copy Data from one worksheet to another | Excel Discussion (Misc queries) | |||
Copy Modified Worksheet 1 Data to Worksheet 2 | Excel Programming | |||
copy all worksheet data into a new worksheet | Excel Programming |