Excel macro code for copying data to a cell reference which is set byvalues in two separate cells in

[Frank Owen]

Hi,

I am looking for assistance in amending some VBA code in a macro which copies data from one worksheet to another worksheet within the same workbook.

At present the macro code (shown below) copies data to a cell identified asColumn “K” and a ROW reference which varies according the value of a cell within my worksheet (in my case named cell reference being “R_matchtask”). The “R_matchTASK” value is derived using a search using the MATCH function. The code works.

Is it possible to amend the code so that the COLUMN reference (“K” in the case below) is also set by the value in a cell, that value also being derived by use of a MATCH function. The amendment will allow me to insert columns without having to amend the “K” reference in my macro code.

Selected code extracts are as follows …………….

Sheets("R-Output").Select
Range("K" & Range("R_matchtask").Value).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks :=False, Transpose:=False

Any advice will be appreciated.

Frank

 

[GS]

Uh.., possibly!

Choose a cell to contain the column ref
Name it *C_matchtask* (possibly)

Change this code...

Sheets("R-Output").Select
Range("K" & Range("R_matchtask").Value).Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks :=False, Transpose:=False

TO...

With Sheets("R-Output")
..Range(Range("C_matchtask").Text & Range("R_matchtask").Value).Value _
= <sourcedata>.Value
End With

...where <sourcedata> needs to be replaced with the range you're
copying, but you can eliminate the copy process because the value is
now being directly assigned to the target range. If you don't get it..,
post all the relevant code and perhaps someone can modify it for you so
it's more efficient than what the macro recorder gave you!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Frank Owen]

Hi,



I am looking for assistance in amending some VBA code in a macro which copies data from one worksheet to another worksheet within the same workbook.



At present the macro code (shown below) copies data to a cell identified as Column “K” and a ROW reference which varies according the value of acell within my worksheet (in my case named cell reference being “R_matchtask”). The “R_matchTASK” value is derived using a search using the MATCH function. The code works.



Is it possible to amend the code so that the COLUMN reference (“K” inthe case below) is also set by the value in a cell, that value also being derived by use of a MATCH function. The amendment will allow me to insert columns without having to amend the “K” reference in my macro code.



Selected code extracts are as follows …………….



Sheets("R-Output").Select

Range("K" & Range("R_matchtask").Value).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks :=False, Transpose:=False



Any advice will be appreciated.



Frank

Thanks Garry, code updated and it operates exactly as I requested.

 

[GS]

Thanks Garry, code updated and it operates exactly as I requested

That's great! Glad to help, and thanks for the feedback!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion