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

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

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.

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.

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.

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.

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