On 2002-04-24 19:50, hgg_21 wrote:
Hi,
I need to copy all the data in worksheet into another worksheet.
Can I have some examples how to go about doing it?
Thanks and regards
Sub MyNewWorksheet()
Dim wsNewWorkSheet As Worksheet
Dim NewSheetRows As Long
Set wsNewWorkSheet = Worksheets.Add
wsNewWorkSheet.Name = "Complete"
Selection.Copy
Range("A1:CT3348").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'change 1 to your first row
'change 1000 to your last row
For NewSheetRows = 1 To 1000
Cells(NewSheetRows, 1).Value = _
"GLEP" & Format(Date, "yyyymmdd") & _
Format(NewSheetRows, "000000")
Next
End Sub
On 2002-04-24 23:57, TsTom wrote:
Hi hgg,
How many rows down are you wanting data?
Try something like this in your macro.
Sub MyNewWorksheet()
Dim wsNewWorkSheet As Worksheet
Dim NewSheetRows As Long
Set wsNewWorkSheet = Worksheets.Add
wsNewWorkSheet.Name = "Complete"
Selection.Copy
Range("A1:CT3348").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'change 1 to your first row
'change 1000 to your last row
For NewSheetRows = 1 To 1000
Cells(NewSheetRows, 1).Value = _
"GLEP" & Format(Date, "yyyymmdd") & _
Format(NewSheetRows, "000000")
Next
End Sub
Hope this helps,
Tom
On 2002-04-25 00:25, TsTom wrote:
ActiveCell.End(xlDown)
is the problem...
To which row in Column A are you going to place this data?
GLEPyyyymmdd000002
Will it be based upon how many rows of data are in the original sheet?
Tom
Sub MyNewWorksheet()
Dim Wks As Worksheet
Dim iCount As Integer
Dim NewSheetRows As Long
'Initialise counter to number of worksheets
iCount = Worksheets.Count
'Copy first worksheet after last worksheet and increase count
Worksheets(1).Copy After:=Worksheets(iCount)
iCount = iCount + 1
'Assign last worksheet to Wks
Set Wks = Worksheets(iCount)
Wks.Name = "Complete"
Range("A2").Activate
'Range(ActiveCell, ActiveCell.End(xlDown)).Select
'change 1 to your first row
'change 1000 to your last row
For NewSheetRows = 2 To Range("B1:B" & Range("B65536").End(xlUp).Row).Rows.Count
Cells(NewSheetRows, 1).Value = _
"GLEP" & Format(Date, "yyyymmdd") & _
Format(NewSheetRows, "000000")
Next
End Sub