Moving columns & data validation on rows

[Andrew Quirl]

Hi .. I am looking for advice and coding hints to get me started on a couple of macro projects. I'm trying to get more comfortable with coding to increase my level of self-sufficiency.

1. Moving columns - on this one I will be copying data from columns on sheet2 and pasting into columns on sheet1, without including the header cell in the copying or pasting process. Row 1 is the header row and should be ignored.
   [Ex: move data (minus header) from sheet2 colH to sheet1 colF]
   
   
2. Data validation by row - the purpose of this macro is to 'clean up' a data set by analyzing each row to either delete the row or to keep the row based on if it includes one of a list of possible values. It also needs to pick out the purchase order number, name the file using the purchase order number as the name, and automatically email the worksheet to an email address.

 

[SirJB7]

Hi, Andrew Quirl!

Not tested, just written here, but hope to not make mistakes.

Option Explicit

Sub Macro1()
    Sheets("Hoja2").Select
    Range("H2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Hoja1").Select
    Range("F2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

Sub Macro2()
    Worksheets("Hoja2").Range(Worksheets("Hoja2").[H2], Worksheets("Hoja2").[H2].End(xlDown)).Copy Worksheets("Hoja1").[F2]
End Sub

If you use the built-in macro recorder, you'll get the code of Macro1, which works but it does several unnecessary and resource consuming tasks as:
- selecting worksheets
- selecting cells
Code that you can modify to arrive at a single line like of Macro2, just by using the also built-in help, by positioning the cursor on a word of the code (Copy in this case) and pressing F1 (Help).

So this is a way, not the only one: macro recorder, [help, edit, test,..].

That's for 1). Skipping 2) for the time being.

Regards!

 

[Andrew Quirl]

Am I guessing correctly that you named your workbook "hoja" and the code hoja1 knows to look at sheet1 of the workbook, and hoja2 knows to look at sheet2 of the workbook?

 

[SirJB7]

Hi, Andrew Quirl!
You're right. I use Spanish versions of Excel, so sheet becomes hoja.
Regards!

 

[Andrew Quirl]

I replaced every instance of hoja with my filename (retaining the 1 or 2) and when I run the macro I get a run-time error '9' Subscript out of range.
I did also change the H2 to R2 but that should not have affected anything.

Sub Macro2()
    Worksheets("CCT1 ZZ Spiral Band Worksheet2").Range(Worksheets("CCT1 ZZ Spiral Band Worksheet2").[R2], Worksheets("CCT1 ZZ Spiral Band Worksheet2").[R2].End(xlDown)).Copy Worksheets("CCT1 ZZ Spiral Band Worksheet1").[F2]
End Sub

 

[SirJB7]

Hi, Andrew Quirl!
First of all, do you have 2 worksheets named "Sheet1" and "Sheet2" (unquoted) in the workbook were you placed the VBA code?
Second, where did you place the code?
Third, that workbook was the only one opened in that Excel instance? If more than one, which one was selected?
Regards!

 

[Andrew Quirl]

FYI, the workbook is titled exactly "CCT1 ZZ Spiral Band Worksheet"
No, sheet1 is named "ZZ SPIRAL BAND CONVERSION" & sheet2 is named "Competitor Data"
The names of the tabs may differ with each workbook I open. sheet2 will always be named "Competitor Data" but sheet1 will have a different name for every workbook I open.
I placed the code into Module2 of my Personal macro workbook.
Personal workbook and the one I'm working on are the only two workbooks open.

 

[SirJB7]

Hi, Andrew Quirl!

So the index out of range error that you get is due to the names of your worksheets. IOOR means in this case that no "Sheet2" worksheet was found (like neither in the original version with "Hoja2"); same for "Sheet1".

You have to replace your actual "Sheet2" and "Sheet1" worksheet names by your actual names: "Competitor Data" and "ZZ SPIRAL BAND CONVERSION".

This will extend notably the length of the instructions, so if I were you I'll do something like this:

Option Explicit

Sub Macro1()
    ' constants
    Const ksWS1 = "ZZ SPIRAL BAND CONVERSION"
    Const ksWS2 = "Competitor Data"
    '
    Sheets(ksWS2).Select
    Range("H2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets(ksWS1).Select
    Range("F2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

Sub Macro2()
    ' constants
    Const ksWS1 = "ZZ SPIRAL BAND CONVERSION"
    Const ksWS2 = "Competitor Data"
    '
    Worksheets(ksWS2).Range(Worksheets(ksWS2).[H2], Worksheets(ksWS2).[H2].End(xlDown)).Copy Worksheets(ksWS1).[F2]
End Sub

Regards!

 

[Andrew Quirl]

macro1 is stand-alone and macro2 is stand-alone right? these are just two different ways to skin the cat, or are they meant to run together as one?

 

[SirJB7]

If you use the built-in macro recorder, you'll get the code of Macro1, which works but it does several unnecessary and resource consuming tasks as:
- selecting worksheets
- selecting cells
Code that you can modify to arrive at a single line like of Macro2, just by using the also built-in help, by positioning the cursor on a word of the code (Copy in this case) and pressing F1 (Help).

Hi, Andrew Quirl!
Two different ways to do the job.
Regards!

 

[Andrew Quirl]

Ok it works for me now (using macro2).
The thing I'm struggling with is the fact that the macro code line would need to be modified every time I open a unique workbook that has a different name for sheet1. Is there not a way to make this code dynamic to work on any name for sheet1?

 

[SirJB7]

Hi, Andrew Quirl!
I can't image how to do it, except that in all your workbooks you have:
a) same worksheet names
b) same relative index position worksheets (2nd and 1st, 5th and 11th... but same on all
Regards!

 

[Andrew Quirl]

The boss said I could template the sheet names so we are good and this works! Thank you! (I'm gonig to post my 2nd question on its own post)

 

[SirJB7]

Hi, Andrew Quirl!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards