CODEWITHSUNDEEP
vbaexcel
Matthew Paulin
Matthew Paulin

Reputation: 59

Copy contents of current worksheet to vba created worksheet

I'm tearing my hair out trying to do what should be a really simple adjustment to a macro.

copy and paste doesn't seem to work. I get a property not supported error.

All I am trying to do is copy all cell contents from the original activesheet in the originating workbook (which will be sName) and paste it to the new workbook sheet(rvname)

Here is my current code: (I need it to work in excel 2003 and 2007)

Sub create_format_wb()
    'This macro will create a new workbook
    'Containing sheet1,(job plan) Original, (job plan) Revised, and 1 sheet for each task entered in the inputbox.


Dim Newbook As Workbook
Dim i As Integer
Dim sName As String
Dim umName As String
Dim rvName As String
Dim tBox As Integer
Dim jobplannumber As String
Dim oldwb As String


line1:
tBox = Application.InputBox(prompt:="Enter Number of Tasks", Type:=1)
If tBox < 1 Then
MsgBox "Must be at least 1"
GoTo line1
Else

sName = ActiveSheet.Name
umName = (sName & " Original")
rvName = (sName & " Revised")
jobplannumber = sName




Set Newbook = Workbooks.Add
    With Newbook
        .Title = sName
        .SaveAs Filename:=(sName & " .xls")
        .Application.SheetsInNewWorkbook = 1
        .Sheets.Add(, After:=Sheets(Worksheets.Count)).Name = umName
        Worksheets(umName).Range("A1").Select
        With Worksheets(umName).QueryTables.Add(Connection:= _
            "ODBC;DSN=MX7PROD;Description=MX7PROD;APP=Microsoft Office 2003;WSID=USOXP-93BPBP1;DATABASE=MX7PROD;Trusted_Connection=Yes" _
            , Destination:=Range("A1"))
            .CommandText = Array( _
            "SELECT jobplan_print.taskid, jobplan_print.description, jobplan_print.critical" & Chr(13) _
            & "" & Chr(10) & "FROM MX7PROD.dbo.jobplan_print jobplan_print" & Chr(13) & "" & Chr(10) _
            & "WHERE (jobplan_print.jpnum= '" & jobplannumber & "' )")
            .Name = "Query from MX7PROD"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .Refresh BackgroundQuery:=False
        End With
            .Worksheets(umName).UsedRange.Columns.AutoFit
            .Sheets.Add(, After:=Sheets(Worksheets.Count)).Name = rvName
            For i = 1 To tBox
                .Sheets.Add(, After:=Sheets(Worksheets.Count)).Name = ("Task " & i)
            Next i
    End With


    Worksheets(rvName).UsedRange.Columns.AutoFit
End If
End Sub

Can somone walk me through how to go about this?

Any help appreciated.

Upvotes: 0

Views: 23445

Answers (3)

Zenadix
Zenadix

Reputation: 16279

To copy the contents of a worksheet to another existing worksheet:

wsDest.UsedRange.Clear 'Clear the contents of the destination sheet
wsSource.UsedRange.Copy Destination:=wsDest.Range("A1")

Where wsSource and wsDest are the source and destination worksheets respectively.

Upvotes: 2

rory.ap
rory.ap

Reputation: 35318

You could do something like this:

Sub Copy()
    Workbooks("Book1").Worksheets("Sheet1").Cells.Copy
    Workbooks("Book2").Worksheets("Sheet1").Range("A1").Select
    Workbooks("Book2").Worksheets("Sheet1").Paste
End Sub

FYI, if you record macros in Excel while doing the operations you'd like to code, you can often get what you're looking for with minimal modifications to the automatically-generated macro code.

Upvotes: 2

Joe Mahley
Joe Mahley

Reputation: 1

Would you consider copying the worksheet as a whole? Here's a basic example, you'll have to customize your workbook organization and naming requirements. 

Sub CopyWkst()
    Dim wksCopyMe As Worksheet

    Set wksCopyMe = ThisWorkbook.Worksheets("Sheet1")
    wksCopyMe.Copy After:=ThisWorkbook.Sheets(Worksheets.Count)
    ThisWorkbook.ActiveSheet.Name = "I'm New!"

End Sub

Upvotes: 0

Related Questions