Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts

    Still struggling with VBA for copying a sheet

    I have the following code that doesn't work.

    I have two workbooks. I want to add a workbook in "ReviewMeeting" at the end and make it's name "b4"&q2 from "NewIncidentB" workbook.

    That seems fine.

    Then, I want to select the range "A1:L46" from "ReviewMeeting", existing sheet "Page2", copy it and paste the column widths and then the VALUES into the newly created sheet.
    That's where the problem is.

    Basically, I want to make a copy of Page2 and add it as a new sheet with a new name. Probably there's an easier way than my round about method.


    What's wrong or what's the RIGHT way to do this? I'm exhausted working on this.

    Sub add_sheet()
    Dim strNewSheet
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Set wb1 = Workbooks("NewIncidentB.xlsm")
    Set wb2 = Workbooks("ReviewMeeting.xlsx")
    Set ws1 = wb1.Sheets("NewIncident")
    strNewSheet = ws1.Range("b4") & ws1.Range("q2")
    Set ws2 = wb2.Sheets.Add(After:=wb2.Worksheets(wb2.Worksheet s.Count))
    ws2.Name = strNewSheet
    wb2.Sheets("Page2").Select
    Range("A1:L46").Select
    Selection.Copy
    ws2.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste
    Range("C3").Select
    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Code:
    Option Explicit
    Sub add_sheet()
      Dim wb1 As Workbook
      Dim wb2 As Workbook
      Dim ws1 As Worksheet
      Dim ws2 As Worksheet
      
      Set wb1 = Workbooks("NewIncidentB.xlsm")
      Set wb2 = Workbooks("ReviewMeeting.xlsx")
      Set ws1 = wb1.Sheets("NewIncident")
      
      With wb2
        Set ws2 = .Sheets.Add(After:=.Worksheets(.Worksheets.Count))
        .Sheets("Page2").Range("A1:L46").Copy
      End With
      With ws2.Range("A1")
        .PasteSpecial Paste:=xlPasteColumnWidths
        .PasteSpecial Paste:=xlPasteValues
      End With
      ws2.Name = ws1.Range("b4") & ws1.Range("q2")
    End Sub
    Your problem was trying to pastespecial on a worksheet without indicating the starting cell. I also cleaned up the code to eliminate selecting and also eliminating an unneeded variable. You could probably elminate the workbook variables since they are each only used once...

    Steve
    Last edited by sdckapr; 2012-07-06 at 16:24.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    THANKS, Steve. I think I'm almost there with your help.

    Error on this line: ws2.Name = ws1.Range("b4") & ws1.Range("q2")

    I suspect I need to activate the ws1 or wb1 before running that line?

    If so, is that: wb1.Activate ??

    (actually, just tried that and it wasn't the solution...clearly missing something else)
    Last edited by kweaver; 2012-07-06 at 10:12.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    What is the error?When the code errors, what is ws2? what is the ws1.range("b4")? What is ws1.range("q2")?

    I suspect that the worksheet name may not be valid (or is a duplicate name perhaps)

    Steve

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    You were right...I had a name issue. I think it's OK now. GREAT! Phew!

Posting Permissions

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