Copy,Paste & rename new worksheet to end of workbook

[jiggaman84]

Hello,

I have just written my first macro and I was wondering if i could get a little help. It's pretty messy and for sure not as efficient as possible but for the most part it does the trick. I recorded most of it and then modified it using helpful threads I found on this website. However, i am finding that sometimes the macro does not do what it is supposed to.

The macro is supposed to...

- Copy a number of entries from the "Master" spreadsheet and paste them to the "Template" spreadsheet.
-Where the entries are pasted onto the "Template" sheet depends on the size of each entry (by square feet).
-The entries to copy (from "Master" sheet) are based on a value in the "Tempate" sheet (B5)

- Once the data is pasted, the macro should make a copy of the Template sheet and paste a new version of the Sheet at the very end of the workbook, while renaming the new sheet to the value in B4 (in the new sheet).

That is the gist of it...
I have noticed that the macro sometimes just copies all entries from "Master" sheet and pastes them into the template, ignoring my commands to autofilter and paste by size. Not sure why this happens, but once it does happen, it will continue to occur.

Here is my code... It's amature, I know. And I am sure there are a lot of redundancies. I hope you can follow it.

Thanks!

' Macro1 Macro
'
Sheets("Template").Select
Range("A15:F27,A37:F61,A71:F120").Select
Selection.ClearContents
Sheets("Master").Select
Sheets("Master").Range("$A$19:$S$6031").AutoFilter Field:=2, Criteria1:=Sheets("Template").Range("B5").Value
Sheets("Master").Range("$A$19:$S$6031").AutoFilter Field:=5, Criteria1:=">=30000"
Sheets("Master").Select
Range("C20:H6031").Select
Selection.Copy
Sheets("Template").Select
Range("A15").Select
Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Master").Select
Selection.AutoFilter

Sheets("Master").Select
Sheets("Master").Range("$A$19:$S$6031").AutoFilter Field:=2, Criteria1:=Sheets("Template").Range("B5").Value
Sheets("Master").Range("$A$19:$S$6031").AutoFilter Field:=5, Criteria1:=">=10000", Operator:=xlAnd, Criteria2:="<30000"
Sheets("Master").Select
Range("C20:H6031").Select
Selection.Copy
Sheets("Template").Select
Range("A37").Select
Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sorted Rent Roll with BUs (2)").Select
Selection.AutoFilter

Sheets("Master").Select
Sheets("Master").Range("$A$19:$S$6031").AutoFilter Field:=2, Criteria1:=Sheets("Template").Range("B5").Value
Sheets("Master").Range("$A$19:$S$6031").AutoFilter Field:=5, Criteria1:="<10000"
Sheets("Master").Select
Range("C20:H6031").Select
Selection.Copy
Sheets("Template").Select
Range("A71").Select
Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Master").Select
Selection.AutoFilter


Sheets("Template").Select
Sheets("Template").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Sheets(Sheets.Count).Range("B4").Value
Sheets("Template").Select
Range("B4").Select




End Sub

 

[dave3009]

Does this help

Sub ggg()
' Macro1 Macro
'
myarray = Array("15, 37, 71")
Sheets("Template").Range("A15:F27,A37:F61,A71:F120").ClearContents

For Each c In myarray
    Sheets("Master").Range("$A$19:$S$6031").AutoFilter Field:=2, Criteria1:=Sheets("Template").Range("B5").Value
    Sheets("Master").Range("$A$19:$S$6031").AutoFilter Field:=5, Criteria1:=">=30000"
    Sheets("Master").Range("C20:H6031").SpecialCells(xlCellTypeVisible).Copy
    Sheets("Template").Range("A" & c).PasteSpecial xlPasteFormulasAndNumberFormats
    Sheets("Master").AutoFilter
Next c

Sheets("Template").Copy After:=Sheets(Sheets.Count)

With Sheets(Sheets.Count)
    .Name = .Range("B4").Value
End With
Sheets("Template").Range("B4").Activate

End Sub

 

[jiggaman84]

Hey Dave,

Thanks for your help. I ran the macro and apparently there is an error with this line:

Sheets("Template").Range("A" & c).PasteSpecial xlPasteFormulasAndNumberFormats

(Run-time error '1004': Application-defined or object-defined error).

I do appreciate the help

 

[dave3009]

Maybe replace c with c.Value

 

[jiggaman84]

No luck, but I appreciate the effort

 

[dave3009]

OK, back to what I was going to post

' Macro1 Macro
'
Sheets("Template").Range("A15:F27,A37:F61,A71:F120").ClearContents
    Sheets("Master").Range("$A$19:$S$6031").AutoFilter Field:=2, Criteria1:=Sheets("Template").Range("B5").Value
    Sheets("Master").Range("$A$19:$S$6031").AutoFilter Field:=5, Criteria1:=">=30000"
    Sheets("Master").Range("C20:H6031").SpecialCells(xlCellTypeVisible).Copy
    Sheets("Template").Range("A15").PasteSpecial xlPasteFormulasAndNumberFormats
    Sheets("Master").AutoFilter
Sheets("Master").Range("$A$19:$S$6031").AutoFilter Field:=2, Criteria1:=Sheets("Template").Range("B5").Value
    Sheets("Master").Range("$A$19:$S$6031").AutoFilter Field:=5, Criteria1:=">=10000"
    Sheets("Master").Range("C20:H6031").SpecialCells(xlCellTypeVisible).Copy
    Sheets("Template").Range("A37").PasteSpecial xlPasteFormulasAndNumberFormats
    Sheets("Master").AutoFilter
Sheets("Master").Range("$A$19:$S$6031").AutoFilter Field:=2, Criteria1:=Sheets("Template").Range("B5").Value
    Sheets("Master").Range("$A$19:$S$6031").AutoFilter Field:=5, Criteria1:="<10000"
    Sheets("Master").Range("C20:H6031").SpecialCells(xlCellTypeVisible).Copy
    Sheets("Template").Range("A71").PasteSpecial xlPasteFormulasAndNumberFormats
    Sheets("Master").AutoFilter
Sheets("Template").Copy After:=Sheets(Sheets.Count)
With Sheets(Sheets.Count)
    .Name = .Range("B4").Value
End With
Sheets("Template").Range("B4").Activate

 

[jiggaman84]

Dave, you did it! Thanks so much for all of your help

 

[dave3009]

Sorry about the first bit, it seems I wasn't thinking all that straight.

The macro recorder is good but it records soooooo much unecessary stuff, most of it can be simplified. The code I posted is exactly yours but trimed down.