Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Copy one worksheet to another.

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Singapore
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    Singapore
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    New Member
    Join Date
    Apr 2002
    Location
    Singapore
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    New Member
    Join Date
    Apr 2002
    Location
    Singapore
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    New Member
    Join Date
    Apr 2002
    Location
    Singapore
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    New Member
    Join Date
    Apr 2002
    Location
    Singapore
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •