Copying, Pasting and Saving USED Range Only

[fluffynicesheep]

Hi,

I currently have a spreadsheet that contains a tab that I want to copy and paste into a new document. It'll then save, close and exit both documents.

The VBA below works fine at the moment, and does as I need .... apart from one thing ... the new spreadsheet goes up to column WQW, and therefore makes scrolling through it a bit of a nightmare!!

The report tab that it gets the data from will always go up to column EN only, and that's where I'd like the new document to finish. The number of rows could well change each time .... so basically I need my range to be all used rows up to column EN ... and that's what I need to go into the new spreadsheet.

If you can let me know the line that needs changing from below that'll be great!

Sub Save_and_Close()
Dim wbA As Workbook
Dim wbB As Workbook
Dim FName As String
  Sheets("Report").Select
Range("EM1").Value = Date
Range("EN1").Value = "Error Report -"
ActiveSheet.Range("A1").Select
    Application.ScreenUpdating = False

    Set wbA = ThisWorkbook

    wbA.Sheets("Report").Visible = True
   
    wbA.Sheets("Report").Copy

    Set wbB = ActiveWorkbook

    With wbB
   
        With .Sheets(1).UsedRange
            .Copy
            .PasteSpecial xlValues
            .PasteSpecial xlFormats
        End With
       
        Dim nm As Name
Dim DeleteCount As Long

'Loop through each name and delete
  For Each nm In ActiveWorkbook.Names
     
        If InStr(1, nm.RefersTo, "#REF!") Then nm.Delete
      On Error GoTo 0
    DeleteCount = DeleteCount + 1

Skip:
   
  Next

 
'Reset Error Handler
  On Error GoTo 0
     ActiveSheet.Range("A1").Select

        Application.CutCopyMode = False

FName = "Y:\National Careers Service - Mail Merge\E-Mail Attachments\Error Reports\Error Report - " & _
        Format(Range("EE2"), "dd-mm-yyyy") & ".xlsm"
       
   ActiveWorkbook.SaveAs Filename:=FName, _
                          FileFormat:=xlOpenXMLWorkbookMacroEnabled
        .Close SaveChanges:=False

    End With
     Application.ScreenUpdating = False
Sheets("Report").Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Cells(1, 1).Activate

Sheets("Dashboard").Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Cells(1, 1).Activate
ActiveCell.Next.Select

ThisWorkbook.Save
Application.DisplayAlerts = True
Application.Quit
End Sub

 

[Fluff]

If the new workbook goes upto column WQW, then so does the report sheet your copying.

 

[fluffynicesheep]

Hi, thanks Fluff.

Is there any way to therefore select and copy up to row EN only - as anything past this is blank cells, and not required in the new workbook.

thanks

 

[Fluff]

Why not just clean-up the report sheet, so that the used range only refers to actual data?
That would
a) remove this problem
b) possibly reduce the size of your workbook
c) possibly improve the workbooks performance.

Always best to remove the source of the problem rather then jump through hoops trying to circumvent it.

 

[CephasOz]

Have a look at this article: Determining worksheet data area

 

[fluffynicesheep]

Hi Fluff,

The report sheet is actually an exported document from an external CRM system and unfortunately I cannot clean this up ..... that's just the way the product exports. The top row also has merged columns in.

So at the moment my macro in workbook 2 is simply opening up this report sheet and copying all cells into the new workbook - Once copied into a new sheet, it then unmerges all, and I can start working on it. But the fact that I select all cells is where I think the issue is. Therefore I'm just trying to find a way to copy all of the rows (A1 to EN something) from the external workbook - every line that has data in column EN.

I think once I can get that range sorted ... it should work.

Thanks

 

[Fluff]

In that case, how about

        With .Sheets(1).UsedRange
            .Copy
            .PasteSpecial xlValues
            .PasteSpecial xlFormats
            .Parent.Range("EO:XFD").EntireColumn.Delete
        End With

 

[fluffynicesheep]

Lovely, that sorts it .... thanks so much!!

 

[Fluff]

You're welcome & thanks for the feedback.