How to copy rows of from one sheet to another sheet using vbscript

Question

Suppose I have Sheet(1) in an excel. Now i do also have 2500 rows which has data for the columns from A to BO.Now I want the data to copy from these sheet to another sheet of the same Excel file for 2500 rows but not the whole the columns,rather i need only columns from A to AA data to copy to the new sheet.

So how to frame it using VBscript?

Please help me.

This is also useful - http://stackoverflow.com/questions/10106465/excel-column-number-from-column-name — Arup Rakshit, Dec 07 '12 at 06:47

score 4 · Accepted Answer · answered Dec 06 '12 at 16:37

To copy data from one sheet to another you can use the Copy en PasteSpecial commands. To do this with a .vbs script do the following:

' Create Excel object
Set objExcel = CreateObject("Excel.Application")
' Open the workbook
Set objWorkbook = objExcel.Workbooks.Open _
    ("C:\myworkbook.xlsx")
' Set to True or False, whatever you like
objExcel.Visible = True

' Select the range on Sheet1 you want to copy 
objWorkbook.Worksheets("Sheet1").Range("A1:AA25").Copy
' Paste it on Sheet2, starting at A1
objWorkbook.Worksheets("Sheet2").Range("A1").PasteSpecial
' Activate Sheet2 so you can see it actually pasted the data
objWorkbook.Worksheets("Sheet2").Activate

If you want to do this in Excel with a VBS macro you can also call the copy and paste methods. Only your workbook object will be something like ActiveWorkbook

score 1 · Answer 2 · edited Jun 15 '15 at 11:22

This code is Working fine. Just Copy and paste it.

Dim CopyFrom As  Object
Dim CopyTo As Object
Dim CopyThis As Object
Dim xl As Object

xl = CreateObject("Excel.Application")
xl.Visible = False
CopyFrom = xl.Workbooks.Open("E:\EXCEL\From.xls")
CopyTo = xl.Workbooks.Open("E:\EXCEL\To.xls")
For i = 0 To 1
    ''To use a password: Workbooks.Open Filename:="Filename", Password:="Password"
    If i = 0 Then
        CopyThis = CopyFrom.Sheets(1)
        CopyThis.Copy(After:=CopyTo.Sheets(CopyTo.Sheets.Count))
        CopyTo.Sheets(3).Name = "Sheet3"
    Else
        CopyThis = CopyFrom.Sheets(2)
        CopyThis.Copy(After:=CopyTo.Sheets(CopyTo.Sheets.Count))
        CopyTo.Sheets(4).Name = "Sheet4"
    End If
Next
CopyTo.Sheets(1).Activate()
CopyTo.Save()
'CopyTo.SaveAs("E:\EXCEL\Check.xls")
xl.Quit()

score 0 · Answer 3 · edited Sep 15 '13 at 13:59

Sub buildMissingSheet(strMissingSheet)  'Just passing the missing sheet name in

' Master Sheet code
' Working on creating the "Master Sheet" at this time...May need to seperate the the code a little.
Dim GetRows1    As Worksheet
Dim GetRows2    As Worksheet
Dim PutRows     As Worksheet
Dim sglRowNum   As Single, i%


If strMissingSheet = strMASTERSHEET Then ' Create the strMASTERSHEET

  Set GetRows1 = Sheets(strRAWDATA)      ' These two sheets could be missing but will code around that later.
  Set GetRows2 = Sheets(strDATAWITH)     ' The two sheets I am getting rows from

' Just creating a new worksheet here assuming it is missing Worksheets.Add(After:=Worksheets(5)).Name = strMissingSheet Set PutRows = Sheets(strMissingSheet) ' Missing sheet must be created before declaring.

  PutRows.Select  'Select the sheet being built.

  With Cells(1, 1)
     .Value = strRAWDATA  'Not copying rows here but left it in this example anyway
    .AddComment
    .Comment.Visible = False
    .Select
    .Comment.Text Text:= _
       Chr(10) & "Name of sheet including header and the last 32 entries at the time this sheet was updated."
  End With

'Here is where we copy the whole row from one sheet to the other.

  GetRows1.Rows(1).Copy PutRows.Rows(2)  'Copy header row from existing sheet to "Master Sheet" for instance.
  GetRows1.Select
  sglRowNum = ReturnLastRow(ActiveSheet.Cells)  'return last row with data on active sheet

' I wanted the last few rows of data "32 rows" so found the end of the sheet this code can be found on the internet in several places including this site.

'Now the code you may have been looking for move 32 row of data from one sheet to another.

  For i = 1 To 32  'Start at row 3 on the Put sheet after sheet name and header.
     GetRows1.Rows(sglRowNum - (32 - i)).Copy PutRows.Rows(i + 2)
  Next i

end sub

started to format your code - lost patience ;-) Please edit and improve (a code line needs 4 leading spaces) — kleopatra, Sep 15 '13 at 14:00

How to copy rows of from one sheet to another sheet using vbscript

3 Answers3

Linked