Macro that will save workbook at a specified location and name the file based on a cell....

[candrews84]

First off any input is greatly appreciated, here's what I am trying to accomplish via Macro/VBA code:

1. Copy cell A2 on tab 2 to cell $A$2 on tab 1

2. Copy cell B2 on tab 2 to cell $B$2 on tab 1

3. Copy cell C2 on tab 2 to cell $C$2 on tab 1

4. File Save As (make the save as name equal to the text in cell D2 on tab 1)

5. Save As location should save to C:/List/(folder named same as cell E2 on tab 1)

6. The macro starts over copying from A3 on tab 2 to tab 1 but all the paste locations remain the same on tab 1 they do not change.


Additionally for step 5 the folder will already exist at the save location I'm just wondering if the VBA code can reference cell E2 (say for example the text 'ABC' resides in this cell) in the workbook and know to match that to the folder in C:/List/ABC(folder)


I've tried to simplify this as best as possible but please let me know if this is clear. Any help is greatly appreciated as I am faced with a large mundane task and I am trying to automate it as much as possible.

 

[candrews84]

[TABLE="width: 708"]
<colgroup><col></colgroup><tbody>[TR]
[TD] I have the following code but I am trying to edit it so that it not only references a cell for the file name but also starts the process over again as mentioned in the original post:

[TABLE="width: 518"]
<colgroup><col></colgroup><tbody>[TR]
[TD] Sheets("tab2").Select[/TD]
[/TR]
[TR]
[TD] Range("A2").Select[/TD]
[/TR]
[TR]
[TD] Selection.Copy[/TD]
[/TR]
[TR]
[TD] Sheets("tab1").Select[/TD]
[/TR]
[TR]
[TD] Range("A2").Select[/TD]
[/TR]
[TR]
[TD] Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/TD]
[/TR]
[TR]
[TD] :=False, Transpose:=False[/TD]
[/TR]
[TR]
[TD] Sheets("tab2").Select[/TD]
[/TR]
[TR]
[TD] Range("B2").Select[/TD]
[/TR]
[TR]
[TD] Application.CutCopyMode = False[/TD]
[/TR]
[TR]
[TD] Selection.Copy[/TD]
[/TR]
[TR]
[TD] Sheets("tab1").Select[/TD]
[/TR]
[TR]
[TD] Range("B2").Select[/TD]
[/TR]
[TR]
[TD] ActiveSheet.Paste[/TD]
[/TR]
[TR]
[TD] Sheets("tab2").Select[/TD]
[/TR]
[TR]
[TD] Range("C2").Select[/TD]
[/TR]
[TR]
[TD] Application.CutCopyMode = False[/TD]
[/TR]
[TR]
[TD] Selection.Copy[/TD]
[/TR]
[TR]
[TD] Sheets("tab1").Select[/TD]
[/TR]
[TR]
[TD] Range("C2").Select[/TD]
[/TR]
[TR]
[TD] Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/TD]
[/TR]
[TR]
[TD] :=False, Transpose:=False[/TD]
[/TR]
[TR]
[TD] Range("U8").Select[/TD]
[/TR]
[TR]
[TD] Application.CutCopyMode = False[/TD]
[/TR]
[TR]
[TD] ActiveWorkbook.SaveAs Filename:= _[/TD]
[/TR]
[TR]
[TD]"C:\List\ABC", FileFormat:= _[/TD]
[/TR]
[TR]
[TD] xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]