Need a Macro

[speedy]

Someone please help me. I wrote a message before and I never got the answer
or the macro that I wanted that worked.

I will try to give as much detail as I can so someone may be able to help
me. I am looking for a macro that will take the list of name ranges that I
have created. The range names that I created are called pg1, pg2, and so on
and so on. These range names are in a workbook and are part of the
worksheets within the work so the pg's will always be different.

I want the macro to go get the range name copy the range name and paste
special value, transpose, to a sheet that I have in the workbook called data.
After completing this I want the macro to go and do the next range name
which would be pg2 and do the same thing as pg1 but I want it posted just
under the data that was posted from pg1 into the data worksheet.

I have create a quick macro from the recorder but it is only good for one
pg1. I tried to just copy this macro to do 10 pg's but it will not work
correctly. Please help. Below you will see what I am trying to do but like
I said this is just for 1 page I need something that will work for unlimited
pg's

Application.Goto Reference:="pg1"
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("data").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select

 

[OssieMac]

Hi Mr Speedy,

In the following macro, if your Data sheet has a header then the code will
accommodate that but if no header then it will leave row 1 blank.

Edit the sheet name to match your sheet name with the original data and
named ranges pg1 etc. (If you scoped the defined names to workbook and not
the specific sheet then selecting the sheet is not required.)

Edit the line Loop While lngPgRef < 5 to the number of pg's that you have.
If it exceeds the number of pg's then it will error out when it gets to one
it can't find.

I assume that you are NOT using xl2007 because pg1, pg2 etc are reserved
names but they appear to work OK in xl2002.

Note that a space and underscore is a line break in an otherwise single line
of code.

Sub CopyAndTranpose()
Dim strPgRef As String
Dim lngPgRef As Long

'Edit following line to match sheet name with defined names Pg1 etc
Sheets("Sheet1").Select

Do

lngPgRef = lngPgRef + 1
strPgRef = "pg" & lngPgRef
MsgBox strPgRef
Range(strPgRef).Copy
Sheets("data").Cells(Rows.Count, "A") _
.End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=True

Loop While lngPgRef < 5

End Sub

 

[speedy]

Mr OssieMac

That is just what I was look for. It even tells me what page I am copying.
I am going to try going over this because I need to learn this macro. Again
thanks a lot you are saving me a lot of work.

 

[OssieMac]

Hi Again Mr Speedy,

Your comment "It even tells me what page I am copying". I didn't intend to
leave that line of code in when I posted the answer. I was using it during
testing. If it becomes a pain clicking on the OK all the time then just
comment it out with a single quote at the beginning of the line like the
following.

'MsgBox strPgRef

Also as you want to understand the code here is some explanation for you as
to what the code does.

Dim strPgRef As String
The above dimensions a string variable. That is it holds data that would
normally be enclosed between double quotes.

Dim lngPgRef As Long
The above dimensions a numeric variable. There are several types of numeric
variables and you can look them up in VBA help under Declaring Variables.


lngPgRef = lngPgRef + 1
The above line is a numeric variable that starts at zero and gets
incremented by 1 on each loop of the code.

strPgRef = "pg" & lngPgRef
The above line is a string variable that is concatenated with the numeric
variable. Like "pg" & 1 to give "pg1"

Range(strPgRef).Copy
The above line uses the VBA string variable in lieu of an actual string.
Note that when using a variable in lieu of an actual string, it is not
enclosed in double quotes. It is like Range("Pg1").Copy.
Don’t confuse VBA variables with named ranges on a worksheet. Named ranges
on a worksheet are used in lieu of actual range addresses and are saved with
the worksheet and generally VBA variables lose their value after the code has
run.

The following explanation is breaking up a single line of code and
explaining each section of the line.

Sheets("data").Cells(Rows.Count, "A")
The above section of code is like selecting the last cell in column A.
Rows.Count is a variable that hold the total number of rows on the sheet
which is row 65536 (or a million and something is xl2007).

..End(xlUp)
The above is like having the last cell in column A selected and then holding
the Ctrl key and pressing the Up arrow. It takes you to the top of the sheet
if there no data in column A or to the first cell it finds with data.

..Offset(1, 0)
The above is like having a cell selected and pressing the down arrow once to
go to the next cell. Therefore if the previous .End(xlUp) took you to the top
of the sheet then this will take you to row 2. If .End(xlUp) found data and
stopped on the cell with data then you want to go down one cell to a blank
row.

I think that the rest of that line of code is self explanatory as is
Loop While lngPgRef < 5

Note: When using VBA Help you must have the VBA editor open and you must
select Help from the VBA menu. If you have help open on the worksheet and
change to the VBA editor and simply change windows to the already open Help
then you will get the worksheet help and will not be able to find what you
need for VBA.

Similarly if you change back to the worksheet when you have VBA help open
then you need to select Help from the worksheet menu and not just change
windows to the already open VBA help ptherwise you will not be able to find
the correct info for the worksheet.