EXCEL CELL REFERENCES AS VARIABLES

[PERRY]

I am using Office 2000, and my sole learning source is
EXCEL HELP. At present I use cut and paste to get all the
data from sheet1 onto sheet2 so I can print it as one page
with column headings. My goal is to copy imported data
from Sheet1 to two separate columns on Sheet2
automatically. The problem is the number of rows of data
on sheet1 is variable. I envision entering the number of
rows into a reference cell and the layout of the data on
sheet2 to happen automatically (or can I determine the
number of rows automatically also?). How can I make a
cell reference into a variable so I can split the rows
into two sets of columns? In other words, Sheet1 A1:A5
and A6:A10 would be put in columns A and G of sheet2
respectively. The next time it could be A1:A26 and
A27:A51 (an odd # of rows)

 

[Debra Dalgleish]

You could use a macro to copy the data onto sheet2:

'======================
Sub CopyPasteSplit()
Dim ws As Worksheet
Dim ws2 As Worksheet
Set ws = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

Dim r As Long
r = ws.Cells(Rows.Count, 1).End(xlUp).Row

ws.Range("A1:A" & Int(r / 2)).Copy _
Destination:=ws2.Range("A1")
ws.Range("A" & Int(r / 2) + 1 & ":A" & r).Copy _
Destination:=ws2.Range("G1")
End Sub
'=======================

To store the macro code:
1. Hold the Alt key and press F11, to open the Visual Basic Editor.
2. In the Project Explorer, at the left, find and select your workbook
3. Choose Insert>Module
4. Copy the above code, and paste it onto the module sheet.
5. Close the Visual Basic Editor and return to Excel

To run the macro:
1. Choose Tools>Macro>Macros
2. From the list of macros, choose CopyPasteSplit
3. Click Run.

Or, you can create a button to run the macro
1. Right-click any toolbar at the top of the worksheet.
2. From the list of toolbars, choose Forms
3. Use the Command button tool to add a button to the worksheet.
4. The Assign Macro dialog box should open.
5. Choose your macro, then click OK.
6. Close the Forms toolbar.