Copy Multiple Cells Efficiently

[gsgriffin]

I've tried many different approaches, but haven't been able to get this to work. In short, I need to have buttons on several different sheets automatically copy a range of cells to a final page. Example:

Button "3-5"...
Copies range of cell on Sheet 3 and then Pastes Cells on Page 10

This same process will take place for Button "4-1" (Sheet 4) and so on.

I'm looking for code that will allow the Button to specify the x,y starting points for the range to be copied and then a common loop for pasting the cells collected. I've got about 40 of these buttons to create and want to minimize my code for efficiency. I seem to be getting caught in specifying the sheet name for collecting the cells.

 

[VulcanJedi]

Might I suggest a userform with a refedit control? A refedit control is the control when you for instance graph and there is tiny box that lets the user select the cell range. The refedit control will return the string of the range I believe its an additional control you may have to select if not already available. Otherwise you could prompt a user for the values:

dim start, end as string
start=inputbox("what is the start cell&quot
(user inputs A1)
end = inputbox(what is the end cell&quot
(user inputs C1)

Otherwise.

sheets("Sheet10&quot.range("A1:C1&quot=Sheets("Sheet3" or simply the # [no quotes]).range("A1:C1&quot

sheets(10).range("A1:C1&quot=Sheets(3).range("A1:C1&quot Tranpkp
************************************
- Let me know if this helped/worked!

 

[gsgriffin]

This is the direction I've trying to head down, if it makes sense. I think descibing the problem is probably more difficult for me to do than for you to answer. See if the following code (term used loosely) gives you an idea.

(Code for Button)
PageName = "Wilderness 1"
startx = 1
starty = 628
Run "Copy_Cells"

(Code for Copy Cells)
'Need to copy information across a row and down a column
'This row,column changes for each different button

Dim y
Dim z
Dim myarray(40) As String


y = 2
z = 0
For x = 2 To 9
z = z + 1
'does this next line need to be an Object?
Set mycell = Workbook("Calendar2003&quot.Sheets(PageName).Cells(x + startx, y)
myarray(z) = mycell.Value
Next x

' the column to record is currently in row 8
x = 8
For y = 3 To 33
Set curcell = ExcelOb.Calendar2003.Sheets(PageName).Cells(x, y + starty)
z = z + 1
myarray(z) = curcell.Value
Next y

'This following function will alway paste the collected cells to the same location. I don't want to paste formulas, only values. A true copy and paste.

Run "Past_Cells"

Does this make more sense?

 

[VulcanJedi]

You aren't applying the SET command correctly. Set is like establishing a Constant.
For Ex:
set ac = application.sheets("sheet1&quot.activecell
--now I can use *ac* anywhere in code and program will sub *ac* with what the set statement says

I've made some changes based the code you provided, but I didn't think you would need loops, originally I think you could easily do this by using the range properties.

Are you simply copying values from a range of cells from one sheet to another? I would suggest this press Record Macro and do this once. Then view that macro and code could help you perform this task? Otherise I think the code below could help

/*/-*/-*/*-/-***/-*/-*

(Code for Button)
set mycell = application."Calendar2003&quot.sheets
dim PageName as string
dim startx, starty as integer

PageName = "Wilderness 1"
startx = 1
starty = 628
Run "Copy_Cells"

(Code for Copy Cells)
'Need to copy information across a row and down a column
'This row,column changes for each different button

Dim y
Dim z
Dim myarray(40) As String
set mycell = application."Calendar2003&quot.sheets
dim PageName as string
dim startx, starty as integer


y = 2
z = 0
For x = 2 To 9

myarray(x-2) = mycell(PageName).Cells(x + startx, y)

Next x

' the column to record is currently in row 8
x = 8
For y = 3 To 33
myarray(x-3) =mycell(PageName).Cells(x, y + starty)
Next y
Tranpkp
************************************
- Let me know if this helped/worked!