Macro to copy values to worksheets based on value

[cjlaing]

Hi

I am looking to write a macro where it takes the information from the 1st sheet and copies it out to the appropriate worksheets when I run the macro. I am looking to move them out into worksheets named after contract numbers 8060, 8290, 8483 as examples. There will be a column in the front sheet that states the contract.

Any help would be much appreciated.

Thanks

CJ

 

[dmt32]

Hi,
welcome to forum

have a look at solution I posted for another here:
https://www.mrexcel.com/forum/excel-questions/924157-copy-rows-data-one-sheet-another-based-value-column-first-sheet.html


Following guidance given in Post #2 & hopefully solution may do what you want.

Hope Helpful

Dave

 

[cjlaing]

Hi,
welcome to forum

have a look at solution I posted for another here:
https://www.mrexcel.com/forum/excel-questions/924157-copy-rows-data-one-sheet-another-based-value-column-first-sheet.html


Following guidance given in Post #2 & hopefully solution may do what you want.

Hope Helpful

Dave

Hi Dave

Apologies about taking a bit of time to get back to you. I needed a bit of time to try and actually sit down and try and work my way around it.

I would always want to filter on column C rather than selecting what to filter. Is there a way to change that? Being honest I am not entirely sure how the filter element is actually working just now, as it created sheets I didn't expect it to.

Thanks

CJ

 

[My Aswer Is This]

Are you saying you have sheet names in column "C" of sheet named "Master"
And you want the row copied to the sheet named in column "C"

So if on sheet named "Master" you have the name "George" in column "C" you want that row copied to a sheet named "George"

And the sheet named "George" is already created.

Is this what you want?

If not please give more details.

We always need specific details

 

[dmt32]

Hi Dave

Apologies about taking a bit of time to get back to you. I needed a bit of time to try and actually sit down and try and work my way around it.

I would always want to filter on column C rather than selecting what to filter. Is there a way to change that? Being honest I am not entirely sure how the filter element is actually working just now, as it created sheets I didn't expect it to.

Thanks

CJ

Hi,
Code was designed as a general utility & had no plans to customise it.
If however, it is largely doing what you want you can try following mods to work on a specific column only by changing following:

Delete this section of code

    On Error Resume Next
    Set objRange = Application.InputBox("Select Field Name To Filter", "Range Input", , , , , , 8)
    On Error GoTo 0
    If objRange Is Nothing Then
        Exit Sub
    ElseIf objRange.Columns.Count > 1 Then
        GoTo top
    End If

and modify following variables / values

    FilterCol = 3
    FilterRow = 1

These changes are untested but hopefully, will do what you want.

Hope Helpful

Dave

 

[cjlaing]

Are you saying you have sheet names in column "C" of sheet named "Master"
And you want the row copied to the sheet named in column "C"

So if on sheet named "Master" you have the name "George" in column "C" you want that row copied to a sheet named "George"

And the sheet named "George" is already created.

Is this what you want?

If not please give more details.

We always need specific details

I am referring back to Dave's macro, which gives you the option of what you want to filter on. I am saying that I do not require that, as it will always be column C I want to look at, while by the looks of the thing the macro he wrote could be used to filter on different columns, because the other person's requirements were different to mine.

I have created sheets for all the current values that would be in column C. However, Dave's functionality of it creating one if it does not exist would be a nice addition, as it would mean that if something new was introduced and I had forgotten to create a new sheet it would not end up missed, although not the be all and end all.

 

[cjlaing]

Hi,
Code was designed as a general utility & had no plans to customise it.
If however, it is largely doing what you want you can try following mods to work on a specific column only by changing following:

Delete this section of code

Set objRange = Application.InputBox("Select Field Name To Filter", "Range Input", , , , , , 8)
    On Error GoTo 0
    If objRange Is Nothing Then
        Exit Sub
    ElseIf objRange.Columns.Count > 1 Then
        GoTo top
    End If

and modify following variables / values

    FilterCol = 3
    FilterRow = 1

These changes are untested but hopefully, will do what you want.

Hope Helpful

Dave

Thanks for coming back Dave. I now get an error reading "the extract range has a missing or illegal field name".

Not sure if you might know what is causing this?

 

[My Aswer Is This]

OK I will move on and let Dave finish helping you.

I am referring back to Dave's macro, which gives you the option of what you want to filter on. I am saying that I do not require that, as it will always be column C I want to look at, while by the looks of the thing the macro he wrote could be used to filter on different columns, because the other person's requirements were different to mine.

I have created sheets for all the current values that would be in column C. However, Dave's functionality of it creating one if it does not exist would be a nice addition, as it would mean that if something new was introduced and I had forgotten to create a new sheet it would not end up missed, although not the be all and end all.

 

[dmt32]

Thanks for coming back Dave. I now get an error reading "the extract range has a missing or illegal field name".

Not sure if you might know what is causing this?

what row is your header row? I set it at FilterRow = 1 but you need to change as required.

If still having problems let me know.

Dave

 

[cjlaing]

what row is your header row? I set it at FilterRow = 1 but you need to change as required.

If still having problems let me know.

Dave

I had changed it but I had put 6 rather than 5.

The only other thing that isn't working is it isn't copying column A. I tried tweaking the line of code I thought was required to change but that seemed to cause it to copy all the rows into each sheet and still not copy column A.