1

Apologies in advance if this is a duplicated post - I searched for quite a while and couldn't find anything to solve my issue. I have an excel spreadsheet that I would like to duplicate into a new workbook, and then perform some functionality on the new workbook to remove various portions of the copied content while leaving the original unaltered.

I have the code working that will copy the sheet into a new workbook and save it, which is shown below:

Dim strFileName As String

'Copy sheet as a new workbook
Sheets("Sheet1").Copy

'SaveAs
strFileName = Application.GetSaveAsFilename(ActiveWorkbook.Name) & "xlsx"
If strFileName = "False" Then Exit Sub 'User Canceled
ActiveWorkbook.SaveAs Filename:=strFileName

My issue is in where to put the code that I'd like to use after the new workbook is created, and whether I need to put more code specifying which workbook or worksheet I'd like to perform the functionality in (maybe a .Activate or a .open?). Below is what code I've written to remove what data I don't want, which works fine if I run it inside the original worksheet rather than the duplicated copy:

'removes rows when criteria is met
x = 4
Do While Sheet1.Cells(x, 1).Value <> ""
    If (Sheet1.Cells(x, 1) = "Prospect") Or (Sheet1.Cells(x, 1) = "Lead") Then
    Sheet1.Rows(x).Delete
    Else
    x = x + 1
    End If
Loop

'removes rows when criteria is met
y = 4
Do While Sheet1.Cells(y, 5).Value <> ""
    If (Sheet1.Cells(y, 5) = "Non-exclusive") Then
    Sheet1.Rows(y).Delete
    Else
    y = y + 1
    End If
Loop

'removes columns
Sheet1.Columns("P:S").Delete
Sheet1.Columns("F").Delete

So in short how do I get this code working on the sheet I just created? Any help would be very much appreciated, and if I did not clarify something please feel free to ask!

Community
  • 1
  • 1
user3813768
  • 13
  • 1
  • 3
  • possible duplicate of [How to copy sheets to another workbook using vba?](http://stackoverflow.com/questions/6863940/how-to-copy-sheets-to-another-workbook-using-vba) – Isaac G Sivaa Jul 07 '14 at 19:58

1 Answers1

0

Set a reference to the sheet once you've copied it, and use that reference in your code:

Dim wb as Workbook, sht as WorkSheet
Dim strFileName As String

'Copy sheet as a new workbook
ActiveWorkbook.Sheets("Sheet1").Copy

Set wb = ActiveWorkbook
Set sht = wb.Sheets(1)

'SaveAs
strFileName = Application.GetSaveAsFilename(wb.Name) & ".xlsx"
If strFileName = "False" Then Exit Sub 'User Canceled
wb.SaveAs Filename:=strFileName

'removes rows when criteria is met
x = 4
Do While sht.Cells(x, 1).Value <> ""
    If sht.Cells(x, 1) = "Prospect" Or sht.Cells(x, 1) = "Lead" Then
    sht.Rows(x).Delete
    Else
    x = x + 1
    End If
Loop

'removes rows when criteria is met
y = 4
Do While sht.Cells(y, 5).Value <> ""
    If sht.Cells(y, 5) = "Non-exclusive" Then
    sht.Rows(y).Delete
    Else
    y = y + 1
    End If
Loop

'removes columns
sht.Columns("P:S").Delete
sht.Columns("F").Delete
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • This works great, thanks Tim! Only thing, for some reason my Save As isn't working properly - it brings up the SaveAs box where I type the desired file name and save location, but the workbook does not save and the new workbook remains the default name of "Book##". Notice anything that could be wrong? It was working before I added in the sheet references. – user3813768 Jul 07 '14 at 20:19
  • Forgot to mention, you accidentally left off a parenthesis when adding in the sheet references for the line: If sht.Cells(x, 1) = "Prospect") might want to add it in if anyone references my code. – user3813768 Jul 07 '14 at 20:22
  • Perfect! I noticed an error on my part for the SaveAs section: I shouldn't have included the "." when appending the file type so as is currently it saves as test1..xlsx but not a huge deal. Thanks again. – user3813768 Jul 07 '14 at 20:33