Hello Guys !
I have a macro that uses inputbox to search for a column name (it's a data) and select a range of data from that column. I would like it to paste this range into a second file into a column with the same name in the range I indicated. However, I don't know exactly how to do it, Do you have some suggestions.
Below is my code:
I have a macro that uses inputbox to search for a column name (it's a data) and select a range of data from that column. I would like it to paste this range into a second file into a column with the same name in the range I indicated. However, I don't know exactly how to do it, Do you have some suggestions.
Below is my code:
Rich (BB code):
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 = ThisWorkbook
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"
'Open file
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'
Set ws = data_wb.Sheets("Final")
''' 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