dynamic select in VBA-excel

Question

I am trying to automate a copy & paste job basically. I want to bring over data from one document into another. I want to find the data based on the content in a cell which is not always in the same place and I want to select values below that cell up to the next blank row.

For example: Select all cells in a range below the cell that says "CURRENT MONTH" until the next row that is blank.

This is what I have so far:

Sub getCurrentMonth()

'get the current month data
Windows("File1.xlsm").Activate
Sheets("Sheet1").Select
celltxt = ActiveSheet.Range("B1:B1000").Text
If InStr(1, celltxt, "CURRENT MONTH") Then
N = Cells(7, 2).End(xlDown).Select
Range("B7:AD" & N).Select
Selection.Copy
Windows("Automation.xlsm").Activate
Sheets("Sheet1").Select
Range("A2").Select
ActiveSheet.Cells(rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial
Else
MsgBox ("No data for Current Month Found")
End If    

End Sub

What is the problem with your code? What is your specific question? I suggest recording the use of the Find feature and then exploring it, and adding to it. — Andy G
– Andy G, Commented Jan 16, 2018 at 15:24
Or is the value "CURRENT MONTH" always in the same column? And is it always the first occupied cell of the column? — Andy G
– Andy G, Commented Jan 16, 2018 at 15:25
It is always in the same column, but it is not always the first occupied cell in that column. It moves up and down based on the data that is printed above it. — hellosimoni
– hellosimoni, Commented Jan 16, 2018 at 15:29
If CURRENT MONTH is found is the data in a contiguous block/table below the cell? With no blank cells in col B of the table (below it)? — Andy G
– Andy G, Commented Jan 16, 2018 at 15:34
Use Set rngCell = ActiveSheet.Range("B:B").Find(What:="CURRENT MONTH", LookAt:=xlWhole).Offset(1,0) to retrieve the cell (it looks in the entire column for a cell whose text is exactly equal to "CURRENT MONTH"). Then use Range(rngCell.Address & ":" & rngCell.Address.End(xlDown).Address).Copy to copy the range. — Brownish Monster
– Brownish Monster, Commented Jan 16, 2018 at 15:41

Scott Holtzman · Accepted Answer · 2018-01-16 18:05:38Z

1

Give this a shot. It assumes that all data under Current Month is contiguous, based on your description. If it's not, let me know and I will edit.

Option Explicit

Sub getCurrentMonth()

    'get the current month data
    With Workbooks("File1.xlsm").Worksheets("Sheet1")

        Dim foundIt As Range
        Set foundIt = .Range("B1:B1000").Find("CURRENT MONTH", lookat:=xlWhole)

        If Not foundIt Is Nothing Then

            Set foundIt = .Range(foundIt.Offset(1,-1), foundIt.End(xlDown)) 'from column A and down
            Set foundIt = foundIt.Resize(foundIt.Rows.Count,29) 'from column A to AD
            Workbooks("Automation.xlsm").Worksheets("Sheet1").Range("A2").Resize(foundIt.Rows.Count, foundIt.Columns.Count).Value = foundIt.Value

        Else

            MsgBox ("No data for Current Month Found")

        End If

    End With

End Sub

edited Jan 16, 2018 at 18:05

answered Jan 16, 2018 at 15:39

Scott Holtzman

27.3k5 gold badges41 silver badges76 bronze badges

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

8 Comments

hellosimoni Over a year ago

This worked to get one column, thanks! How would I get the entire row selected and copied for each?

Scott Holtzman Over a year ago

@hellosimoni - do you want the entire row? Or just rows with data? If just rows with data how many columns will it be? Or is it a contiguous set of columns (with no blanks cells until the end of the data set)?

hellosimoni Over a year ago

Yes, I need the entire row for each row of data below "CURRENT MONTH" the columns are contiguous. It goes from Column A-AD but some columns are hidden, which I don't want returned.

Scott Holtzman Over a year ago

@hellosimoni - some columns are hidden which I don't want returned - if you want to skip certain columns of the data this make it much more complex. It also should have been noted in your initial question. How many columns do you not want to show and which columns are they?

hellosimoni Over a year ago

Sorry I forgot. We can start with just returning the entire row. I have another sub that already prints only my unhidden columns which I should be able to figure it out. But Column A, F, G, M, P, Q, R, S, T, U, W, X are hidden The code that I use to get the past month's data is below. This doesn't work for the CURRENT MONTH because the cell where that text is moves based on how many rows of past month data there are.

|

Collectives™ on Stack Overflow

dynamic select in VBA-excel

1 Answer 1

8 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Related