CODEWITHSUNDEEP
vbaexcel
Swindo
Swindo

Reputation: 1

Using a cell reference in VBA

I would like to copy a range from a filtered table to another sheet. The starting point is easy however I would like to only copy a number of rows down based on a cell that a user enters into. I can make it work when I hard code the number however I would like to make this based on a cell.

The variable lastrow is where I need to have the cell H4. (H4 in my spreadsheet is where the user keys in the number of rows to copy)

My code thus far is:

Sub Line()

Dim Copyrange As String
Dim lastrow As Range

Startrow = 8
lastrow = 10
Let Copyrange = "B" & Startrow & ":" & "H" & lastrow
Range(Copyrange).Select
End Sub

Any help is really appreciated

Upvotes: 0

Views: 465

Answers (2)

WGS
WGS

Reputation: 14179

Changing my answer to the following instead. Not the best of solutions, as working with SpecialCells is tricky at best, but this might suit your needs fairly well.

Sub Illusion()

Dim Source, Dummy As Worksheet
Dim SRow, LRow As Long

Application.ScreenUpdating = False

With ThisWorkbook
    Set Source = .ActiveSheet
    Set Dummy = .Sheets.Add(After:=Sheets(Sheets.Count))
End With

SRow = 8
LRow = Range("H4").Value + 1

'Change the H10000 below to the correct end row of your unfiltered table.
Source.Range("B" & SRow & ":H10000").SpecialCells(xlCellTypeVisible).Copy

With Dummy
    'We create an illusion that we copy only the names we need by bridging using a dummy sheet and copying from there.
    .Range("A1").PasteSpecial xlPasteValues
    .Range("A1:A" & LRow).Copy
    'Paste to desired location as values as well.
    .Delete
End With

Application.ScreenUpdating = True

End Sub

What I did here was, we copy the visible cells into a dummy sheet, adjust the number of names to copy from that dummy sheet and do whatever you want with it, then delete the dummy sheet. It's a quick and dirty, but it beats having to go to the intricacies of SpecialCells.

Upvotes: 0

mattboy
mattboy

Reputation: 2910

Sounds like all you need is this?

Let Copyrange = "B" & Startrow & ":" & "H" & Range("H4")

Upvotes: 1

Related Questions