VBA - Search sheet based on criteria, copy column, paste in the next colmun to right

Hi,

I'm currently relatively new to the VBA arena and am trying to solve a time consuming task via the help of VBA in Excel.

I have attached an very basic example of the excel data and layout I'm working with. This is what I need the code to do:

1. Search for the string in Sheet 1 (cell A2) and find it in Sheet 2.
2. Copy the entire column of the found column.
3. Paste the formulas/data in the column next to the found column.

Do you think there is anyone how could potentially help me with this problem?

Thanks in advance for the help.

Kind regards,

05125668

Re: VBA - Search sheet based on criteria, copy column, paste in the next colmun to ri

Hi Venkat1926,

Thanks for your reply. Yes you're correct the date is in B1, apologises for this.

In terms of the formula in column M, I just created dummy formulas as I just need to this is work in principal and then I can modify this to work in document I'm currently working in. You can put whatever formula you like in column M, I just need the vba code to copy the formulas and paste them in the next blank column to the right (i.e, column N).

Unfortunately I can't send through the master as the document is rather large in size and also has confidential information in it.

Please let me know if you need any further information and thanks again.

Kind regards,

Re: VBA - Search sheet based on criteria, copy column, paste in the next colmun to ri

Hi,<BR><BR>hopefully this should be of more help.&nbsp; I have put in a bit more information and have also put in an instructions tab to details what I need the vba code to do.&nbsp; Please let me know if you need any further information.<BR><BR>Many thanks in advance.

Re: VBA - Search sheet based on criteria, copy column, paste in the next colmun to ri

Hi venkat1926,

Thanks very much for taking the time to do this. The code did works as I requested and I made some additions to create a fail safe to prevent the formulas from being deleted.

I just need one last thing to be performed in the code and for the next months header to be found in "Business Unit XX" and this columns and all columns to the right of this to be hidden.

I have put code below that will find the next month header but I just need the vba code that will hide that column and all others to the right

Sub test()
Dim mmonth As Date, cfind As Range, pmonth As Date, nmonth As Date
With Worksheets("dashboard")
mmonth = .Range("B2").Value
pmonth = .Range("B1").Value
nmonth -.Range("B3").Value


With Worksheets("Business Unit XX")


ActiveSheet.Cells.EntireColumn.Hidden = False


Set cfind = .Rows("5:5").Find(what:=CDate(pmonth), lookat:=xlWhole)
If Not cfind Is Nothing Then
If cfind.Offset(2, 0).Value <> "" Then
MsgBox "This action can't be performed as you will over write the formulas. Please insert the correct current month and previous month in the Dasshboard sheet"
Exit Sub
Else
End If
End If


Set cfind = .Rows("5:5").Find(what:=CDate(mmonth), lookat:=xlWhole)
If Not cfind Is Nothing Then
Range(cfind.Offset(1, 0), cfind.End(xlDown)).Copy
Range(cfind.Offset(1, 1), cfind.Offset(1, 1).End(xlDown)).PasteSpecial xlPasteAll
cfind.Offset(1, 0).PasteSpecial xlPasteValues
End If


End With
End With
Application.CutCopyMode = False


End Sub

Re: VBA - Search sheet based on criteria, copy column, paste in the next colmun to ri

Sorry here is the modified workbook.

I also forgot to mention that I will need to run the same code for multiple business units.

Is there anyway to call the code but change the worksheet name of the business unit worksheet? I have added this to the excel document to show what I mean.

Please let me know if you need any more information.

Kind regards,

Re: VBA - Search sheet based on criteria, copy column, paste in the next colmun to ri

Hi venkat1926,

Thanks sooo much for your help, I have managed to make the modifications you suggested and it now works a treat. Thanks for having the patience with me as I know my initial requirements weren't clear.

Please see below for the final code:

Sub test()
Dim mmonth As Date, cfind As Range, pmonth As Date, nmonth As Date, ws As String
With Worksheets("dashboard")
mmonth = .Range("B2").Value
pmonth = .Range("B1").Value
nmonth = .Range("B3").Value


ws = InputBox("type the name of the sheet")


With Worksheets(ws)


ActiveSheet.Cells.EntireColumn.Hidden = False


Set cfind = .Rows("5:5").Find(what:=CDate(pmonth), lookat:=xlWhole)
If Not cfind Is Nothing Then
If cfind.Offset(2, 0).Value <> "" Then
Range(cfind.Offset(0, 1), cfind.End(xlToRight)).EntireColumn.Hidden = True
MsgBox "This action can't be performed as you will over write the formulas. Please insert the correct current month and previous month in the Dashboard sheet"
Exit Sub
Else
End If
End If


Set cfind = .Rows("5:5").Find(what:=CDate(mmonth), lookat:=xlWhole)
If Not cfind Is Nothing Then
Range(cfind.Offset(1, 0), cfind.End(xlDown)).Copy
Range(cfind.Offset(1, 1), cfind.Offset(1, 1).End(xlDown)).PasteSpecial xlPasteAll
cfind.Offset(1, 0).PasteSpecial xlPasteValues
End If


Range(cfind.Offset(0, 2), cfind.End(xlToRight)).EntireColumn.Hidden = True
End With
End With
Application.CutCopyMode = False


End Sub

Thanks very much again

Kind regards,

05125668