Code:
Sub DataBaseQuote()
Call Select_Last
Dim i As Integer
Dim ws As Worksheet
Dim sh As Worksheet
Dim lr As Integer
Dim ar As Variant
Set sh = Sheet14
For Each ws In ThisWorkbook.Worksheets
If ws.Range("R6") = False Then
If Left(ws.Name, 5) = "Quote" Then
ws.Range("D6:D62").AutoFilter 1, ">0"
lr = ws.Range("C" & Rows.Count).End(xlUp).Row
If lr >= 7 Then
Range("A7:F62").Select
Selection.Copy
Range("AA7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AE7:AF62").Select
Selection.NumberFormat = "$#,##0.00"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1").Select
Sheet14.Range("A65536").End(xlUp)(2).Value = ws.Name
ws.Range("AA7:AF62").Copy Sheet14.Range("C65536").End(xlUp)(2)
sh.Range(sh.Cells(sh.Cells(Rows.Count, 1).End(xlUp).Row, 1), _
sh.Cells(sh.Cells(Rows.Count, 3).End(xlUp).Row, 1)).Value = ws.Name
End If
ws.AutoFilterMode = False
End If
End If
ws.UsedRange.Calculate
Next ws
Application.Goto "startCell"
Application.ScreenUpdating = True
Application.Run "ProtectAll"
End Sub
I dont know what im doing wrong...It works perfectly as long as the Previous ranges are populated.
But lets say i skip the first 5 cells keeping them empty..and Start the Data entry on A5 B5 C5 D5 etc etc...it isnt putting the data over on the DataQuote sheet...Also AC7:AC62 should Resize correctly but thats not working also...Thoughts?