VBA Macro Help!

[chandler1983]

HI,

I am having a hard time putting something together.

What I need is to be able to copy cells a3 - to I23, copy them into a new excel file, then without prompts save the file as c:\newFiles.csv and overwrite (even if the file exists with no prompts)

I have the first portion, however can't seem to find the last part to save properly.

Sub TRIM()
Dim wsOld As Worksheet
Dim wsNew As Worksheet

Set wsOld = ActiveSheet
Set wsNew = Workbooks.Add(xlWorksheet).Sheets(1)

wsOld.Range("A3:A23").Copy wsNew.Range("A:A")
wsOld.Range("B3:B23").Copy wsNew.Range("B:B")
wsOld.Range("C3:C23").Copy wsNew.Range("C:C")
wsOld.Range("D3:D23").Copy wsNew.Range("D:D")
wsOld.Range("E3:E23").Copy wsNew.Range("E:E")
wsOld.Range("F3:F23").Copy wsNew.Range("F:F")
wsOld.Range("G3:G23").Copy wsNew.Range("G:G")
wsOld.Range("H3:H23").Copy wsNew.Range("H:H")
wsOld.Range("I3:I23").Copy wsNew.Range("I:I")

End Sub


Am I able to do this with one button or does this have to be a 2-step process?

 

[Fluff]

How about

Sub TRIM()
   Dim wsOld As Worksheet
   Dim wsNew As Worksheet
   
   Set wsOld = ActiveSheet
   Set wsNew = Workbooks.Add(xlWorksheet).Sheets(1)
   
   wsOld.Range("A3:I23").Copy wsNew.Range("A1")
   Application.DisplayAlerts = False
   With ActiveWorkbook
      .SaveAs fileName:= "C:\Newfile.csv", FileFormat:=xlCSV
      .Close
   End With
   Application.DisplayAlerts = True

End Sub

 

[chandler1983]

thank you! Much simpler than what I was working with, guess I was over-complicating this. thank you again!

 

[Fluff]

Glad to help & thanks for the feedback