Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
table preps for xls external data refresh
Randy
Here's one way. Comments in the cell, but post back if you have questions. Sub MoveTotals() Dim cell As Range Dim Rng As Range Dim DestRng As Range Dim sh As Worksheet, sh2 As Worksheet 'Identify the sheet to pull from Set sh = ThisWorkbook.Sheets("Sheet1") 'Identify the sheet to write to Set sh2 = ThisWorkbook.Sheets("Sheet2") 'Set up a range of column D to loop through Set Rng = Intersect(sh.Columns(4), sh.UsedRange) 'Set up the first cell to write Set DestRng = sh2.Range("A400") 'Loop through the cells For Each cell In Rng.Cells 'If it's a total row If Right(cell, 5) = "Total" Then 'Copy the whole row and paste to destrng cell.EntireRow.Copy DestRng 'Move destrng down one row for next total Set DestRng = DestRng.Offset(1, 0) End If Next cell End Sub -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Randy" wrote in message ... Need some code that will copy nine subtotalled rows to a remote range in same worksheet, as a preparation for identifying the remote range as a table. This table would then become the object of an external data refresh in another worksheet of the same workbook. The data in the subtotalled worksheet varies as to the number of rows, so I can't directly reference the s/t rows in the data set as the table object. Thus the need to copy the contents of the s/t rows to a remote range. The s/t labels are always in column D (Internal Total, Shared Total, etc), so I would like a way to programatically: 1. identify the s/t rows 2. copy the entire values of each of the nine s/t rows to a remote range in same worksheet (A400, A401, etc). I'm not a skilled VBA programmer. This is the beginning code I've been working with, but I'm way short of a solution. If "RIGHT($D2,5)=""Total""" Then ActiveRows.Select Selection.Copy Range("A400").Select Selection.PasteSpecial Paste:=xlValues,Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False EndIf Thanks for your help! Randy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
table preps for xls external data refresh
Hi Dick, thanks for the help!
Sorry to bother you further, but I'm Having a problem with the For loop in your code. That statement results in "Run- time error '91': Object variable or With block variable not set". I'm not sure what to do at this point. Can you bail me out? Randy -----Original Message----- Randy Here's one way. Comments in the cell, but post back if you have questions. Sub MoveTotals() Dim cell As Range Dim Rng As Range Dim DestRng As Range Dim sh As Worksheet, sh2 As Worksheet 'Identify the sheet to pull from Set sh = ThisWorkbook.Sheets("Sheet1") 'Identify the sheet to write to Set sh2 = ThisWorkbook.Sheets("Sheet2") 'Set up a range of column D to loop through Set Rng = Intersect(sh.Columns(4), sh.UsedRange) 'Set up the first cell to write Set DestRng = sh2.Range("A400") 'Loop through the cells For Each cell In Rng.Cells 'If it's a total row If Right(cell, 5) = "Total" Then 'Copy the whole row and paste to destrng cell.EntireRow.Copy DestRng 'Move destrng down one row for next total Set DestRng = DestRng.Offset(1, 0) End If Next cell End Sub -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Randy" wrote in message ... Need some code that will copy nine subtotalled rows to a remote range in same worksheet, as a preparation for identifying the remote range as a table. This table would then become the object of an external data refresh in another worksheet of the same workbook. The data in the subtotalled worksheet varies as to the number of rows, so I can't directly reference the s/t rows in the data set as the table object. Thus the need to copy the contents of the s/t rows to a remote range. The s/t labels are always in column D (Internal Total, Shared Total, etc), so I would like a way to programatically: 1. identify the s/t rows 2. copy the entire values of each of the nine s/t rows to a remote range in same worksheet (A400, A401, etc). I'm not a skilled VBA programmer. This is the beginning code I've been working with, but I'm way short of a solution. If "RIGHT($D2,5)=""Total""" Then ActiveRows.Select Selection.Copy Range("A400").Select Selection.PasteSpecial Paste:=xlValues,Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False EndIf Thanks for your help! Randy . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
table preps for xls external data refresh
Randy
Which line is highlighted when you get that message and hit Debug? -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Randy" wrote in message ... Hi Dick, thanks for the help! Sorry to bother you further, but I'm Having a problem with the For loop in your code. That statement results in "Run- time error '91': Object variable or With block variable not set". I'm not sure what to do at this point. Can you bail me out? Randy -----Original Message----- Randy Here's one way. Comments in the cell, but post back if you have questions. Sub MoveTotals() Dim cell As Range Dim Rng As Range Dim DestRng As Range Dim sh As Worksheet, sh2 As Worksheet 'Identify the sheet to pull from Set sh = ThisWorkbook.Sheets("Sheet1") 'Identify the sheet to write to Set sh2 = ThisWorkbook.Sheets("Sheet2") 'Set up a range of column D to loop through Set Rng = Intersect(sh.Columns(4), sh.UsedRange) 'Set up the first cell to write Set DestRng = sh2.Range("A400") 'Loop through the cells For Each cell In Rng.Cells 'If it's a total row If Right(cell, 5) = "Total" Then 'Copy the whole row and paste to destrng cell.EntireRow.Copy DestRng 'Move destrng down one row for next total Set DestRng = DestRng.Offset(1, 0) End If Next cell End Sub -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Randy" wrote in message ... Need some code that will copy nine subtotalled rows to a remote range in same worksheet, as a preparation for identifying the remote range as a table. This table would then become the object of an external data refresh in another worksheet of the same workbook. The data in the subtotalled worksheet varies as to the number of rows, so I can't directly reference the s/t rows in the data set as the table object. Thus the need to copy the contents of the s/t rows to a remote range. The s/t labels are always in column D (Internal Total, Shared Total, etc), so I would like a way to programatically: 1. identify the s/t rows 2. copy the entire values of each of the nine s/t rows to a remote range in same worksheet (A400, A401, etc). I'm not a skilled VBA programmer. This is the beginning code I've been working with, but I'm way short of a solution. If "RIGHT($D2,5)=""Total""" Then ActiveRows.Select Selection.Copy Range("A400").Select Selection.PasteSpecial Paste:=xlValues,Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False EndIf Thanks for your help! Randy . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
table preps for xls external data refresh
For Each cell In Rng.Cells
-----Original Message----- Randy Which line is highlighted when you get that message and hit Debug? -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Randy" wrote in message ... Hi Dick, thanks for the help! Sorry to bother you further, but I'm Having a problem with the For loop in your code. That statement results in "Run- time error '91': Object variable or With block variable not set". I'm not sure what to do at this point. Can you bail me out? Randy -----Original Message----- Randy Here's one way. Comments in the cell, but post back if you have questions. Sub MoveTotals() Dim cell As Range Dim Rng As Range Dim DestRng As Range Dim sh As Worksheet, sh2 As Worksheet 'Identify the sheet to pull from Set sh = ThisWorkbook.Sheets("Sheet1") 'Identify the sheet to write to Set sh2 = ThisWorkbook.Sheets("Sheet2") 'Set up a range of column D to loop through Set Rng = Intersect(sh.Columns(4), sh.UsedRange) 'Set up the first cell to write Set DestRng = sh2.Range("A400") 'Loop through the cells For Each cell In Rng.Cells 'If it's a total row If Right(cell, 5) = "Total" Then 'Copy the whole row and paste to destrng cell.EntireRow.Copy DestRng 'Move destrng down one row for next total Set DestRng = DestRng.Offset(1, 0) End If Next cell End Sub -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Randy" wrote in message ... Need some code that will copy nine subtotalled rows to a remote range in same worksheet, as a preparation for identifying the remote range as a table. This table would then become the object of an external data refresh in another worksheet of the same workbook. The data in the subtotalled worksheet varies as to the number of rows, so I can't directly reference the s/t rows in the data set as the table object. Thus the need to copy the contents of the s/t rows to a remote range. The s/t labels are always in column D (Internal Total, Shared Total, etc), so I would like a way to programatically: 1. identify the s/t rows 2. copy the entire values of each of the nine s/t rows to a remote range in same worksheet (A400, A401, etc). I'm not a skilled VBA programmer. This is the beginning code I've been working with, but I'm way short of a solution. If "RIGHT($D2,5)=""Total""" Then ActiveRows.Select Selection.Copy Range("A400").Select Selection.PasteSpecial Paste:=xlValues,Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False EndIf Thanks for your help! Randy . . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
table preps for xls external data refresh
Randy
That's odd. The only problem I can think may be there is if Rng isn't what I think. Run it again, hit Debug and go to the immediate window (Control-G if it's not visible). Type ?Rng Is Nothing ?Rng.Address - if above is False ?sh.UsedRange.Address ?sh.Columns(4).Address ?Intersect(sh.Columns(4),sh.UsedRange).Address What do you get for those? Also, if you've made any changes to the code, post the code as you have it now. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Randy" wrote in message ... For Each cell In Rng.Cells -----Original Message----- Randy Which line is highlighted when you get that message and hit Debug? -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Randy" wrote in message ... Hi Dick, thanks for the help! Sorry to bother you further, but I'm Having a problem with the For loop in your code. That statement results in "Run- time error '91': Object variable or With block variable not set". I'm not sure what to do at this point. Can you bail me out? Randy -----Original Message----- Randy Here's one way. Comments in the cell, but post back if you have questions. Sub MoveTotals() Dim cell As Range Dim Rng As Range Dim DestRng As Range Dim sh As Worksheet, sh2 As Worksheet 'Identify the sheet to pull from Set sh = ThisWorkbook.Sheets("Sheet1") 'Identify the sheet to write to Set sh2 = ThisWorkbook.Sheets("Sheet2") 'Set up a range of column D to loop through Set Rng = Intersect(sh.Columns(4), sh.UsedRange) 'Set up the first cell to write Set DestRng = sh2.Range("A400") 'Loop through the cells For Each cell In Rng.Cells 'If it's a total row If Right(cell, 5) = "Total" Then 'Copy the whole row and paste to destrng cell.EntireRow.Copy DestRng 'Move destrng down one row for next total Set DestRng = DestRng.Offset(1, 0) End If Next cell End Sub -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Randy" wrote in message ... Need some code that will copy nine subtotalled rows to a remote range in same worksheet, as a preparation for identifying the remote range as a table. This table would then become the object of an external data refresh in another worksheet of the same workbook. The data in the subtotalled worksheet varies as to the number of rows, so I can't directly reference the s/t rows in the data set as the table object. Thus the need to copy the contents of the s/t rows to a remote range. The s/t labels are always in column D (Internal Total, Shared Total, etc), so I would like a way to programatically: 1. identify the s/t rows 2. copy the entire values of each of the nine s/t rows to a remote range in same worksheet (A400, A401, etc). I'm not a skilled VBA programmer. This is the beginning code I've been working with, but I'm way short of a solution. If "RIGHT($D2,5)=""Total""" Then ActiveRows.Select Selection.Copy Range("A400").Select Selection.PasteSpecial Paste:=xlValues,Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False EndIf Thanks for your help! Randy . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
table preps for xls external data refresh
Dick, results to your debug excercise in previous message
below. Also, only change to the code is to the set statement for sh2 ... changed the label to ("Sheet1") so the destrng would write to the same worksheet. Copied here to verify. Sub MoveTotals() Dim cell As Range Dim Rng As Range Dim DestRng As Range Dim sh As Worksheet, sh2 As Worksheet 'Identify the sheet to pull from Set sh = ThisWorkbook.Sheets("Sheet1") 'Identify the sheet to write to Set sh2 = ThisWorkbook.Sheets("Sheet1") 'Set up a range of column D to loop through Set Rng = Intersect(sh.Columns(4), sh.UsedRange) 'Set up the first cell to write Set DestRng = sh2.Range("A400") 'Loop through the cells For Each cell In Rng.Cells 'If it's a total row If Right(cell, 5) = "Total" Then 'Copy the whole row and paste to destrng cell.EntireRow.Copy DestRng 'Move destrng down one row for next total Set DestRng = DestRng.Offset(1, 0) End If Next cell End Sub -----Original Message----- Randy That's odd. The only problem I can think may be there is if Rng isn't what I think. Run it again, hit Debug and go to the immediate window (Control-G if it's not visible). Type ?Rng Is Nothing True ?Rng.Address - if above is False ?sh.UsedRange.Address $A$1 ?sh.Columns(4).Address $D:$D ?Intersect(sh.Columns(4),sh.UsedRange).Address Run-time error '91': Object variable or With block variable not set What do you get for those? Also, if you've made any changes to the code, post the code as you have it now. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Randy" wrote in message ... For Each cell In Rng.Cells -----Original Message----- Randy Which line is highlighted when you get that message and hit Debug? -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Randy" wrote in message ... Hi Dick, thanks for the help! Sorry to bother you further, but I'm Having a problem with the For loop in your code. That statement results in "Run- time error '91': Object variable or With block variable not set". I'm not sure what to do at this point. Can you bail me out? Randy -----Original Message----- Randy Here's one way. Comments in the cell, but post back if you have questions. Sub MoveTotals() Dim cell As Range Dim Rng As Range Dim DestRng As Range Dim sh As Worksheet, sh2 As Worksheet 'Identify the sheet to pull from Set sh = ThisWorkbook.Sheets("Sheet1") 'Identify the sheet to write to Set sh2 = ThisWorkbook.Sheets("Sheet2") 'Set up a range of column D to loop through Set Rng = Intersect(sh.Columns(4), sh.UsedRange) 'Set up the first cell to write Set DestRng = sh2.Range("A400") 'Loop through the cells For Each cell In Rng.Cells 'If it's a total row If Right(cell, 5) = "Total" Then 'Copy the whole row and paste to destrng cell.EntireRow.Copy DestRng 'Move destrng down one row for next total Set DestRng = DestRng.Offset(1, 0) End If Next cell End Sub -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Randy" wrote in message ... Need some code that will copy nine subtotalled rows to a remote range in same worksheet, as a preparation for identifying the remote range as a table. This table would then become the object of an external data refresh in another worksheet of the same workbook. The data in the subtotalled worksheet varies as to the number of rows, so I can't directly reference the s/t rows in the data set as the table object. Thus the need to copy the contents of the s/t rows to a remote range. The s/t labels are always in column D (Internal Total, Shared Total, etc), so I would like a way to programatically: 1. identify the s/t rows 2. copy the entire values of each of the nine s/t rows to a remote range in same worksheet (A400, A401, etc). I'm not a skilled VBA programmer. This is the beginning code I've been working with, but I'm way short of a solution. If "RIGHT($D2,5)=""Total""" Then ActiveRows.Select Selection.Copy Range("A400").Select Selection.PasteSpecial Paste:=xlValues,Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False EndIf Thanks for your help! Randy . . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
table preps for xls external data refresh
Randy
?Rng Is Nothing True ?Rng.Address - if above is False ?sh.UsedRange.Address $A$1 ?sh.Columns(4).Address $D:$D ?Intersect(sh.Columns(4),sh.UsedRange).Address Run-time error '91': Object variable or With block variable not set Do you expect to have nothing in column D when you run this sub? It loops through the cells in column D looking for entries that end in "Total", but if your UsedRange is A1, then apparently you don't have any information in column D. I'm sure I don't understand something here. Can you explain what you're trying to do again? -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Refresh table with external data? | Links and Linking in Excel | |||
Excel 2007 external data source extend table refresh | Excel Discussion (Misc queries) | |||
How do I refresh Pivot Table from an external source ? | Excel Worksheet Functions | |||
Pivot Table external XLS file source change and GETPIVOTDATA refresh | Excel Discussion (Misc queries) | |||
Pivot Table REFRESH Flaw -- Saves Old Data in Selection Area AFTER REFRESH | Excel Programming |