Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a variable range in Visual Basic
I've created a macro that will filter specific data from a sheet and copy
that filtered data to another sheet for a "watchlist". There are multiple sheets to be filtered, and I'd like to be able to have the copied information paste in at the end of the previous data on the "watchlist" sheet. I'm not really good with Visual Basic, but can usually muddle my way through. Is there code I can use to make the range variable, so the info will paste where I need it to? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a variable range in Visual Basic
Add this near the beginning of your existing macro.
Dim lr As Long, sh2 As Worksheet Set sh2 = Sheets("watchlist") 'Set the receiving sht to a variable ' The next line finds the last cell with data in col A and adds 1 lr = sh2.Cells(Rows.Count, 1).End(xlUp).Row + 1 'Your existing code here Selection.Copy sh2.Range("A" & lr) That last line can be modified. I used Selection because the actual code being currently used was not posted, But that is the line that will put the data on the "watchlist" sheet in the next blank cell of column A and will post the entire copied range.. "Deby" wrote in message ... I've created a macro that will filter specific data from a sheet and copy that filtered data to another sheet for a "watchlist". There are multiple sheets to be filtered, and I'd like to be able to have the copied information paste in at the end of the previous data on the "watchlist" sheet. I'm not really good with Visual Basic, but can usually muddle my way through. Is there code I can use to make the range variable, so the info will paste where I need it to? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a variable range in Visual Basic
This is the code that was created with the macro by using a criteria and
extract range (works better for the info requested): Rows("21:3039").Select Selection.Delete Shift:=xlUp Range("A13").Select Range("AR").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _ "A6:U8"), CopyToRange:=Range("A20:S20"), Unique:=False Rows("21:21").Select Selection.Insert Shift:=xlDown Selection.ClearFormats Range("A22").Select Selection.CurrentRegion.Select Selection.Copy Sheets("Watchlist").Select Range("A4").Select Selection.End(xlDown).Select Range("A6").Select ActiveSheet.Paste Sheets("Criteria-Extract").Select Rows("21:21").Select ActiveSheet.Next.Select ActiveSheet.Next.Select ActiveSheet.Next.Select Sheets("Criteria-Extract").Select Rows("21:4084").Select Application.CutCopyMode = False Selection.Delete Shift:=xlUp Range("A13").Select Range("MT").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _ "A6:U8"), CopyToRange:=Range("A20:S20"), Unique:=False Rows("21:21").Select Selection.Insert Shift:=xlDown Selection.ClearFormats Range("A22").Select Selection.CurrentRegion.Select Selection.Copy Sheets("Watchlist").Select Range("A4").Select Selection.End(xlDown).Select Range("A20").Select ActiveSheet.Paste Sheets("Criteria-Extract").Select Thanks!!! "JLGWhiz" wrote: Add this near the beginning of your existing macro. Dim lr As Long, sh2 As Worksheet Set sh2 = Sheets("watchlist") 'Set the receiving sht to a variable ' The next line finds the last cell with data in col A and adds 1 lr = sh2.Cells(Rows.Count, 1).End(xlUp).Row + 1 'Your existing code here Selection.Copy sh2.Range("A" & lr) That last line can be modified. I used Selection because the actual code being currently used was not posted, But that is the line that will put the data on the "watchlist" sheet in the next blank cell of column A and will post the entire copied range.. "Deby" wrote in message ... I've created a macro that will filter specific data from a sheet and copy that filtered data to another sheet for a "watchlist". There are multiple sheets to be filtered, and I'd like to be able to have the copied information paste in at the end of the previous data on the "watchlist" sheet. I'm not really good with Visual Basic, but can usually muddle my way through. Is there code I can use to make the range variable, so the info will paste where I need it to? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Visual Basic Macro | Excel Discussion (Misc queries) | |||
setting variable direction in visual basic | Excel Programming | |||
Creating a folder using Visual Basic HELP!!! | Excel Programming | |||
VBE (visual Basic Editor) variable | Excel Programming | |||
Visual Basic Range variable | Excel Programming |