| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to add row and past values on new worksheet. HLP
I need to automate some tasks in my workbook.
Let me explain. In one worksheet I have a web query updated daily. I need to copy different row values from the query, into a new sixth row, on each of the 20 sheets. Is there a way to make this a single event instead of 20? Any help with the code? I found http://www.mvps.org/dmcritchie/excel/insrtrow.htm is of great help but I dont get 99% of the instructions. Cant distinguish functions from variables in most cases. Bruno |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to add row and past values on new worksheet. HLP
You need to write a macro to move the data to the 20 worksheets. You can
either run the macro manually or use an AFTER Query event to run the macro. See in VBA help Using Events with the QueryTable Object and AfterRefresh Event You will need to add a CLASS MODULE to the VBA Project for this code to run. "Bruno" wrote: I need to automate some tasks in my workbook. Let me explain. In one worksheet I have a web query updated daily. I need to copy different row values from the query, into a new sixth row, on each of the 20 sheets. Is there a way to make this a single event instead of 20? Any help with the code? I found http://www.mvps.org/dmcritchie/excel/insrtrow.htm is of great help but I dont get 99% of the instructions. Cant distinguish functions from variables in most cases. Bruno |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to add row and past values on new worksheet. HLP
That looks hard to achieve.
I was thinking in a macro that would add the 6th row joined with another that would past special from the query worksheet. Seems easier. I found some examples http://www.mvps.org/dmcritchie/excel/insrtrow.htm to add row and paste special but none match what I need. Any help there with the VBA code? "Joel" wrote: You need to write a macro to move the data to the 20 worksheets. You can either run the macro manually or use an AFTER Query event to run the macro. See in VBA help Using Events with the QueryTable Object and AfterRefresh Event You will need to add a CLASS MODULE to the VBA Project for this code to run. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to add row and past values on new worksheet. HLP
Writng a macro that moves the data from the query sheet t the 20 other sheets
is simple. I just need the details of the sheet names and the row number on the query sheet and the rows where the data goes in the 20 Sheets. The Rows can be just added after the data that already exists. The code can be a simple loop or just 20 copy instructions. This is not complicated. What would be complicated is making it automatic after the query is updated. I haven't done this before a would take me a few minutes to figure it out. The VBA help isn't very clear on how to do it. "Bruno" wrote: That looks hard to achieve. I was thinking in a macro that would add the 6th row joined with another that would past special from the query worksheet. Seems easier. I found some examples http://www.mvps.org/dmcritchie/excel/insrtrow.htm to add row and paste special but none match what I need. Any help there with the VBA code? "Joel" wrote: You need to write a macro to move the data to the 20 worksheets. You can either run the macro manually or use an AFTER Query event to run the macro. See in VBA help Using Events with the QueryTable Object and AfterRefresh Event You will need to add a CLASS MODULE to the VBA Project for this code to run. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to add row and past values on new worksheet. HLP
Here's what i've been able to crunch from other examples.
Joel can you help me select all 20 sheets or make this a simple macro to run? Sub Insert_Rows_Loop() ' INSERT NEW 6TH ROW ON ALL SELECTED SHEETS IN MY CASE 20!! NEED TO SOLVE THIS... Dim CurrentSheet As Object For Each CurrentSheet In ActiveWindow.SelectedSheets CurrentSheet.Range("a6").EntireRow.Insert Next CurrentSheet End Sub Sub CopyAndPaste() ' SIMPLEST PASTESPECIAL VALUES CODE I FOUND REPEATED AGAIN 20 TIMES, TO PASTE ON THE NEW A6 ROW Worksheets("INTRADAY").Range("A7:N7").Copy Set Rng = Selection Worksheets("ALTR").Range("A6").PasteSpecial xlValues Rng.Select Worksheets("INTRADAY").Range("A8:N8").Copy Set Rng = Selection Worksheets("BCP").Range("A6").PasteSpecial xlValues Rng.Select Worksheets("INTRADAY").Range("A9:N9").Copy Set Rng = Selection Worksheets("BES").Range("A6").PasteSpecial xlValues Rng.Select .... End Sub "Joel" wrote: Writng a macro that moves the data from the query sheet t the 20 other sheets is simple. I just need the details of the sheet names and the row number on the query sheet and the rows where the data goes in the 20 Sheets. The Rows can be just added after the data that already exists. The code can be a simple loop or just 20 copy instructions. This is not complicated. What would be complicated is making it automatic after the query is updated. I haven't done this before a would take me a few minutes to figure it out. The VBA help isn't very clear on how to do it. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to add row and past values on new worksheet. HLP
I tried to make the code very robust (work under any condition). Because the
order of worksheets can change in a workbook I didn't want to rely on somebody switching the order of the sheets. To do this requires entering each sheet name into the macro. Also if somebody added more sheets this would also cause errors if sheet names weren't listed in the code. I also used Rows incase you added additional columns to your data Sub CopyAndPaste() for each sht in sheets select case sht.name case "ALTR": SourceRow = 7 case "BCP": SourceRow = 8 case "BES": SourceRow = 9 case "XXX": SourceRow = 10 case "XXX": SourceRow = 11 case "XXX": SourceRow = 12 case "XXX": SourceRow = 13 case "XXX": SourceRow = 14 case "XXX": SourceRow = 15 case "XXX": SourceRow = 16 case "XXX": SourceRow = 17 case "XXX": SourceRow = 18 case "XXX": SourceRow = 19 case "XXX": SourceRow = 20 case "XXX": SourceRow = 21 case "XXX": SourceRow = 22 case "XXX": SourceRow = 23 case "XXX": SourceRow = 24 case "XXX": SourceRow = 25 case "XXX": SourceRow = 26 case else : SourceRow = 0 end select if SourceRow 0 then Worksheets("INTRADAY").Rows(SourceRow).Copy sht.Rows(6).PasteSpecial xlValues Rng.Select end if next sht End Sub "Bruno" wrote: Here's what i've been able to crunch from other examples. Joel can you help me select all 20 sheets or make this a simple macro to run? Sub Insert_Rows_Loop() ' INSERT NEW 6TH ROW ON ALL SELECTED SHEETS IN MY CASE 20!! NEED TO SOLVE THIS... Dim CurrentSheet As Object For Each CurrentSheet In ActiveWindow.SelectedSheets CurrentSheet.Range("a6").EntireRow.Insert Next CurrentSheet End Sub Sub CopyAndPaste() ' SIMPLEST PASTESPECIAL VALUES CODE I FOUND REPEATED AGAIN 20 TIMES, TO PASTE ON THE NEW A6 ROW Worksheets("INTRADAY").Range("A7:N7").Copy Set Rng = Selection Worksheets("ALTR").Range("A6").PasteSpecial xlValues Rng.Select Worksheets("INTRADAY").Range("A8:N8").Copy Set Rng = Selection Worksheets("BCP").Range("A6").PasteSpecial xlValues Rng.Select Worksheets("INTRADAY").Range("A9:N9").Copy Set Rng = Selection Worksheets("BES").Range("A6").PasteSpecial xlValues Rng.Select .... End Sub "Joel" wrote: Writng a macro that moves the data from the query sheet t the 20 other sheets is simple. I just need the details of the sheet names and the row number on the query sheet and the rows where the data goes in the 20 Sheets. The Rows can be just added after the data that already exists. The code can be a simple loop or just 20 copy instructions. This is not complicated. What would be complicated is making it automatic after the query is updated. I haven't done this before a would take me a few minutes to figure it out. The VBA help isn't very clear on how to do it. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to add row and past values on new worksheet. HLP
JOEL YOU MADE IT LOOK SO SIMPLE!!
for with an if... DAHH!! I've cleaned up Rng.Select and added sht.Range("a6").EntireRow.Insert AND BANG!! MADE IT IN ONE MACRO!!! THANK GOOD I HAD PASCAL 10 YEARS AGO... On the next days i'll try to make it a button event macro, and explore it for recalculating previous formulas THANK YOU VERY MUCH JOEL Sub CopyAndPaste() For Each sht In Sheets Select Case sht.Name Case "ALTR": SourceRow = 7 Case "BCP": SourceRow = 8 Case Else: SourceRow = 0 End Select If SourceRow 0 Then sht.Range("a6").EntireRow.Insert Worksheets("INTRADAY").Rows(SourceRow).Copy sht.Rows(6).PasteSpecial xlValues End If Next sht End Sub "Joel" wrote: I tried to make the code very robust (work under any condition). Because the order of worksheets can change in a workbook I didn't want to rely on somebody switching the order of the sheets. To do this requires entering each sheet name into the macro. Also if somebody added more sheets this would also cause errors if sheet names weren't listed in the code. I also used Rows incase you added additional columns to your data Sub CopyAndPaste() for each sht in sheets select case sht.name case "ALTR": SourceRow = 7 case "BCP": SourceRow = 8 case "BES": SourceRow = 9 case "XXX": SourceRow = 10 case "XXX": SourceRow = 11 case "XXX": SourceRow = 12 case "XXX": SourceRow = 13 case "XXX": SourceRow = 14 case "XXX": SourceRow = 15 case "XXX": SourceRow = 16 case "XXX": SourceRow = 17 case "XXX": SourceRow = 18 case "XXX": SourceRow = 19 case "XXX": SourceRow = 20 case "XXX": SourceRow = 21 case "XXX": SourceRow = 22 case "XXX": SourceRow = 23 case "XXX": SourceRow = 24 case "XXX": SourceRow = 25 case "XXX": SourceRow = 26 case else : SourceRow = 0 end select if SourceRow 0 then Worksheets("INTRADAY").Rows(SourceRow).Copy sht.Rows(6).PasteSpecial xlValues Rng.Select end if next sht End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy and Past Values AND Formatting AND Text | Excel Discussion (Misc queries) | |||
| Copy Past Values | Excel Discussion (Misc queries) | |||
| Copy and past as values | Excel Programming | |||
| record values from past dates | Excel Discussion (Misc queries) | |||
| Select row by first cell's contents, then past in values. | Excel Programming | |||
Linear Mode
