Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting worksheet and ranges within sheet to run macro on
I am trying to automate a sheet in a workbook that has worksheets being added
to it continually. I have created a worksheet that is designed to pull infomation from a selected workbook from 3 different areas of the worksheet. I have recorded a macro that goes into the workbook that the macro was recorded with, and pulls information as internded. Heres my problem. Can I change this macro so I can: 1-Select the workbook that the information is to be pulled from 2-Make the second range selection a variable? I want to tell it which cells to copy from. Here is the recorded nacro: Copy_from_balance_for_Validation Macro ' Macro recorded 5/21/2009 by Jacky D ' ' Range("A8").Select Sheets("Sheet2").Select Range("A3:AA3").Select Range("AA3").Activate Selection.Copy Sheets("Validation").Select Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=True Range("B8").Select Sheets("SHEET2").Select ActiveWindow.ScrollColumn = 83 Range("A29:AA29").Select Range("AA29").Activate Application.CutCopyMode = False Selection.Copy Sheets("Validation").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Range("C8").Select Sheets("SHEET2").Select Range("A1:AA1").Select Range("AA1").Activate Application.CutCopyMode = False Selection.Copy Sheets("Validation").Select Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=True End Sub Any row in the macro above that reads "Sheets("Sheet2").Select" I would want to make a variable. The Sheets("SHEET2").Select ActiveWindow.ScrollColumn = 83 Range("A29:AA29").Select Range("AA29").Activate" I would also want to select the range within the workbook. I know I need to define the worksheet that information is to be pulled from, and then the range and use an inputbox to do this, but I can't seem to get it right. Thanks so much for the help. Jacky D. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting worksheet and ranges within sheet to run macro on
Thanks, I think a drop down list could become unwieldly, there are currently
45 worksheets in this workbook, and more are added every day. They are named uniguely, and when created, they are placed near the sheet that I will run the macro within. I have no idea how to create a change event macro. I don't know how to write in VBA, but have been pretty successful in recording macros, and then tweaking to suit my needs. Wouldn't I want to keep the selections that are pointing to the cell ranges which I do not want to change? "Don Guillett" wrote: You could have a drop down list (tied to a change_event macro) to select the wordbook to fetch from. Then an input box asking for the sheet and range to copy from, etc. You should endeavor to remove all unnecessary "selections" and the scrolling is completely unnecessary. -- Don Guillett Microsoft MVP Excel SalesAid Software "Jacky D." wrote in message ... I am trying to automate a sheet in a workbook that has worksheets being added to it continually. I have created a worksheet that is designed to pull infomation from a selected workbook from 3 different areas of the worksheet. I have recorded a macro that goes into the workbook that the macro was recorded with, and pulls information as internded. Heres my problem. Can I change this macro so I can: 1-Select the workbook that the information is to be pulled from 2-Make the second range selection a variable? I want to tell it which cells to copy from. Here is the recorded nacro: Copy_from_balance_for_Validation Macro ' Macro recorded 5/21/2009 by Jacky D ' ' Range("A8").Select Sheets("Sheet2").Select Range("A3:AA3").Select Range("AA3").Activate Selection.Copy Sheets("Validation").Select Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=True Range("B8").Select Sheets("SHEET2").Select ActiveWindow.ScrollColumn = 83 Range("A29:AA29").Select Range("AA29").Activate Application.CutCopyMode = False Selection.Copy Sheets("Validation").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Range("C8").Select Sheets("SHEET2").Select Range("A1:AA1").Select Range("AA1").Activate Application.CutCopyMode = False Selection.Copy Sheets("Validation").Select Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=True End Sub Any row in the macro above that reads "Sheets("Sheet2").Select" I would want to make a variable. The Sheets("SHEET2").Select ActiveWindow.ScrollColumn = 83 Range("A29:AA29").Select Range("AA29").Activate" I would also want to select the range within the workbook. I know I need to define the worksheet that information is to be pulled from, and then the range and use an inputbox to do this, but I can't seem to get it right. Thanks so much for the help. Jacky D. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting worksheet and ranges within sheet to run macro on
I meant to use the drop down with list to select the file to copy from.
Then use an input box (or list to prevent errors) to define the sheet (IF not known) Then use an input box (or list to prevent errors) to define the range Or, seek professional help. -- Don Guillett Microsoft MVP Excel SalesAid Software "Jacky D." wrote in message ... Thanks, I think a drop down list could become unwieldly, there are currently 45 worksheets in this workbook, and more are added every day. They are named uniguely, and when created, they are placed near the sheet that I will run the macro within. I have no idea how to create a change event macro. I don't know how to write in VBA, but have been pretty successful in recording macros, and then tweaking to suit my needs. Wouldn't I want to keep the selections that are pointing to the cell ranges which I do not want to change? "Don Guillett" wrote: You could have a drop down list (tied to a change_event macro) to select the wordbook to fetch from. Then an input box asking for the sheet and range to copy from, etc. You should endeavor to remove all unnecessary "selections" and the scrolling is completely unnecessary. -- Don Guillett Microsoft MVP Excel SalesAid Software "Jacky D." wrote in message ... I am trying to automate a sheet in a workbook that has worksheets being added to it continually. I have created a worksheet that is designed to pull infomation from a selected workbook from 3 different areas of the worksheet. I have recorded a macro that goes into the workbook that the macro was recorded with, and pulls information as internded. Heres my problem. Can I change this macro so I can: 1-Select the workbook that the information is to be pulled from 2-Make the second range selection a variable? I want to tell it which cells to copy from. Here is the recorded nacro: Copy_from_balance_for_Validation Macro ' Macro recorded 5/21/2009 by Jacky D ' ' Range("A8").Select Sheets("Sheet2").Select Range("A3:AA3").Select Range("AA3").Activate Selection.Copy Sheets("Validation").Select Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=True Range("B8").Select Sheets("SHEET2").Select ActiveWindow.ScrollColumn = 83 Range("A29:AA29").Select Range("AA29").Activate Application.CutCopyMode = False Selection.Copy Sheets("Validation").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Range("C8").Select Sheets("SHEET2").Select Range("A1:AA1").Select Range("AA1").Activate Application.CutCopyMode = False Selection.Copy Sheets("Validation").Select Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=True End Sub Any row in the macro above that reads "Sheets("Sheet2").Select" I would want to make a variable. The Sheets("SHEET2").Select ActiveWindow.ScrollColumn = 83 Range("A29:AA29").Select Range("AA29").Activate" I would also want to select the range within the workbook. I know I need to define the worksheet that information is to be pulled from, and then the range and use an inputbox to do this, but I can't seem to get it right. Thanks so much for the help. Jacky D. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro: selecting ranges from list of names | Excel Programming | |||
Selecting Contiguous Ranges on a Sheet | Excel Programming | |||
Rename Worksheet without Selecting the sheet | Excel Programming | |||
selecting 2 ranges, 1 Worksheet, printing on 1 page PDF | Excel Discussion (Misc queries) | |||
Macro to make a graph: help selecting the data ranges. | Excel Programming |