reformat data if column criteria is meet

[cmccabe]

I am trying to create a button that will clear and reformat the input data based on criteria ("array") being meet in a column [Test]. I hope the below VB is close:

Please Login or Register  to view this content.

Thank you .

[cmccabe]

Not sure why this posted twice, but there is an example attached in the other post. Thank you and I apologize for the duplicate .

[Richard Buttrey]

That macro is in the Workbook Open event. If you want it attached to a button then create a Module level procedure and point the button to that procedure.

You'll need to change the PasteSpecial line to

Please Login or Register  to view this content.

[cmccabe]

So I create a button with:

Please Login or Register  to view this content.

and then a module with:

Please Login or Register  to view this content.

Thank you .

[Richard Buttrey]

Not necessarily.

You mentioned a button so I took you at your word and assumed you wanted to control this process without it running automatically when the workbook opens.

I'm not exactly sure what the IF [Test] stuff is, but f you want the process to run automatically then a button is, strictly speaking, not necessary since you could put everything in the Workbook open event. i.e.

Please Login or Register  to view this content.

Otherwise a button which points to a procedure with your IF [Test] stuff and which ends with a line

Call MyNewDataProcedure 'where 'MyNewDataProcedure' is a module level procedure that contains your code

[cmccabe]

I placed a button in the spreadsheet:

Please Login or Register  to view this content.

and a workbook open event in the spreadsheet.

When the code is run I get: syntax error

the

Please Login or Register  to view this content.

is highlighted. Thank you .

[Richard Buttrey]

...As I said I don't understand what your If [Test] "array" string of characters is trying to do. This didn't enter the thread until post #4 and I assumed this was code that was working for you already.

Please explain by a narrative what you are trying to achieve with this.

[cmccabe]

In the attached workbook there is a column [Test] that has multiple values in it. If the value in the column is "array" then [Lab #] , [Name], [Result], [Final] are copied to the input sheet. If the value is anything else it is removed. Thank you .

[Richard Buttrey]

I'm confused.

the original task seemed to be to open a file and copy the contents to the input sheet. You're last note seems to be saying that you are also copying some of the rows on the Input sheet but to where? The input sheet?
And in which case to where is the data from the file you open copied?

[cmccabe]

When the spreadsheet is first opened the user is prompted for new data, then the data is loaded in the input sheet. However only the data that has array in the Test column is needed. The desired columns are kept in the input sheeet all others are removed. I hope this helps and thank you .

[Richard Buttrey]

OK understood.

I'm going to assume that your existing file open copy/paste stuff works OK. In which case put the whole of this code either in the Workbook Open event of yuo want it to run automatically when it opens, or in a module level procedure which you can link to a button if you want to control the process pf loading.

Please Login or Register  to view this content.

[cmccabe]

Please Login or Register  to view this content.

I get subscript out of range with the underlined portion highlighted. The sheet 1 name is Input. Thank you .

[Richard Buttrey]

Hi,

Sorry that should be

Please Login or Register  to view this content.

Check that the VB code name for your "Input" tab name is indeed 'Sheet1'. If not change Sheet1 to whatever is the VB code name. It's usually better to work with VB code names since these never change. If you hard code the tab name in the code and someone changes the tab name you're stuffed.

[cmccabe]

That worked.

Column D (Final) is a date and is not displaying as such.
The spreadsheet in which the new data is loaded is multiple tabs (12 total), is there a way to prompt the user for a specific tab in a spreadsheet.

So, there is one spreasheet (Month) in which the data is loaded from, but in that spreadsheet there are 12 tabs. Thank you .

[Richard Buttrey]

Hi,

Yes just create an input box asking the user to enter a valid tab name and apply that name to a string variable, say 'stMonth'. You may need to think about validating the text that's entered.

So assuming the input box captures the string "January" and there is a tab of that name adapt the macro so that it says

Please Login or Register  to view this content.

I'm not sure what you mean by the last column D not displaying correctly. What's in the original file and how is the column in the sheet you're copying formatted?

[cmccabe]

The last column is a date that is in date format in the original.

Please Login or Register  to view this content.

The code works great, how would I validate the text entered? I am assuming you mean Jan vs. January (is there code that looks for either). Thank you .

[Richard Buttrey]

Hi,

Yes you could check for various spellings or check the entry against an array of valid names. Personally though I'd offer the user a simple form with a drop down combobox that contains all the month names then there's no doubt.