Excel VBA help needed

%3CLINGO-SUB%20id%3D%22lingo-sub-1602009%22%20slang%3D%22en-US%22%3EExcel%20VBA%20help%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1602009%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHopefully%20this%20isn't%20too%20abstract.%20I%20have%20a%20sheet%20that%20we%20use%20for%20tracking%20submissions%20to%20one%20of%20our%20customers.%20I%20would%20like%20to%20pull%20that%20data%20into%20a%20worksheet%20that%20I'm%20building%20that%20will%20do%20the%20same%20thing%20for%20our%20other%20customers%2C%20only%20with%20different%20categories%20that%20are%20a%20bit%20more%20conducive%20to%20building%20some%20simple%20metrics.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20essentially%20what%20I'm%20trying%20to%20accomplish%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20sheet%201%20I%20need%20to%20transfer%20the%20following%20information%20to%20the%20next%20available%20row%20on%20sheet%202%3A%3C%2FP%3E%3CUL%3E%3CLI%3ESheet%201%3A%20Column%20A%20to%20Sheet%202%3A%20Column%20A%3C%2FLI%3E%3CLI%3ESheet%202%3A%20Column%20B%20should%20have%20%22TOR%22%20in%20each%20row%20where%20data%20was%20pulled%20from%20Sheet%201%3C%2FLI%3E%3CLI%3ESheet%201%3A%20Column%20B%20to%20Sheet%202%3A%20Column%20C%3C%2FLI%3E%3CLI%3ESheet%201%3A%20Column%20C%20to%20Sheet%202%3A%20Column%20D%3C%2FLI%3E%3CLI%3ESheet%202%3A%20Column%20G%20should%20have%20%22Pass%22%20in%20each%20row%20where%20data%20was%20pulled%20from%20Sheet%201%3C%2FLI%3E%3CLI%3ESheet%201%3A%20Column%20M%20to%20Sheet%202%3A%20Column%20I.%3CUL%3E%3CLI%3EColumn%20M%20is%20a%20short-hand%20version%20of%20the%20content%20that%20would%20be%20entered%20into%20Column%20I%20if%20Sheet%202%20were%20to%20be%20used%20on%20its%20own.%20So%20I%20need%20to%20change%20the%20value%20during%20the%20copy%2C%20for%20example%2C%20if%20%221%22%20was%20entered%20it%20in%20Column%20M%2C%20it%20would%20need%20to%20output%20as%20%22One%22%20in%20Column%20I.%26nbsp%3B%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3EI%20should%20also%20mention%20that%20I%20do%20have%20some%20code%20living%20on%20Sheet%202%20for%20when%20that%20is%20filled%20out%20on%20its%20own.%20Not%20sure%20if%20it%20would%20cause%20any%20problems%20since%20all%20it%20does%20is%20fill%20in%20the%20date%20in%20Sheet%202%20Column%20A%20if%20a%20value%20is%20typed%20into%20Cell%20B%20in%20the%20same%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20anyone%20can%20offer%20a%20guiding%20hand%20on%20how%20to%20do%20the%20above%2C%20I%20would%20really%20appreciate%20it.%20I've%20been%20sort%20of%20divining%20how%20the%20VBA%20works%20from%20bits%20of%20code%20I've%20scrounged%20up%20around%20the%20internet%20and%20I've%20been%20able%20to%20get%20some%20results%20but%20they're%20pretty%20piece-meal%2Finconsistent%20and%20things%20tend%20to%20quickly%20fall%20apart%20since%20I'm%20basically%20building%20Frankenstein.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJohn%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1602009%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1602305%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VBA%20help%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1602305%22%20slang%3D%22en-US%22%3E%3CP%3E1%20Create%20a%20Macro%3A%20With%20Excel%20VBA%20you%20can%20automate%20tasks%20in%20Excel%20by%20writing%20so-called%20macros.%20In%20this%20chapter%2C%20learn%20how%20to%20create%20a%20simple%20macro.%3C%2FP%3E%3CP%3E2%20MsgBox%3A%20The%20MsgBox%20is%20a%20dialog%20box%20in%20Excel%20VBA%20you%20can%20use%20to%20inform%20the%20users%20of%20your%20program.%3C%2FP%3E%3CP%3E3%20Workbook%20and%20Worksheet%20Object%3A%20Learn%20more%20about%20the%20Workbook%20and%20Worksheet%20object%20in%20Excel%20VBA.%3C%2FP%3E%3CP%3E4%20Range%20Object%3A%20The%20Range%20object%2C%20which%20is%20the%20representation%20of%20a%20cell%20(or%20cells)%20on%20your%20worksheet%2C%20is%20the%20most%20important%20object%20of%20Excel%20VBA.%3C%2FP%3E%3CP%3E5%20Variables%3A%20This%20chapter%20teaches%20you%20how%20to%20declare%2C%20initialize%20and%20display%20a%20variable%20in%20Excel%20VBA.%3C%2FP%3E%3CP%3E6%20If%20Then%20Statement%3A%20Use%20the%20If%20Then%20statement%20in%20Excel%20VBA%20to%20execute%20code%20lines%20if%20a%20specific%20condition%20is%20met.%3C%2FP%3E%3CP%3E7%20Loop%3A%20Looping%20is%20one%20of%20the%20most%20powerful%20programming%20techniques.%20A%20loop%20in%20Excel%20VBA%20enables%20you%20to%20loop%20through%20a%20range%20of%20cells%20with%20just%20a%20few%20codes%20lines.%3C%2FP%3E%3CP%3E8%20Macro%20Errors%3A%20This%20chapter%20teaches%20you%20how%20to%20deal%20with%20macro%20errors%20in%20Excel.%3C%2FP%3E%3CP%3E9%20String%20Manipulation%3A%20In%20this%20chapter%2C%20you'll%20find%20the%20most%20important%20functions%20to%20manipulate%20strings%20in%20Excel%20VBA.%3C%2FP%3E%3CP%3E10%20Date%20and%20Time%3A%20Learn%20how%20to%20work%20with%20dates%20and%20times%20in%20Excel%20VBA.%3C%2FP%3E%3CP%3E11%20Events%3A%20Events%20are%20actions%20performed%20by%20users%20which%20trigger%20Excel%20VBA%20to%20execute%20code.%3C%2FP%3E%3CP%3E12%20Array%3A%20An%20array%20is%20a%20group%20of%20variables.%20In%20Excel%20VBA%2C%20you%20can%20refer%20to%20a%20specific%20variable%20(element)%20of%20an%20array%20by%20using%20the%20array%20name%20and%20the%20index%20number.%3C%2FP%3E%3CP%3E13%20Function%20and%20Sub%3A%20In%20Excel%20VBA%2C%20a%20function%20can%20return%20a%20value%20while%20a%20sub%20cannot.%3C%2FP%3E%3CP%3E14%20Application%20Object%3A%20The%20mother%20of%20all%20objects%20is%20Excel%20itself.%20We%20call%20it%20the%20Application%20object.%20The%20application%20object%20gives%20access%20to%20a%20lot%20of%20Excel%20related%20options.%3C%2FP%3E%3CP%3E15%20ActiveX%20Controls%3A%20Learn%20how%20to%20create%20ActiveX%20controls%2C%20such%20as%20command%20buttons%2C%20text%20boxes%2C%20list%20boxes%20etc.%3C%2FP%3E%3CP%3E16%20Userform%3A%20This%20chapter%20teaches%20you%20how%20to%20create%20an%20Excel%20VBA%20Userform.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1602418%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VBA%20help%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1602418%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F378366%22%20target%3D%22_blank%22%3E%40Smith_J%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIMHO%2C%20it's%20easier%20to%20give%20the%20link%20on%20the%20source%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.excel-easy.com%2Fvba.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.excel-easy.com%2Fvba.html%3C%2FA%3E%26nbsp%3Brather%20than%20copy%2Fpaste%20the%20text%20having%20no%20internal%20links.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnyway%2C%20that's%20not%20related%20to%20the%20initial%20question.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hi all,

 

Hopefully this isn't too abstract. I have a sheet that we use for tracking submissions to one of our customers. I would like to pull that data into a worksheet that I'm building that will do the same thing for our other customers, only with different categories that are a bit more conducive to building some simple metrics.

 

Here is essentially what I'm trying to accomplish:

 

For sheet 1 I need to transfer the following information to the next available row on sheet 2:

  • Sheet 1: Column A to Sheet 2: Column A
  • Sheet 2: Column B should have "TOR" in each row where data was pulled from Sheet 1
  • Sheet 1: Column B to Sheet 2: Column C
  • Sheet 1: Column C to Sheet 2: Column D
  • Sheet 2: Column G should have "Pass" in each row where data was pulled from Sheet 1
  • Sheet 1: Column M to Sheet 2: Column I.
    • Column M is a short-hand version of the content that would be entered into Column I if Sheet 2 were to be used on its own. So I need to change the value during the copy, for example, if "1" was entered it in Column M, it would need to output as "One" in Column I. 

I should also mention that I do have some code living on Sheet 2 for when that is filled out on its own. Not sure if it would cause any problems since all it does is fill in the date in Sheet 2 Column A if a value is typed into Cell B in the same row.

 

If anyone can offer a guiding hand on how to do the above, I would really appreciate it. I've been sort of divining how the VBA works from bits of code I've scrounged up around the internet and I've been able to get some results but they're pretty piece-meal/inconsistent and things tend to quickly fall apart since I'm basically building Frankenstein. 

 

Thanks,

 

John

2 Replies
Highlighted

1 Create a Macro: With Excel VBA you can automate tasks in Excel by writing so-called macros. In this chapter, learn how to create a simple macro.

2 MsgBox: The MsgBox is a dialog box in Excel VBA you can use to inform the users of your program.

3 Workbook and Worksheet Object: Learn more about the Workbook and Worksheet object in Excel VBA.

4 Range Object: The Range object, which is the representation of a cell (or cells) on your worksheet, is the most important object of Excel VBA.

5 Variables: This chapter teaches you how to declare, initialize and display a variable in Excel VBA.

6 If Then Statement: Use the If Then statement in Excel VBA to execute code lines if a specific condition is met.

7 Loop: Looping is one of the most powerful programming techniques. A loop in Excel VBA enables you to loop through a range of cells with just a few codes lines.

8 Macro Errors: This chapter teaches you how to deal with macro errors in Excel.

9 String Manipulation: In this chapter, you'll find the most important functions to manipulate strings in Excel VBA.

10 Date and Time: Learn how to work with dates and times in Excel VBA.

11 Events: Events are actions performed by users which trigger Excel VBA to execute code.

12 Array: An array is a group of variables. In Excel VBA, you can refer to a specific variable (element) of an array by using the array name and the index number.

13 Function and Sub: In Excel VBA, a function can return a value while a sub cannot.

14 Application Object: The mother of all objects is Excel itself. We call it the Application object. The application object gives access to a lot of Excel related options.

15 ActiveX Controls: Learn how to create ActiveX controls, such as command buttons, text boxes, list boxes etc.

16 Userform: This chapter teaches you how to create an Excel VBA Userform.

Highlighted

@Smith_J 

IMHO, it's easier to give the link on the source https://www.excel-easy.com/vba.html rather than copy/paste the text having no internal links.

 

Anyway, that's not related to the initial question.