VBA Excel Macro find column name and next need to copy range

Question

I have a part of macro which, after typing in the inputbox, finds the name of the column (date), after finding it, it needs to copy a given fragment of rows from this column, always after finding a given cell, it will be the same range. But i dont knot excatly how please take a look as below code

''' Put date input box  '''
    Sub Makro10()

Dim vDate As Date
Dim wbMe As Workbook
Dim data_wb As Workbook
Dim ws As Worksheet
Dim inputbx As String

'Set workbook'
    
Set wbMe = ActiveWorkbook
wbMe.Sheets("input_forecast").Rows("1:1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "YYYY-MM-DD"
    
Set wbMe = ThisWorkbook
file_name = Application.GetOpenFilename(Title:="Choose a target Workbook")
If file_name <> False Then

   'Set data file
   Set data_wb = Application.Workbooks.Open(file_name)
   
   'paste copy like value and change to date format'
   data_wb.Sheets("Final").Rows("1:1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "YYYY-MM-DD"
    'set our ws'

    
    
''' Put date input box  '''
    Do
        inputbx = InputBox("Date, FORMAT; YYYY-MM-DD")
        If inputbx = vbNullString Then Exit Sub
        On Error Resume Next
        vDate = CDate(inputbx)
        On Error GoTo 0
        DateIsValid = IsDate(vDate)
        If Not DateIsValid Then MsgBox "Please enter a valid date.", vbExclamation
    Loop Until DateIsValid
'COPY loop"
    Dim loc As Range, lc As Long
    With data_wb.Sheets("Final")
        Set loc = .Cells.Find(what:=Format(inputbx, "YYYY-MM-DD"))
        If Not loc Is Nothing Then
            lc = .Cells(loc.Row, Columns.Count).End(xlToLeft).Column
            .Range(.Cells(109, loc.Column), .Cells(123, lc)).Copy
        End If
    End With
    End If
End Sub

EDITED:

CDP1802 · Accepted Answer · 2021-09-24 15:05:48Z

1

With data_wb.Sheets("Final")
    Set loc = .Cells.Find(what:="2021-01-01")
    If Not loc Is Nothing Then
        .Cells(109, loc.Column).Resize(15).Copy
    End If
End With

alternative

    Dim loc As Range, lc As Long
    With data_wb.Sheets("Final")
        Set loc = .Cells.Find(what:=Format(inputbx, "YYYY-MM-DD"))
        If Not loc Is Nothing Then
            lc = .Cells(loc.Row, Columns.Count).End(xlToLeft).Column
            .Range(.Cells(109, loc.Column), .Cells(123, lc)).Copy
        End If
    End With

edited Sep 24, 2021 at 15:05

answered Sep 24, 2021 at 10:11

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

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

10 Comments

Przemek Dabek Over a year ago

And if I would like to select all the lines starting from 109 in loc.columns to the end of the sheet, 110 to the end of the sheet, 111 to the end of the sheet

Przemek Dabek Over a year ago

Its working, but maybe we can little edited for my above comments

Przemek Dabek Over a year ago

Yes, last visible value on the each row

Przemek Dabek Over a year ago

I meant that from the column we chose a poem until the end right. See the photo that I added in the post. Thanks for your help

CDP1802 Over a year ago

@Whyexcel Ok see update

|

Collectives™ on Stack Overflow

VBA Excel Macro find column name and next need to copy range

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related