help on selecting from 1st visible cell to last with VBA please

[chip666]

this is a macro I created to select cells and do some tricks on them:

Sub MDB()
'
' MDB Macro
' for MDB
'

'
Range("D77:D249").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Range("B1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-1],"".pdf"")"
Range("B1").Select
Selection.Copy
Range("B1:B60").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("B1:B60").Select
Application.CutCopyMode = False
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Replace What:="-", Replacement:="_", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub


the thing is that the range D77 to D249 won't be the same for each spreadsheet
it's a range of visible cells I need to select

and them, I need to perform different things on the copied result from 1st row of column B to the last row
In this case from row 1 to row 60 (but should be last row

please help!
thank you
chip666

 

[Fluff]

How about

Sub MDB()
'
' MDB Macro
' for MDB
'

'
Range("D77:D50000").SpecialCells(xlCellTypeVisible).Copy
Workbooks.Add
ActiveSheet.Paste
With Range("B1", Range("A" & Rows.count).End(xlUp).Offset(, 1))
   .FormulaR1C1 = "=CONCATENATE(RC[-1],"".pdf"")"
   .Copy
End With
Workbooks.Add
Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
   :=False, Transpose:=False
Application.CutCopyMode = False
Range("A:A").Replace What:="-", Replacement:="_", LookAt:=xlPart, _
   SearchOrder:=xlByRows, MatchCase:=False
End Sub

 

[chip666]

How about

Sub MDB()
'
' MDB Macro
' for MDB
'

'
Range("D77:D50000").SpecialCells(xlCellTypeVisible).Copy
Workbooks.Add
ActiveSheet.Paste
With Range("B1", Range("A" & Rows.count).End(xlUp).Offset(, 1))
   .FormulaR1C1 = "=CONCATENATE(RC[-1],"".pdf"")"
   .Copy
End With
Workbooks.Add
Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
   :=False, Transpose:=False
Application.CutCopyMode = False
Range("A:A").Replace What:="-", Replacement:="_", LookAt:=xlPart, _
   SearchOrder:=xlByRows, MatchCase:=False
End Sub

I just changed the Range("D77:D50000") to Range("D1:D50000")
because it's not all workbook that will start at this row.

But from what I see, it worked with all the workbooks so far. Thank you very much

 

[Fluff]

Glad to help & thanks for the feedback