Copy one worksheet to another.

hgg_21

New Member
Joined
Apr 23, 2002
Messages
17
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.

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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top