I need some help with a macro i want to run. It involves cutting a block of
data from a workbook, and pasting transposed into another workbook. So
basically i want to be able to run this macro that will copy the block of
data from data workbook, switch workbooks, paste transposed onto the new
workbook, move down for rows (the original data only has 4 columns), switch
back to the original work book, and then move to the next sheet. And then
repeat over and over and over again. etc. I need it to move down 4 rows so
its ready for the next sheet of data, so i can "stack" the data on top of
each other. Heres the code that i have when i recorded the macro

Range("E15:H24").Select
Selection.Copy
Windows("Data Sheet-2-20-08.xls").Activate
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
ActiveCell.Offset(0, 1).Select
Windows("Advocate.xls").Activate
ActiveSheet.Next.Select

The issue im having is that i need to remove the "Selection" line of code.
Each time i run the macro its going to always copy and paste to that cell.
What i need it to do is move down 4 rows each time, so i can "stack" the data
on top of each other.

Let me know if this makes sense, and if you can help me with this.

-Adam

Your explanation is a wee bit fuzzy to me, but I never let that stop me!
I presume the first select, Range("E15:H24").Select, is the area you're
selecting manually before running the macro; if so, that line needs to come
out of your VBA.

The piece of code that determines where you are on your second worksheet
after the paste, is ActiveCell.Offset(0, 1).Select. I believe changing that
to ActiveCell.Offset(Selection.Rows.Count + 4, 0).Select
will put you four rows below the information you just finished pasting
(i.e., your next block of information will start on the fifth row).


"Adam" wrote:

I need some help with a macro i want to run. It involves cutting a block of
data from a workbook, and pasting transposed into another workbook. So
basically i want to be able to run this macro that will copy the block of
data from data workbook, switch workbooks, paste transposed onto the new
workbook, move down for rows (the original data only has 4 columns), switch
back to the original work book, and then move to the next sheet. And then
repeat over and over and over again. etc. I need it to move down 4 rows so
its ready for the next sheet of data, so i can "stack" the data on top of
each other. Heres the code that i have when i recorded the macro

Range("E15:H24").Select
Selection.Copy
Windows("Data Sheet-2-20-08.xls").Activate
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
ActiveCell.Offset(0, 1).Select
Windows("Advocate.xls").Activate
ActiveSheet.Next.Select

The issue im having is that i need to remove the "Selection" line of code.
Each time i run the macro its going to always copy and paste to that cell.
What i need it to do is move down 4 rows each time, so i can "stack" the data
on top of each other.

Let me know if this makes sense, and if you can help me with this.

-Adam

Larry, thanks for your reply.

Yea the offset code was a type, is should read (4, 0). Yes, the E15:H24 is
the range of data i need to cut and paste transposed onto a new sheet. I
have like 1000 unique sheets with the data in the E:15:h24 format. I need to
make a macro that will cut and paste transposed onto the new sheet, stacking
the data vertically down on the sheet. Does that make sense?


I will give this a shot and see what happends. Thanks!!!

-Adam

"LarryP" wrote:

Your explanation is a wee bit fuzzy to me, but I never let that stop me!
I presume the first select, Range("E15:H24").Select, is the area you're
selecting manually before running the macro; if so, that line needs to come
out of your VBA.

The piece of code that determines where you are on your second worksheet
after the paste, is ActiveCell.Offset(0, 1).Select. I believe changing that
to ActiveCell.Offset(Selection.Rows.Count + 4, 0).Select
will put you four rows below the information you just finished pasting
(i.e., your next block of information will start on the fifth row).


"Adam" wrote:

I need some help with a macro i want to run. It involves cutting a block of
data from a workbook, and pasting transposed into another workbook. So
basically i want to be able to run this macro that will copy the block of
data from data workbook, switch workbooks, paste transposed onto the new
workbook, move down for rows (the original data only has 4 columns), switch
back to the original work book, and then move to the next sheet. And then
repeat over and over and over again. etc. I need it to move down 4 rows so
its ready for the next sheet of data, so i can "stack" the data on top of
each other. Heres the code that i have when i recorded the macro

Range("E15:H24").Select
Selection.Copy
Windows("Data Sheet-2-20-08.xls").Activate
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
ActiveCell.Offset(0, 1).Select
Windows("Advocate.xls").Activate
ActiveSheet.Next.Select

The issue im having is that i need to remove the "Selection" line of code.
Each time i run the macro its going to always copy and paste to that cell.
What i need it to do is move down 4 rows each time, so i can "stack" the data
on top of each other.

Let me know if this makes sense, and if you can help me with this.

-Adam

Okay, I tried it a couple times so I'm pretty sure the bit of code I
suggested will position your cursor correctly in your "paste-to" sheet.

Since your "copy-from" sheets ALWAYS have the data in E15.H24, you should
also be able to extend the macro so it'll just crunch through your workbook
sheet by sheet, grab that chunk of data, and paste it into your target sheet.
That would require some kind of test, though, to be sure it only does its
thing on the appropriate sheets: maybe a consistent way of naming those
sheets, or a field somewhere on those sheets that always has a particular
"flag" value, or whatever. If you have a thousand (!!) sheets, that would
certainly be worth the effort. Good luck!

"Adam" wrote:

Larry, thanks for your reply.

Yea the offset code was a type, is should read (4, 0). Yes, the E15:H24 is
the range of data i need to cut and paste transposed onto a new sheet. I
have like 1000 unique sheets with the data in the E:15:h24 format. I need to
make a macro that will cut and paste transposed onto the new sheet, stacking
the data vertically down on the sheet. Does that make sense?


I will give this a shot and see what happends. Thanks!!!

-Adam

"LarryP" wrote:

Your explanation is a wee bit fuzzy to me, but I never let that stop me!
I presume the first select, Range("E15:H24").Select, is the area you're
selecting manually before running the macro; if so, that line needs to come
out of your VBA.

The piece of code that determines where you are on your second worksheet
after the paste, is ActiveCell.Offset(0, 1).Select. I believe changing that
to ActiveCell.Offset(Selection.Rows.Count + 4, 0).Select
will put you four rows below the information you just finished pasting
(i.e., your next block of information will start on the fifth row).


"Adam" wrote:

I need some help with a macro i want to run. It involves cutting a block of
data from a workbook, and pasting transposed into another workbook. So
basically i want to be able to run this macro that will copy the block of
data from data workbook, switch workbooks, paste transposed onto the new
workbook, move down for rows (the original data only has 4 columns), switch
back to the original work book, and then move to the next sheet. And then
repeat over and over and over again. etc. I need it to move down 4 rows so
its ready for the next sheet of data, so i can "stack" the data on top of
each other. Heres the code that i have when i recorded the macro

Range("E15:H24").Select
Selection.Copy
Windows("Data Sheet-2-20-08.xls").Activate
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
ActiveCell.Offset(0, 1).Select
Windows("Advocate.xls").Activate
ActiveSheet.Next.Select

The issue im having is that i need to remove the "Selection" line of code.
Each time i run the macro its going to always copy and paste to that cell.
What i need it to do is move down 4 rows each time, so i can "stack" the data
on top of each other.

Let me know if this makes sense, and if you can help me with this.

-Adam

Here's the generic answer. If you can't figure it out from this I'll try to
set up the actual VBA for you, but haven't got time to do that right this
minute (Monday Morning Syndrome).

(1) Declare an object variable, e.g., mySheet
(2) Set up a For Each loop, e.g., For Each mySheet in ActiveWorkbook.Sheets
(3) Within the loop, test each sheet for whatever flag you've decided on,
e.g.,
If mySheet.Name starts with "Data" or If mySheet.Range("AA1").Value = "x"
(4) Within the If statement, write code to do whatever it is you want done
repeatedly, e.g., in your case, copy E15:H24 and paste it into your other
sheet
(5) Then use Loop to move on to the next sheet





"Adam" wrote:

Thanks Larry,

One question, how do i code in to have it repeat the macro so it does it for
every sheet on the workbook?

Thanks,

-adam

"LarryP" wrote:

Okay, I tried it a couple times so I'm pretty sure the bit of code I
suggested will position your cursor correctly in your "paste-to" sheet.

Since your "copy-from" sheets ALWAYS have the data in E15.H24, you should
also be able to extend the macro so it'll just crunch through your workbook
sheet by sheet, grab that chunk of data, and paste it into your target sheet.
That would require some kind of test, though, to be sure it only does its
thing on the appropriate sheets: maybe a consistent way of naming those
sheets, or a field somewhere on those sheets that always has a particular
"flag" value, or whatever. If you have a thousand (!!) sheets, that would
certainly be worth the effort. Good luck!

"Adam" wrote:

Larry, thanks for your reply.

Yea the offset code was a type, is should read (4, 0). Yes, the E15:H24 is
the range of data i need to cut and paste transposed onto a new sheet. I
have like 1000 unique sheets with the data in the E:15:h24 format. I need to
make a macro that will cut and paste transposed onto the new sheet, stacking
the data vertically down on the sheet. Does that make sense?


I will give this a shot and see what happends. Thanks!!!

-Adam

"LarryP" wrote:

Your explanation is a wee bit fuzzy to me, but I never let that stop me!
I presume the first select, Range("E15:H24").Select, is the area you're
selecting manually before running the macro; if so, that line needs to come
out of your VBA.

The piece of code that determines where you are on your second worksheet
after the paste, is ActiveCell.Offset(0, 1).Select. I believe changing that
to ActiveCell.Offset(Selection.Rows.Count + 4, 0).Select
will put you four rows below the information you just finished pasting
(i.e., your next block of information will start on the fifth row).


"Adam" wrote:

I need some help with a macro i want to run. It involves cutting a block of
data from a workbook, and pasting transposed into another workbook. So
basically i want to be able to run this macro that will copy the block of
data from data workbook, switch workbooks, paste transposed onto the new
workbook, move down for rows (the original data only has 4 columns), switch
back to the original work book, and then move to the next sheet. And then
repeat over and over and over again. etc. I need it to move down 4 rows so
its ready for the next sheet of data, so i can "stack" the data on top of
each other. Heres the code that i have when i recorded the macro

Range("E15:H24").Select
Selection.Copy
Windows("Data Sheet-2-20-08.xls").Activate
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
ActiveCell.Offset(0, 1).Select
Windows("Advocate.xls").Activate
ActiveSheet.Next.Select

The issue im having is that i need to remove the "Selection" line of code.
Each time i run the macro its going to always copy and paste to that cell.
What i need it to do is move down 4 rows each time, so i can "stack" the data
on top of each other.

Let me know if this makes sense, and if you can help me with this.

-Adam