jiggaman84

New Member
Joined
Jan 22, 2010
Messages
17
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
Does this help

Code:
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
 
Last edited:

jiggaman84

New Member
Joined
Jan 22, 2010
Messages
17
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
 

jiggaman84

New Member
Joined
Jan 22, 2010
Messages
17

ADVERTISEMENT

No luck, but I appreciate the effort
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
OK, back to what I was going to post

Code:
' 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
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,147
Messages
5,657,100
Members
418,355
Latest member
michaelirl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top