PDA

View Full Version : Solved: Assistance for a Beginner



YellowLabPro
08-14-2006, 10:37 PM
I am requesting the assistance in streamlining a recorded macro.
I am copying specific columns from one workbook, sheet to another workbook, worksheet.



thank you in advance:

geekgirlau
08-14-2006, 11:16 PM
The idea behind streamlining is to remove anything that is unnecessary. At a basic level, much of this involves selecting cells (which is almost always unnecessary) and parameters in a dialog box that haven't been updated. For example, if you select File | Page Setup and change the left margin, you will also record the current setting for every other option in the dialog box.

Try this for starters - I've commented out sections of your code and included explanations about what I've done.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/15/2006 by YLP'
'
'Application.CommandBars("Stop Recording").Visible = True
'Windows("Complete_Upload_File.xls").Activate

Windows("TGS Item Record Creator.xls").Activate

' you don't need to select cells to copy them
'Range("U3").Select
'Range(Selection, Selection.End(xlDown)).Select
'Range(Selection, Selection.End(xlUp)).Select
'Range("U3:U118").Select
'Selection.Copy
Range("U3:U118").Copy

' activate the other workbook and paste
Windows("TGSUpdater.xls").Activate
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False

' copy from the original workbook
Windows("TGS Item Record Creator.xls").Activate
'ActiveWindow.ScrollRow = 1
'Range("W3:W118").Select
'Selection.Copy
Range("W3:W118").Copy

' activate the other workbook and paste
Windows("TGSUpdater.xls").Activate
Range("B1").Select

' no need to copy twice
'ActiveSheet.Paste
'Application.CutCopyMode = False
'Selection.Copy

' don't worry about the default parameters unless you are changing them
'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
':=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Windows("TGS Item Record Creator.xls").Activate
' don't need to scroll, as your macro doesn't need to "see" the worksheet
'ActiveWindow.ScrollColumn = 15
'ActiveWindow.ScrollColumn = 16
'ActiveWindow.ScrollColumn = 17
'ActiveWindow.ScrollColumn = 18
'ActiveWindow.ScrollRow = 1

'Range("Z3").Select
'Selection.AutoFill Destination:=Range("Z3:Z118"), Type:=xlFillDefault
Range("Z3").AutoFill Destination:=Range("Z3:Z118"), Type:=xlFillDefault

'Range("Z3:Z118").Select
'Application.CutCopyMode = False
'Selection.Copy
'Range("Z3:Z118").Select
'Application.CutCopyMode = False
'Selection.Copy
Range("Z3:Z118").Copy

Windows("TGSUpdater.xls").Activate
Range("C1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Windows("TGS Item Record Creator.xls").Activate
'ActiveWindow.ScrollColumn = 19
'ActiveWindow.ScrollColumn = 20
'ActiveWindow.SmallScroll Down:=-79
'ActiveWindow.ScrollColumn = 19
'ActiveWindow.ScrollColumn = 18
'ActiveWindow.ScrollColumn = 17
'ActiveWindow.ScrollColumn = 16
'ActiveWindow.ScrollColumn = 15
'ActiveWindow.ScrollColumn = 14
'ActiveWindow.ScrollColumn = 13
'ActiveWindow.ScrollColumn = 12
'ActiveWindow.ScrollColumn = 11
'ActiveWindow.ScrollColumn = 10
'ActiveWindow.ScrollColumn = 9
'ActiveWindow.ScrollColumn = 8
'ActiveWindow.ScrollColumn = 7
'ActiveWindow.ScrollColumn = 6
'ActiveWindow.ScrollColumn = 5
'ActiveWindow.ScrollColumn = 4

'Range("F3:F118").Select
'Application.CutCopyMode = False
'Selection.Copy
Range("F3:F118").Copy

Windows("TGSUpdater.xls").Activate
Range("D1").Select
'ActiveSheet.Paste
'Application.CutCopyMode = False
'Selection.Copy
'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
':=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

Bob Phillips
08-15-2006, 12:36 AM
Another good technique is to create and assign workbook and worksheet variables, and use these in code. It helps control the code better, and makes it more readable



Set oWBSource = Windows("TGS Item Record Creator.xls")
Set oWSSource = oWBSource.Worksheets("Record Creator")

Set oWBTarget = Workbooks("TGSUpdater.xls")
Set oWSTarget = oWBTarget.Worksheets("Update")

YellowLabPro
08-15-2006, 06:49 AM
Thanks geekgirlau,
The explanations helped clarify tremendously.

Here is where I am at after following your instruct.


I would like to follwo Xld's advice and implement the worksheets as variables, can someone get me started on this?

Change the text on the page to entirely Uppercase.
Look in Column "A" and search and remove any spaces.
Copy Column "A2" to "G2"

I am not requesting that someone necessarily do this for me outright, but rather help guide me through it, such in the manner that geekgirl did. My ultimate goal is to learn this program so that I may unlock all the power here in VBA.

thanks,

YLP


Option Explicit
Sub Updater()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng1 As Range, rng2 As Range
Set ws1 = Workbooks("TGS Item Record Creator.xls").Sheets("Record Creator")
Set ws2 = Workbooks("TGSUpdater.xls").Sheets("Update")
'Set ws3 = Workbooks("Complete_Upload_File.xls").Sheets("EC Products")
'LastRow = ws3.Cells(Rows.Count, 1).End(xlUp).Row

Range("A1:G1") = Array("Item#", "Record Desription", "Cost", "Price", "Qty", "Vend.Id", "Item#")

Windows("TGS Item Record Creator.xls").Activate
Range("F3:F118").Copy
Windows("TGSUpdater.xls").Activate
Range("F2").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Windows("TGS Item Record Creator.xls").Activate
Range("U3:U118").Copy
Windows("TGSUpdater.xls").Activate
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Windows("TGS Item Record Creator.xls").Activate
Range("W3:W118").Copy
Windows("TGSUpdater.xls").Activate
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Windows("TGS Item Record Creator.xls").Activate
Range("Y3:Y118").Copy
Windows("TGSUpdater.xls").Activate
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Windows("TGS Item Record Creator.xls").Activate
Range("Z3:Z118").Copy
Windows("TGSUpdater.xls").Activate
Range("D2").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Windows("TGS Item Record Creator.xls").Activate
Range("AA3:AA118").Copy
Windows("TGSUpdater.xls").Activate
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Rows("1:1").HorizontalAlignment = xlCenter
Rows("1:1").Font.Bold = True
Cells.Columns.AutoFit
End Sub

YellowLabPro
08-15-2006, 08:08 AM
Test Response

Bob Phillips
08-15-2006, 08:32 AM
Okay, so your code creates the worksheet variables, so now you need to use them.


This line



Range("A1:G1") = Array("Item#", "Record Desription", "Cost", "Price", "Qty", "Vend.Id", "Item#")


must refere to a worksheet somewhere, but your code gives no clue. Precede the range statement with the appropriate worksheet object.

Then this code


Windows("TGS Item Record Creator.xls").Activate
Range("F3:F118").Copy


becomes one line, worksheet_object.Range(...).Copy

Similarly,


Windows("TGSUpdater.xls").Activate
Range("F2").Select
Selection.PasteSpecial Paste:=xlPasteValues


then becomes next_worksheet_object.Range(...).pastespecial ...

etc.

Just make sure that all ranges (and that includes the Cells object) are qualified with the appropriate worksheet object.

YellowLabPro
08-15-2006, 12:48 PM
Hi Xld,
Ok, that went well, better than I expected. Thanks, you led me along enough for me to have to figure it out, w/out doing it for me...


Sub Updater()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng1 As Range, rng2 As Range
Set ws1 = Workbooks("TGS Item Record Creator.xls").Sheets("Record Creator")
Set ws2 = Workbooks("TGSUpdater.xls").Sheets("Update")
'Set ws3 = Workbooks("Complete_Upload_File.xls").Sheets("EC Products")
'LastRow = ws3.Cells(Rows.Count, 1).End(xlUp).Row

ws2.Range("A1:G1") = Array("Item#", "Record Desription", "Cost", "Price", "Qty", "Vend.Id", "Item#")
Rows("1:1").HorizontalAlignment = xlCenter
Rows("1:1").Font.Bold = True
Cells.Columns.AutoFit
Rows("1:1").HorizontalAlignment = xlCenter
Rows("1:1").Font.Bold = True

ws1.Range("F3:F118").Copy
ws2.Range("F2").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

ws1.Range("U3:U118").Copy
ws2.Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ws1.Range("W3:W118").Copy
ws2.Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

ws1.Range("Y3:Y118").Copy
ws2.Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ws1.Range("Z3:Z118").Copy
ws2.Range("D2").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

ws1.Range("AA3:AA118").Copy
ws2.Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Rows("1:1").HorizontalAlignment = xlCenter
Rows("1:1").Font.Bold = True
Cells.Columns.AutoFit
End Sub


For my next part, could someone offer how I will go about this? Again, more on the lines of guidance, not providing the code please-

1. Change the text on the page to entirely Uppercase.
2. Look in Column "A" and search and remove any spaces.
3. Copy Column "A2" to "G2"

thanks

YLP

Bob Phillips
08-15-2006, 12:54 PM
1. Change the text on the page to entirely Uppercase.
You need to loop through all the cells and upshift them. VBA has a UCase function that can do that.

For Each ... Next is the loop construct to go through all cells, and you could use UsedRange to identify what to loop through.


2. Look in Column "A" and search and remove any spaces.
Remove spaces in string or delete rows where A is blank?

Either way you need to identify the last row of data, and loop from there back to row 1 (must be backwards otherwise there is a danger of missing items). Test for either all blank if that condition, else you can use Replace to remove " ".

Tip. Turn off screenupdating.


3. Copy Column "A2" to "G2"
A2 is a cell not a column, A is a column.

This would be the same as you used before

rng.Copy rng2



Giving guidance is harder than giving the solution :)

Zack Barresse
08-15-2006, 01:05 PM
Not sure if I understood you correctly, but maybe ...

Sub Updater()
Dim ws1 As Worksheet, ws2 As Worksheet, c As Range
Set ws1 = Workbooks("TGS Item Record Creator.xls").Sheets("Record Creator")
Set ws2 = Workbooks("TGSUpdater.xls").Sheets("Update")
ws2.Range("A1:G1") = Array("Item#", "Record Desription", "Cost", "Price", "Qty", "Vend.Id", "Item#")
ws2.Rows("1:1").HorizontalAlignment = xlCenter
ws2.Rows("1:1").Font.Bold = True
ws1.Range("F3:F118").Copy
ws2.Range("F2:F117").PasteSpecial xlPasteValues
ws1.Range("U3:U118").Copy
ws2.Range("A2:A117").PasteSpecial xlPasteValues
ws1.Range("W3:W118").Copy
ws2.Range("B2:B117").PasteSpecial xlPasteValues
ws1.Range("Y3:Y118").Copy
ws2.Range("C2:C117").PasteSpecial xlPasteValues
ws1.Range("Z3:Z118").Copy
ws2.Range("D2:D117").PasteSpecial xlPasteValues
ws1.Range("AA3:AA118").Copy
ws2.Range("E2:E117").PasteSpecial xlPasteValues
ws2.Range("A:A").Replace " ", "", xlValues
ws2.Range("A2").Copy Destination:=ws2.Range("G2")
For Each c In ws2.Cells.SpecialCells(xlCellTypeConstants, 23)
If Not IsNumeric(c.Value) Then c.Value = UCase(c.Value)
Next c
ws2.Cells.Columns.AutoFit
Application.CutCopyMode = False
End Sub

YellowLabPro
08-15-2006, 01:31 PM
Giving guidance is harder than giving the solution :)
But you do it very well....:clap:

I have the copy line done:

ws2.Range("A2:A118").Copy Range("Q2")

YellowLabPro
08-15-2006, 03:30 PM
Zack,
This line of code works if there are any spaces in the text string in (A), but then a bug message pops up.
Would it be that it needs to be an IF statment?
Something like IF " " .Replace ""

Zack Barresse
08-15-2006, 03:35 PM
Something like that, I'd use an On Error Resume Next statement on, since it's fairly inconsequential. For items of more consequence, I'd try to use more proper error handling.

YellowLabPro
08-15-2006, 04:30 PM
Zack,
Not sure what changed, but the acutal line of code does not actually work. I substituted it out w/

ws2.Columns("A").Replace What:=" ", Replacement:=""

Your other code helped streamline my other lines of code one more step,

thanks.

YellowLabPro
08-15-2006, 05:48 PM
Hi Zack,
The UpperCase did the trick, thanks- I would not have figured this out.
I understand part of your code, theoretically. Can you provide some more info regarding it when you have a moment?

Xld wrote to use either the For Each or the UsedRange. I did not find much on either method in the help files... Where should I go to lookup information for understanding on the subject either method?

Xld also wrote about writing this so it looked for the last row. I tried this on a couple of things, but do not have the syntax right.

For your code, would this be an appropriate step?

For Each C In ws2.Cells.SpecialCells(xlCellTypeConstants, 23)
If Not IsNumeric(C.Value) Then C.Value = UCase(C.Value)
Next C


There is one are of my code that I need to alter: Currently I am using a fixed range, from F3:F118. I do need to use this line of code to search for the last item in the row and go up to F3. This range will change all the time:

ws1.Range("F3:F118").Copy
ws2.Range("P2").PasteSpecial Paste:=xlPasteValues


I tried:
ws1.Range("F3:F" & LastRowSrc).Copy ws1.Range("F" & LastRowDst)
ws2.Range("P2").PasteSpecial Paste:=xlPasteValues

Block of Code:

Sub Updater()
Dim LastRow As Long, LastRowSrc As Long, LastRowDst As Long, i As Long
Dim ws1 As Worksheet, ws2 As Worksheet, C As Range
Dim rng1 As Range, rng2 As Range
Set ws1 = Workbooks("TGS Item Record Creator.xls").Sheets("Record Creator")
Set ws2 = Workbooks("TGSUpdater.xls").Sheets("Update")
LastRowSrc = ws1.Range("A65536").End(xlUp).Row
'Set ws3 = Workbooks("Complete_Upload_File.xls").Sheets("EC Products")
'LastRow = ws3.Cells(Rows.Count, 1).End(xlUp).Row

ws2.Range("A1:V1") = Array("Item#", "Record Desription", "Inv", "Tax", "Dept", "Cat", "Qty", "-", "-", "-", "Cost", "Price", "-", "-", "-", "Vend.Id", "Item#", "", "", "", "", "1")
Rows("1:1").HorizontalAlignment = xlCenter
Rows("1:1").Font.Bold = True
Cells.Columns.AutoFit
Rows("1:1").HorizontalAlignment = xlCenter
Rows("1:1").Font.Bold = True

ws2.Range("A2:Z200").Cells.ClearContents


ws1.Range("F3:F" & LastRowSrc).Copy ws1.Range("F" & LastRowDst)
ws2.Range("P2").PasteSpecial Paste:=xlPasteValues
ws1.Range("U3:U118").Copy
ws2.Range("A2").PasteSpecial Paste:=xlPasteValues


Thanks,

YLP

YellowLabPro
08-15-2006, 09:12 PM
All is completed,
Thanks for everyone's assistance.

YLP

Bob Phillips
08-16-2006, 01:26 AM
LastRowSrc = ws1.Range("A65536").End(xlUp).Row


Never hard-code the row counts, use


LastRowSrc = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row

YellowLabPro
08-16-2006, 05:24 AM
Thanks xld

compariniaa
08-16-2006, 07:45 AM
Range("U3:U118").Copy

' activate the other workbook and paste
Windows("TGSUpdater.xls").Activate
Range("A1").Select
ActiveSheet.Paste
End Sub there's an even easier way to do this:

'the first workbooks and sheets part is unneccessary if
'the active book and sheet are your copy source
workbooks("TGS Item Record Creator.xls").sheets("Sheet1").range("U3:U118") .copy _
workbooks("TGSUpdater.xls").sheets("Sheet1").range("A1")
I don't know much about compatibility issues, so it may not work on earlier versions of Excel (I'm using xl2003)

Zack Barresse
08-16-2006, 07:52 AM
Bob used the UsedRange, which I never fully trusted and probably will rarely - if ever - use. My code could error out if there are no constants there, but I feel safer using it than the UsedRange object.

SpecialCells are a great method to interact with cells. The first syntax is the type of special cells, the second syntax is the value, which can be used to return more than one type of special cells type(s). There are all sorts of combinations you can use.

Change ..

ws1.Range("F3:F118").Copy
ws2.Range("P2").PasteSpecial Paste:=xlPasteValues

.. to ..

With ws1.Range("F3", ws1.Cells(ws1.Rows.Count, "F").end(xlup))
.Value = .Value
End With

Which, btw, is another (strong) option to copy/paste values. In VBA, there is little need to paste values anyway, since we can just leave the Value to equal it's own Value and be done with it, not even accessing the CutCopyMode at all.

HTH

Bob Phillips
08-16-2006, 09:30 AM
Bob used the UsedRange, which I never fully trusted and probably will rarely - if ever - use.

Why? at worst it over-eggs the range, so you end up checking a few empty cells! I have never heard of it under-egging it.

Zack Barresse
08-16-2006, 09:40 AM
A few? Maybe I've seen worst cases than you have, Bob. I really don't like the idea of looping, let alone looping through a bunch of unecessary cells/objects.