Copy one worksheet to another.

[hgg_21]

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

[Yogi Anand]

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

Hi hgg_21:
You can copy the data and all the formatting from one worksheet into a new worksheet all in one step. While in Worksheet sheet1, say ...
1. EDIT|Move_or_COPY_Sheet
2. in the To_book: box selectBook1, e.g. if you want the new worksheet to be in the same workbook
3. in the Before_sheet: box, make appropriate selection
4. check Create_a_copy check box if you want to create a copy of the source worksheet, if you do not check this check box, the source worksheet will be moved to the new location.

That's it!

HTH

Please post back if it works for you ... otherwise explain a little furthe r and let us take it from there!



_________________
Yogi Anand
Edit: Deleted reference to inactive web site from signature line

[ This Message was edited by: Yogi Anand on 2003-01-19 18:35 ]

[hgg_21]

Hi,

thanks for replying.
I m sorry for not specifying my requirements.

I want to create a macro to create a new worksheet and then paste some data from an old worksheet into the newly create worksheet.

This is what I have done, it doesn't seems to work. Please have a look as I m very new to VBA, sorry if I ask some really easy questions.

Thanks


Sub MyNewWorksheet()
Dim wsNewWorkSheet As Worksheet

Set wsNewWorkSheet = Worksheets.Add
wsNewWorkSheet.Name = "Complete"

Selection.Copy
Range("A1:CT3348").Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

[hgg_21]

Hi,

I managed to solve the copying already.
Now in my new worksheet, I want to insert data into my 1st column. For example
I want to insert GELPyyyymmdd000001 in the
1st row, GLEPyyyymmdd000002 in the 2nd row and so on.
yyyymmdd is the system date.
Can I have some ideas on how to do it?

THanks!

[Tom Schreiner]

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

[hgg_21]

hi,

thanks for your reply,

I have changed my code to this:

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 = 1 To ActiveCell.End(xlDown)
Cells(NewSheetRows, 1).Value = _
"GLEP" & Format(Date, "yyyymmdd") & _
Format(NewSheetRows, "000000")
Next
End Sub

But it still cant work.
Can u help me? thanks
One more thing, how do u create and delete columns?

[Tom Schreiner]

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

[hgg_21]

Hi Tom,

I want to create this marco that will insert the values from the second line. But values starts from 1. Also I do not know the no. or rows. Can that be done?

THanks

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.

Hope this helps,
Tom

[hgg_21]

Yes it will be based upon how many rows of data in the original sheet.

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

[Tom Schreiner]

Well?
Try this and see what happens...
I do not know how to base your number of rows on the new sheet because you have not given me a criteria.
In this example, I'm using column B to figure how far to run your data in column A

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

Tom