Copy Range of Formulas to a Variable Range of Cells

**xace** · 09-02-2015, 04:19 AM

Hi everyone,

I have difficulties trying to writing some VBA codes. As what I have attached, I am trying to copy a range of formulas, and paste them into another range of cells.

For example, when excel detects column A with "Apple", I want to copy formulas from M2:Z2 and copy them to M24:M34. The thing is that the range of M24:M34 is more of variable and always changing, it could be M14:M64 the next day. How should I amend the codes to paste the formulas from M2:Z2 to the M columns between "Apple" and "Pear". Sorry if this is confusing.. but please let me know if there is any uncertainty. Thanks

Sub copyFormula()

myrow = 21
Lastrow = Range("A65000").End(xlUp).Row
For i = 21 To Lastrow
Range("A" & myrow).Select

If ActiveCell.Value = "Apple" Then

Range("M2:Z2").Select
Selection.Copy
Range("M24:M34").Select
ActiveSheet.Paste

End If
myrow = myrow + 1
Next I
End sub

**jaslake** · 09-02-2015, 11:15 AM

Hi xace

I can write Code that'll work on your Sample File but it'll be based on the Contents of Column A. I'm fairly confident that your Actual Data does not look like your Sample Data where Apple is in each Cell under Apple, Pear is in each Cell under Pear, etc.

What does your actual Data look like?

Row\Col	A
23	Apple
24	Apple1
25	Apple2
26	Apple3
27	Apple4
28	Apple5
29	Apple6
30	Apple7
31	Apple8
32	Apple9
33	Apple10
34	Apple11
35	Pear
36	Pear1
37	Pear2
38	Pear3
39	Pear4
40	Pear5
41	Pear6
42	Pear7
43	Pear8
44	Pear9
45	Pear10
46	Pear11
47	Pear12
48	Pear13
49	Pear14
50	Pear15
51	Pear16
52	Pear17
53	Pear18
54	Orange
55	Orange1
56	Orange2
57	Orange3
58	Orange4
59	Orange5
60	Orange6
61	Orange7
62	Orange8
63	Orange9

**xace** · 09-02-2015, 01:04 PM

Hi jaslake, appreciate the response.. Apple, pear and orange are just headers. Actual data are security types like bonds,c equities etc. So the cells under bonds would be the identifiers such as the ISIN or sedol.

I'm just wondering if it's possible to set the range to paste between the different headers.

**jaslake** · 09-02-2015, 01:13 PM

Hi xace

Yes, it's possible.

I'm just wondering if it's possible to set the range to paste between the different headers.

What are the ACTUAL Header Titles? Are they ALWAYS present in the Worksheet? Are they ALWAYS in the same sequence in the Worksheet?

**xace** · 09-02-2015, 09:29 PM

Hi jaslake,

The actual header titles are Bond, CFD,Equity, Fund certificate, Future, GDR/ADR, Option and so on.

The tricky part is that they are not always in the same sequence in the worksheet. The only constant is that it will always appear under column A, after line 20. For example, there are days where there might not be equities in the report, so it will just be Bond, CFD, Fund certificate and so on etc.

**xace** · 09-03-2015, 12:19 PM

Bump for help

**jaslake** · 09-03-2015, 02:53 PM

Hi xace

Nothing further comes to mind based on your Sample Data. I'd suggest you post a Sample File with your Actual Data with it's Actual Structure (desensitized) so we can see what we're dealing with.

**xace** · 09-03-2015, 09:53 PM

before.xlsm after.xlsm

Attached a before and after excel. Hopefully it gets clearer?

**jaslake** · 09-03-2015, 11:05 PM

Hi xace

I may be able to work with this. Just looking for ways...looking for "hooks".

A small hook:
Does Bond ALWAYS have "XX" as the leading two Characters?
Does Equity ALWAYS have "B" as the leading Character?
Does Fund Certificate ALWAYS have "XQ" as the leading two Characters?
Does Options ALWAYS have "FX" as the leading two Characters?

A bigger hook (might be a solution):
Is Column B for the Headers (Bond, Equity, Fund Certificate and Options) ALWAYS empty?

Perhaps another hook:
Are Headers (Bond, Equity, Fund Certificate and Options) ALWAYS Bold? Are any other Cells in Column A in Bold?

Most important...are these the ONLY possible Headers we have?

Bond
Equity
Fund Certificate
Options

**xace** · 09-04-2015, 12:35 AM

Hi jaslake, the bigger hook hits! Only column "A" of the header is filled. Column "B" onwards have nothing.

**jaslake** · 09-04-2015, 12:43 AM

Hi xace

It's a hook...I'll look at it tomorrow...after midnight here....

**jaslake** · 09-04-2015, 01:03 AM

Hi xace

Went to bed...had a thought.

Under each Header Row you have Data Rows. Is Column B of each of these Data Rows ALWAYS populated?

**Nitin Revankar** · 09-04-2015, 01:08 AM

Hi All,

I have a workbook that includes 1 Sheet which is named as TT & CT, used to view the time tracking information. (Excel file name is TT & CT Final)
I need the data of E9:K9 from the above sheet i.e "TT & CT" to A2:G2 to new excel workbook.
The cell that will be copied is fixed but the place where it will be pasted in new workbook will need to come by order wise. Below is the example how it should be pasted to new excel workbook.
A2:G2
A3:G3
A4:G4
A5:G5.

Please help me out to get macro code for the same. As i am not very much good in macros.. hoping t get as simple and easy way to get it done,

Thanks in Advance..!!

**xace** · 09-04-2015, 03:24 AM

thanks jaslake.

Under each Header Row you have Data Rows. Is Column B of each of these Data Rows ALWAYS populated? - yes

Just to inform you as well, there are actually more headers such as Right, Index bond, Covered warrant etc, but I believe these can be manually added easily into the coding template you'll be writing?

**jaslake** · 09-04-2015, 01:13 PM

Hi xace

This Code in the attached looks for Blank Cells in Column B and fills Columns M through Z with the Formulas in M2 to Z2. Looking at your after.xlsm Workbook, this is obviously NOT what you're looking for. But I have no idea what your looking for as you've not explained except for this from your Initial Post.

How should I amend the codes to paste the formulas from M2:Z2 to the M columns between "Apple" and "Pear"

So, explain how one determines which Formulas to paste where and when.

Please Login or Register  to view this content.

**AlphaFrog** · 09-04-2015, 02:11 PM

Building off of jaslake's good work, this uses the .SpecialCells(xlCellTypeConstants).Areas properties to define each section and copy the formulas en masse.

Edit the Select Case code block to define the range of formulas to copy for a given Header.

Please Login or Register  to view this content.

**xace** · 09-07-2015, 10:28 PM

Appreciate the help jaslake and AlphaFrog.

AlphaFrog's codes are good. Will be doing some testing on my end. Thanks for bearing with my lousy explanation

Copy Range of Formulas to a Variable Range of Cells

LinkBack

Thread Tools

Rate This Thread

Display

Copy Range of Formulas to a Variable Range of Cells

Re: Copy Range of Formulas to a Variable Range of Cells

Re: Copy Range of Formulas to a Variable Range of Cells

Re: Copy Range of Formulas to a Variable Range of Cells

Re: Copy Range of Formulas to a Variable Range of Cells

Re: Copy Range of Formulas to a Variable Range of Cells

Re: Copy Range of Formulas to a Variable Range of Cells

Re: Copy Range of Formulas to a Variable Range of Cells

Re: Copy Range of Formulas to a Variable Range of Cells

Re: Copy Range of Formulas to a Variable Range of Cells

Re: Copy Range of Formulas to a Variable Range of Cells

Re: Copy Range of Formulas to a Variable Range of Cells

Re: Copy Range of Formulas to a Variable Range of Cells

Re: Copy Range of Formulas to a Variable Range of Cells

Re: Copy Range of Formulas to a Variable Range of Cells

Thread Information

Users Browsing this Thread

Similar Threads

Copy range of cells, AND their formulas?

Copy only cells that contain values from a range cells that contain formulas to next row

Copy range of cells using variable criteria and named ranges

[SOLVED] Copy Variable to a Range of Cells

[SOLVED] Copy range from multiple sheets, into a master sheet and moving over by variable col range

[SOLVED] Copy/Paste Values from Variable Range to Corresponding Variable Range

Copy only Visible Cells of a Variable Range...

Bookmarks

Bookmarks

Posting Permissions