Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assigning cell values to variables | Excel Discussion (Misc queries) | |||
Assigning Cell data to VB Variables | Excel Programming | |||
MS Office Excel 2003 assigning cell values to variables | Excel Programming | |||
Assigning multiple text strings to variables | Excel Programming | |||
Assigning cell address components to variables | Excel Programming |