Copy cell data and assigning to variables
 

Excel 97 macro. I need to read cells on a worksheet and use that as an
address to paste data to. It sounds easy and probably is. But
everything I try is not working.

A B C
1 SheetNo RowNo ColumNo
2 sheet2 1134 3
3 sheet3 2278 3
4 sheet4 3389 3

on sheet1 read A2
read B2
read C2

It should copy cells A1:D100 of sheet2 and paste the date to sheet1
column 3
row 1134

copy cells A1:D100 of sheet3 and paste the date to sheet2
column 3
row 2278
etc.

I can copy cells, paste cells, change worksheets, and change
workbooks. Mostly thanks to help from these forums. I can't read three
cells and assign data to three variables (SheetNo, RowNo, ColumNo) and
make it work. Sigh.

Copy cell data and assigning to variables
 

On Oct 13, 3:02 pm, Fan924 wrote:
Excel 97 macro. I need to read cells on a worksheet and use that as an
address to paste data to. It sounds easy and probably is. But
everything I try is not working.

A B C
1 SheetNo RowNo ColumNo
2 sheet2 1134 3
3 sheet3 2278 3
4 sheet4 3389 3

on sheet1 read A2
read B2
read C2

It should copy cells A1:D100 of sheet2 and paste the date to sheet1
column 3
row 1134

copy cells A1:D100 of sheet3 and paste the date to sheet2
column 3
row 2278
etc.

I can copy cells, paste cells, change worksheets, and change
workbooks. Mostly thanks to help from these forums. I can't read three
cells and assign data to three variables (SheetNo, RowNo, ColumNo) and
make it work. Sigh.


I think this is what you want to do. Here's the basic approach:

Sub copystuff()


Dim r As Double, c As Double, sname As String

sname = Range("A2").Value 'pull in the sheet name
r = Range("B2").Value 'pull in the row number
c = Range("C2").Value 'pull in the column number

Worksheets(sname).Range("D1:D100").Copy 'copy range from the
right sheet
Cells(r, c).PasteSpecial 'paste it in sheet1 on
the r and c specified

End Sub


Here's a version that takes care of all of it using an array to hold
the locations you want.


Sub copystuff2()


Dim r As Double, c As Double, sname As String, x As Integer, _
xcount As Integer


xcount = Application.WorksheetFunction.CountA(Range("A:A"))
'how many, excluding header row

Dim arr As Variant, rng As String
ReDim arr(xcount - 1, 2)

rng = "A2:C" & xcount 'defines where the information
on the rows and columns resides (A2:C4)

arr = Range(rng) 'brings the information into
a 3x3 array

For x = 1 To xcount - 1

Worksheets(arr(x, 1)).Range("D1:D100").Copy 'copy range from
the right sheet
Cells(arr(x, 2), arr(x, 3)).PasteSpecial 'paste it in
sheet1 on the r and c specified

Next x


End Sub

Copy cell data and assigning to variables
 

Thanks chip, worked great. I did try something like r =
Range("B2").Value previously but it wouldn't read.

Can I use something else other than...
Cells(arr(x, 2), arr(x, 3)).PasteSpecial ???????????
There is data and a chart in the copy and paste and the chart gets
lost. I believe there are options to PasteSpecial but I don't have
the reference materials.