Paste values in dynamic range excel vba

Question

I am writing a script where I want to enable a search in a Database, presenting the results of the search queries in a different worksheet (which I have named Results), so that users do not have access to the whole database at the same time.

In order to do this I want to copy values from the "Database" worksheet into the "Results" worksheet. I have succeeded in selecting the right data from the "Database", in respect to any specific search criteria. I did this with the following code:

With Sheets("Database")
   .Range(.Cells(i, 1), .Cells(i, 9)).Copy
End With

Now I want to paste the results into the "Results" spreadsheet and I have done so by writing the following:

Sheets("Results").Range("B600").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats

By doing this, I don't quite understand:

if I have strictly defined the paste range as between the first empty row and B600 or;
if I am just defining the beginning of the paste range and, in the case that the search results exceed the 600th row, they will still be pasted after this row.

I ask this because, as the database grows, I will certainly need to guarantee a paste range greater than B600.

I have researched on it but cannot seem to be absolutely sure of what I have done exactly. I must say that I know that the first empty row in the "Results" database will always be 12. In this case, I know that I basically want to paste the search results from the 12th row on. Maybe there is a more straight-forward way to do this.

This is the entire code, for reference:

Private Sub SearchButton_Click()

'This is the search function

'1. declare variables
'2. clear old search results
'3. Find records that match criteria and paste them

Dim country As String
Dim Category As String
Dim Subcategory As String
Dim finalrow As Integer
Dim i As Integer 'row counter


'Erase any entries from the Results sheet
Sheets("Results").Range("B10:J200000").ClearContents

'Deformat any tables in the Results sheet
For Each tbl In Sheets("Results").ListObjects
    tbl.Clear

    Next

'Define the user-inputed variables
country = Sheets("Results").Range("D5").Value
Category = Sheets("Results").Range("D6").Value
Subcategory = Sheets("Results").Range("D7").Value
finalrow = Sheets("Database").Range("A" & Rows.Count).End(xlUp).Row

'If statement for search

'For every variable i, start comparing from row 2 until the final row
For i = 2 To finalrow

    'If the country field is left empty
    If country = "" Then
        Sheets("Results").Range("B10:J200000").Clear
        MsgBox "You must select a country in order to search the database. Please do so in the drop-down list provided."
        Sheets("Results").Range("D5").ClearContents
        Sheets("Results").Range("D6").ClearContents
        Sheets("Results").Range("D7").ClearContents
        Exit Sub

    'If the country field is filled in and there results from the search made
    ElseIf Sheets("Database").Cells(i, 1) = country And _
        (Sheets("Database").Cells(i, 3) = Category Or Category = "") And _
        (Sheets("Database").Cells(i, 4) = Subcategory Or Subcategory = "") Then

            'Copy the headers of the table
            With Sheets("Database")
            .Range("A1:I1").Copy
            End With
            Sheets("Results").Range("B10:J10").PasteSpecial

            'Copy the rows of the table that match the search query
            With Sheets("Database")
            .Range(.Cells(i, 1), .Cells(i, 9)).Copy
            End With
            Sheets("Results").Range("B600").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats

    'Hides search form
    Me.Hide

    End If

Next i

'Toggle Results sheet
Sheets("Results").Activate

'Format results as a table
Set rng = Range(Range("B10"), Range("B10").End(xlUp).SpecialCells(xlLastCell))
Set table = Sheets("Results").ListObjects.Add(xlSrcRange, rng, , xlYes)
table.TableStyle = "TableStyleMedium13"

Range("B11").Select

'Make Excel window visible
Application.Visible = True

End Sub

Thank you very much for your help.

will you always be pasting to the exact same range in the "Results" worksheet? — mojo3340
– mojo3340, Commented Nov 21, 2016 at 11:04
and what is the range of the data in the "Database" worksheet? — mojo3340
– mojo3340, Commented Nov 21, 2016 at 11:05
Sheets("Results").Range("B600").End(xlUp) is the first cell above "B600" that has data inside it. The question is where do you want to Paste the copied data ? last occupied row (below "B600") , where ? — Shai Rado
– Shai Rado, Commented Nov 21, 2016 at 11:07
the Paste is more about the start location, the size of the Range that you Copy>Paste is determined in the Copy command, in the Range you define — Shai Rado
– Shai Rado, Commented Nov 21, 2016 at 11:14
Yes, but they will overwrite the data in rows 600 and beyond — Shai Rado
– Shai Rado, Commented Nov 21, 2016 at 11:17

Robin Mackenzie · Accepted Answer · 2016-11-21 11:14:39Z

2

You can count from the bottom of the sheet upto the last used cell in column B, and then OFFSET by 1 row. This prevents you needing to worry about

a) that the range to paste to starts from row 12 (they should contain values), and

b) that you are currently using a hard-coded 'anchor' of B600 which will need updating as the data grows.

Sample code:

Dim ws As Worksheet
Dim rngColumnBUsed As Range
Dim lngFirstEmptyRow As Long

Set ws = ThisWorkbook.Sheets("Results")
Set rngColumnBUsed = ws.Range("B" & ws.Rows.Count).End(xlUp).Offset(1, 0)
lngFirstEmptyRow = rngColumnBUsed.Row

answered Nov 21, 2016 at 11:14

Robin Mackenzie

19.4k7 gold badges42 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shai Rado Over a year ago

why did you need to set a Range and then take the row from it? couldn't you just find the last row and add 1 ? lngFirstEmptyRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row + 1

Robin Mackenzie Over a year ago

Yes, your example is more succinct. I am a bit of a sucker for writing out code examples step-by-step :)

franciscofcosta Over a year ago

Thank you for your answer. I still don't understand how I would go about integrating this in the paste part of the code. After putting your sample code in mine, how would I amend this?: End With Sheets("Results").Range("B600").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats

user6432984 · Accepted Answer · 2016-11-21 12:34:40Z

Two ListObjects tblDatabase and tblResults
tblResults data gets cleared
A filter is applied to the second, third and fourth columns of tblDatabase
If there are less than 588 results, we copy the filtered records from tblDatabase to tblResults
If there are more than 588 results then we resize the filtered records' range down to the first 588 records and then copy them to tblResults
We never worry about formatting because tblResults keeps it's original format.

Sub ListObjectDemo()
    Dim tblDatabase As ListObject, tblResults As ListObject

    Set tblDatabase = Worksheets("Database").ListObjects("tblDatabase")
    Set tblResults = Worksheets("Results").ListObjects("tblResults")
    If Not tblResults.DataBodyRange Is Nothing Then tblResults.DataBodyRange.ClearContents

    With tblDatabase.Range
        .AutoFilter Field:=2, Criteria1:="Test A"
        .AutoFilter Field:=3, Criteria1:="East"
        .AutoFilter Field:=4, Criteria1:="Algeria"
    End With

    With tblDatabase.DataBodyRange
        If .Rows.Count <= 588 Then
            .Copy tblResults.ListRows.Add.Range
        Else
            .Resize(588).Copy tblResults.ListRows.Add.Range
        End If
    End With

End Sub

ravi · Accepted Answer · 2016-11-21 11:23:42Z

1

Dim searchdata as range, inputfromuser as string

inputfromuser = inputbox("type what you wanna search")

set searchdata = sheets("Database").find(inputfromuser).select

searchdata = activecell.value or activecell.offset(10,5).value

sheets("results").activate

with sheets("result")

range("a12",range("a12").end(xldown)).offset(1,0).select

searchdata.copy destination:= activecell

activecell.offset(1,0).select

end with

Not sure, if I understood you corectly mate.

I dont haveexcel sheet or VBE editor. Just wrote this directly on website. Pls amend as per your need.

answered Nov 21, 2016 at 11:23

ravi

11 bronze badge

Collectives™ on Stack Overflow

Paste values in dynamic range excel vba

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related