Re: $$Excel-Macros$$ Need help on keeping copy-paste simpler

[Isabel Cristina Ortiz]

Thank you DP!
Thank you very much for your help. It worked perfectly.

However, I tried to do the same in Calculation2 sheet, but inserting rows 
instead of columns and it did not work the same logic. It gives me a syntax 
error.
Could you help me fix this?

I used:
Dim v As Integer
v = Range("Months").Value - 1

If v > 1 Then
Range("RowLong").Resize(v,).Insert
Range("RowFormula").Copy
Range("RowLong").Offset(-v,).Resize(v,).PasteSpecial Paste:=xlFormulas
Application.CutCopyMode = False
End If

Regards,
Isabel



On Sunday, September 13, 2015 at 8:56:11 PM UTC-5, De Premor wrote:
>
> We can avoid using loop in this case, try to replace your looping code 
> with offset and resize, try this another solution
>
> Dim u As Integer
> u = Range("Months").Value - 1
>
> If u > 1 Then
> Range("ColumnLong").Resize(, u).Insert
> Range("ColumnFormula").Copy
> Range("ColumnLong").Offset(, -u).Resize(, u).PasteSpecial 
> Paste:=xlFormulas
> Application.CutCopyMode = False
> End If
>
> Rgds,
> [dp]
>
> On 14/09/2015 6:31, Isabel Cristina Ortiz wrote:
>
> Hello Ashish,
> Thank you for your response.
> This is a simplified copy of my macro. The lines you can find there are 
> repeated 
> multiple times in my final workbook, in different spreadsheets because it 
> has a broader mathematical formulation.
> Hope this helps,
> Regards
>
>
>
> On Saturday, September 12, 2015 at 2:19:22 PM UTC-5, ashish wrote: 
>>
>> Can u pls share a sample file
>> On Sep 12, 2015 9:38 PM, "Isabel Cristina Ortiz"  
>> wrote:
>>
>>> Thank you Basole for your message!
>>> I have been already using your recommendation on my code, but it still 
>>> takes a lot of memory.
>>>
>>> Regards,
>>> Isabel
>>>
>>>
>>>
>>> On Saturday, September 12, 2015 at 9:20:05 AM UTC-5, Basole wrote: 

 Hi isabel, try using "Application.ScreenUpdating = False" To Turn Off 
 at the start of code. and "Application.Calculation = xlCalculationManual" 
 To turn off the automatic calculation
 at the beginning of the code
 see example:

 Dim u As Integer, v As Integer
 u = Range("Months").Value - 1
 Application.ScreenUpdating = False 'To Turn Off at the start of code.
 Application.Calculation = xlCalculationManual  'To turn off the automatic 
 calculationIf u > 1 Then
 For v = 1 To u - 1
 Application.Goto Range("ColumnLong"), True
 Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 Application.Goto Range("ColumnFormulas"), True
 Selection.Copy
 Selection.Offset(0, -2).Select
 Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, 
 SkipBlanks:= _
 False, Transpose:=False
 Application.CutCopyMode = False
 Next v
 
 ElseEnd If
 Application.ScreenUpdating = True 'To Turn on at the end of the code.
 Application.Calculation = xlCalculationAutomatic 'To turn On the automatic 
 calculation

 Regards.


 2015-09-11 13:31 GMT-03:00 Isabel Cristina Ortiz :

> Hello, 
> I have been working on a workbook. It is a cash flow and macro helps 
> me to change the numbers of years (columns) because each project needs 
> different time periods.
> What I do is that I have two columns named as "ColumnLong" and 
> "ColumnFormulas". The first one is empty and only serves as a reference 
> point to add more cells to the left to it, according to the number of 
> months of the cash flow.
> Then, I always check the number of months on a specific cell named 
> "Months" and I have a loop, it insert a column left to "ColumnLong", then 
> goes to "ColumnFormulas" and it paste it into the new column which is two 
> columns left to "ColumnFormulas".
>
> What I need is a simpler code because I have several loops similar to 
> this one and I am having memory problems.
> I have been working on some recommendations I found on 
> http://www.excelitems.com/2010/12/optimize-vba-code-for-faster-macros.html
>  
> but wasn't able to apply to this code.
>
> I hope you can help me.
> Best regards,
> Isabel 
>
>
> This is the code I am using:
>
> Dim u As Integer, v As Integer
> u = Range("Months").Value - 1
>
> If u > 1 Then
> For v = 1 To u - 1
> Application.Goto Range("ColumnLong"), True
> Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
> Application.Goto Range("ColumnFormulas"), True
> Selection.Copy
> Selection.Offset(0,   - 2).Select
> Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, 
> SkipBlanks:= _
> False, Transpose:=False
> Application.CutCopyMode = False
> Next v
> 
> Else
> End If
>
>
>
>
>
> -- 
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in 

Re: $$Excel-Macros$$ Need help on keeping copy-paste simpler

[De Premor]

Hi Isabel,

I'am glad that its work, ok here is the problem in out 2nd case
1st, we need to step back to learn about how the function work, 
cpearson.com have a bunch article about this complete with explanation & 
sample, you can follow this link 
*http://www.cpearson.com/excel/optionalargumentstoprocedures.aspx*


After reading on that link, i'am sure you will know the different between :
* SomeFunction (**/Param1/**, **/Param2/**) **
** OtherFunction ( **/Param3/**,**/[Param4] /**) **
** AnotherFunc ( **/[Param5]/**,**/[Param6]/**) *

Parameter with the bracket "[ ... ]"  is an optional parameter, we can 
ignoring that.

Ex:
SomeFunction(5) <== This function will error, because the 2nd param is 
not an optional, we must provide a value in that like SomeFunction(5,4)
OtherFunction(100) <== this will fine, because the next parameter is 
optional
OtherFunction(100,) <==This will error, because vba will think you 
forgot to write the 2nd param, so it will warn you. In another word, 
"you must write something after that comma" :D

AnotherFunc(30) <== Ok
AnotherFunc(,30) <== Ok, why ? 1st param is optional, we can ignoring them.

Now, Please look at your formula, here is the right code
'No need that coma after "v"

If v > 1 Then
Range("RowLong").Resize(v).Insert
Range("RowFormula").Copy
Range("RowLong").Offset(-v).Resize(v).PasteSpecial Paste:=xlFormulas
Application.CutCopyMode = False
End If

:D
Hope it help, Rgds
[dp]

On 14/09/2015 23:12, Isabel Cristina Ortiz wrote:

Thank you DP!
Thank you very much for your help. It worked perfectly.

However, I tried to do the same in Calculation2 sheet, but inserting 
rows instead of columns and it did not work the same logic. It gives 
me a syntax error.

Could you help me fix this?

I used:
Dim v As Integer
v = Range("Months").Value - 1

If v > 1 Then
Range("RowLong").Resize(v,).Insert
Range("RowFormula").Copy
Range("RowLong").Offset(-v,).Resize(v,).PasteSpecial Paste:=xlFormulas
Application.CutCopyMode = False
End If

Regards,
Isabel



On Sunday, September 13, 2015 at 8:56:11 PM UTC-5, De Premor wrote:

We can avoid using loop in this case, try to replace your looping
code with offset and resize, try this another solution

Dim u As Integer
u = Range("Months").Value - 1

If u > 1 Then
Range("ColumnLong").Resize(, u).Insert
Range("ColumnFormula").Copy
Range("ColumnLong").Offset(, -u).Resize(, u).PasteSpecial
Paste:=xlFormulas
Application.CutCopyMode = False
End If

Rgds,
[dp]

On 14/09/2015 6:31, Isabel Cristina Ortiz wrote:

Hello Ashish,
Thank you for your response.
This is a simplified copy of my macro. The lines you can find
there arerepeated multiple times in my final workbook, in
different spreadsheets because it has a broader mathematical
formulation.
Hope this helps,
Regards



On Saturday, September 12, 2015 at 2:19:22 PM UTC-5, ashish wrote:

Can u pls share a sample file

On Sep 12, 2015 9:38 PM, "Isabel Cristina Ortiz"
 wrote:

Thank you Basole for your message!
I have been already using your recommendation on my code,
but it still takes a lot of memory.

Regards,
Isabel



On Saturday, September 12, 2015 at 9:20:05 AM UTC-5,
Basole wrote:

Hi isabel, try using "Application.ScreenUpdating =
False" To Turn Off at the start of code. and
"Application.Calculation = xlCalculationManual" To
turn off the automatic calculation
at the beginning of the code
see example:

Dim  uAs  Integer, vAs  Integer
u = Range("Months").Value - 1
Application.ScreenUpdating =False  'To Turn Off at the start of 
code.
Application.Calculation = xlCalculationManual'To turn off the 
automatic calculation
If  u > 1Then
 For  v = 1To  u - 1
 Application.Goto  Range("ColumnLong"),True
 Selection.Insert Shift:=xlDown, 
CopyOrigin:=xlFormatFromLeftOrAbove
 Application.Goto  Range("ColumnFormulas"),True
 Selection.Copy
 Selection.Offset(0, -2).Select
 Selection.PasteSpecial Paste:=xlFormulas, 
Operation:=xlNone, SkipBlanks:=_
 False, Transpose:=False
 Application.CutCopyMode =False
 Next  v
 
 Else

End  If
Application.ScreenUpdating =True  'To Turn on at the end of the 
code.
Application.Calculation = xlCalculationAutomatic'To turn On the 
automatic calculation

Regards.


2015-09-11 13:31 GMT-03:00 Isabel Cristina Ortiz
   

Re: $$Excel-Macros$$ Need help on keeping copy-paste simpler

[Isabel Cristina Ortiz]

Thank you DP
Your explanation was extraordinary. In addition to solve my problem, you taught 
me a lot! ;)

Thank you very much again
Regards
Isabel



On Monday, September 14, 2015 at 7:37:57 PM UTC-5, De Premor wrote:
>
> Hi Isabel,
>
> I'am glad that its work, ok here is the problem in out 2nd case
> 1st, we need to step back to learn about how the function work, 
> cpearson.com have a bunch article about this complete with explanation & 
> sample, you can follow this link 
> *http://www.cpearson.com/excel/optionalargumentstoprocedures.aspx 
> *
>
> After reading on that link, i'am sure you will know the different between :
> * SomeFunction (** Param1**, **Param2** ) *
> * OtherFunction ( **Param3**,** [Param4] **) *
> * AnotherFunc ( **[Param5]**,** [Param6]** ) *
>
> Parameter with the bracket "[ ... ]"  is an optional parameter, we can 
> ignoring that.
> Ex:
> SomeFunction(5) <== This function will error, because the 2nd param is not 
> an optional, we must provide a value in that like SomeFunction(5,4) 
> OtherFunction(100) <== this will fine, because the next parameter is 
> optional
> OtherFunction(100,) <==This will error, because vba will think you forgot 
> to write the 2nd param, so it will warn you. In another word, "you must 
> write something after that comma" :D
> AnotherFunc(30) <== Ok
> AnotherFunc(,30) <== Ok, why ? 1st param is optional, we can ignoring them.
>
> Now, Please look at your formula, here is the right code
> 'No need that coma after "v"
>
> If v > 1 Then
> Range("RowLong").Resize(v).Insert 
> Range("RowFormula").Copy
> Range("RowLong").Offset(-v).Resize(v).PasteSpecial Paste:=xlFormulas
> Application.CutCopyMode = False
> End If
>
> :D
> Hope it help, Rgds
> [dp]
>
> On 14/09/2015 23:12, Isabel Cristina Ortiz wrote:
>
> Thank you DP!
> Thank you very much for your help. It worked perfectly.
>
> However, I tried to do the same in Calculation2 sheet, but inserting rows 
> instead of columns and it did not work the same logic. It gives me a 
> syntax error.
> Could you help me fix this?
>
> I used:
> Dim v As Integer
> v = Range("Months").Value - 1
>
> If v > 1 Then
> Range("RowLong").Resize(v,).Insert
> Range("RowFormula").Copy
> Range("RowLong").Offset(-v,).Resize(v,).PasteSpecial Paste:=xlFormulas
> Application.CutCopyMode = False
> End If
>
> Regards,
> Isabel
>
>
>
> On Sunday, September 13, 2015 at 8:56:11 PM UTC-5, De Premor wrote: 
>>
>> We can avoid using loop in this case, try to replace your looping code 
>> with offset and resize, try this another solution
>>
>> Dim u As Integer
>> u = Range("Months").Value - 1
>>
>> If u > 1 Then
>> Range("ColumnLong").Resize(, u).Insert
>> Range("ColumnFormula").Copy
>> Range("ColumnLong").Offset(, -u).Resize(, u).PasteSpecial 
>> Paste:=xlFormulas
>> Application.CutCopyMode = False
>> End If
>>
>> Rgds,
>> [dp]
>>
>> On 14/09/2015 6:31, Isabel Cristina Ortiz wrote:
>>
>> Hello Ashish,
>> Thank you for your response.
>> This is a simplified copy of my macro. The lines you can find there are 
>> repeated 
>> multiple times in my final workbook, in different spreadsheets because 
>> it has a broader mathematical formulation.
>> Hope this helps,
>> Regards
>>
>>
>>
>> On Saturday, September 12, 2015 at 2:19:22 PM UTC-5, ashish wrote: 
>>>
>>> Can u pls share a sample file
>>> On Sep 12, 2015 9:38 PM, "Isabel Cristina Ortiz"  
>>> wrote:
>>>
 Thank you Basole for your message!
 I have been already using your recommendation on my code, but it still 
 takes a lot of memory.

 Regards,
 Isabel



 On Saturday, September 12, 2015 at 9:20:05 AM UTC-5, Basole wrote: 
>
> Hi isabel, try using "Application.ScreenUpdating = False" To Turn Off 
> at the start of code. and "Application.Calculation = xlCalculationManual" 
> To turn off the automatic calculation
> at the beginning of the code
> see example:
>
> Dim u As Integer, v As Integer
> u = Range("Months").Value - 1
> Application.ScreenUpdating = False 'To Turn Off at the start of code.
> Application.Calculation = xlCalculationManual  'To turn off the automatic 
> calculationIf u > 1 Then
> For v = 1 To u - 1
> Application.Goto Range("ColumnLong"), True
> Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
> Application.Goto Range("ColumnFormulas"), True
> Selection.Copy
> Selection.Offset(0, -2).Select
> Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, 
> SkipBlanks:= _
> False, Transpose:=False
> Application.CutCopyMode = False
> Next v
> 
> ElseEnd If
> Application.ScreenUpdating = True 'To Turn on at the end of the code.
> Application.Calculation = xlCalculationAutomatic 'To turn On the 
> automatic calculation
>

Re: $$Excel-Macros$$ Need help on keeping copy-paste simpler

[Isabel Cristina Ortiz]

Hello Ashish,
Thank you for your response.
This is a simplified copy of my macro. The lines you can find there are 
repeated 
multiple times in my final workbook, in different spreadsheets because it 
has a broader mathematical formulation.
Hope this helps,
Regards



On Saturday, September 12, 2015 at 2:19:22 PM UTC-5, ashish wrote:
>
> Can u pls share a sample file
> On Sep 12, 2015 9:38 PM, "Isabel Cristina Ortiz"  > wrote:
>
>> Thank you Basole for your message!
>> I have been already using your recommendation on my code, but it still 
>> takes a lot of memory.
>>
>> Regards,
>> Isabel
>>
>>
>>
>> On Saturday, September 12, 2015 at 9:20:05 AM UTC-5, Basole wrote:
>>>
>>> Hi isabel, try using "Application.ScreenUpdating = False" To Turn Off at 
>>> the start of code. and "Application.Calculation = xlCalculationManual" To 
>>> turn off the automatic calculation
>>> at the beginning of the code
>>> see example:
>>>
>>> Dim u As Integer, v As Integer
>>> u = Range("Months").Value - 1
>>> Application.ScreenUpdating = False 'To Turn Off at the start of code.
>>> Application.Calculation = xlCalculationManual  'To turn off the automatic 
>>> calculationIf u > 1 Then
>>> For v = 1 To u - 1
>>> Application.Goto Range("ColumnLong"), True
>>> Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
>>> Application.Goto Range("ColumnFormulas"), True
>>> Selection.Copy
>>> Selection.Offset(0, -2).Select
>>> Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, 
>>> SkipBlanks:= _
>>> False, Transpose:=False
>>> Application.CutCopyMode = False
>>> Next v
>>> 
>>> ElseEnd If
>>> Application.ScreenUpdating = True 'To Turn on at the end of the code.
>>> Application.Calculation = xlCalculationAutomatic 'To turn On the automatic 
>>> calculation
>>>
>>>
>>> Regards.
>>>
>>>
>>>
>>> 2015-09-11 13:31 GMT-03:00 Isabel Cristina Ortiz :
>>>
 Hello,
 I have been working on a workbook. It is a cash flow and macro helps me 
 to change the numbers of years (columns) because each project needs 
 different time periods.
 What I do is that I have two columns named as "ColumnLong" and 
 "ColumnFormulas". The first one is empty and only serves as a reference 
 point to add more cells to the left to it, according to the number of 
 months of the cash flow.
 Then, I always check the number of months on a specific cell named 
 "Months" and I have a loop, it insert a column left to "ColumnLong", then 
 goes to "ColumnFormulas" and it paste it into the new column which is two 
 columns left to "ColumnFormulas".

 What I need is a simpler code because I have several loops similar to 
 this one and I am having memory problems.
 I have been working on some recommendations I found on 
 http://www.excelitems.com/2010/12/optimize-vba-code-for-faster-macros.html 
 but wasn't able to apply to this code.

 I hope you can help me.
 Best regards,
 Isabel 


 This is the code I am using:

 Dim u As Integer, v As Integer
 u = Range("Months").Value - 1

 If u > 1 Then
 For v = 1 To u - 1
 Application.Goto Range("ColumnLong"), True
 Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 Application.Goto Range("ColumnFormulas"), True
 Selection.Copy
 Selection.Offset(0,   - 2).Select
 Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, 
 SkipBlanks:= _
 False, Transpose:=False
 Application.CutCopyMode = False
 Next v
 
 Else
 End If





 -- 
 Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? 
 It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
 https://www.facebook.com/discussexcel
  
 FORUM RULES
  
 1) Use concise, accurate thread titles. Poor thread titles, like Please 
 Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice 
 will not get quick attention or may not be answered.
 2) Don't post a question in the thread of another member.
 3) Don't post questions regarding breaking or bypassing any security 
 measure.
 4) Acknowledge the responses you receive, good or bad.
 5) Jobs posting is not allowed.
 6) Sharing copyrighted material and their links is not allowed.
  
 NOTE : Don't ever post confidential data in a workbook. Forum owners 
 and members are not responsible for any loss.
 --- 
 You received this message because you are subscribed to the Google 
 Groups "MS EXCEL AND VBA MACROS" group.
 To unsubscribe from this group and stop receiving emails from it, send 
 an email to excel-macros...@googlegroups.com.
 To post to this group, send email to excel-...@googlegroups.com.
 Visit this group at 

Re: $$Excel-Macros$$ Need help on keeping copy-paste simpler

[De Premor]

We can avoid using loop in this case, try to replace your looping code 
with offset and resize, try this another solution


Dim u As Integer
u = Range("Months").Value - 1

If u > 1 Then
Range("ColumnLong").Resize(, u).Insert
Range("ColumnFormula").Copy
Range("ColumnLong").Offset(, -u).Resize(, u).PasteSpecial 
Paste:=xlFormulas

Application.CutCopyMode = False
End If

Rgds,
[dp]

On 14/09/2015 6:31, Isabel Cristina Ortiz wrote:

Hello Ashish,
Thank you for your response.
This is a simplified copy of my macro. The lines you can find there 
arerepeated multiple times in my final workbook, in different 
spreadsheets because it has a broader mathematical formulation.

Hope this helps,
Regards



On Saturday, September 12, 2015 at 2:19:22 PM UTC-5, ashish wrote:

Can u pls share a sample file

On Sep 12, 2015 9:38 PM, "Isabel Cristina Ortiz"  wrote:

Thank you Basole for your message!
I have been already using your recommendation on my code, but
it still takes a lot of memory.

Regards,
Isabel



On Saturday, September 12, 2015 at 9:20:05 AM UTC-5, Basole
wrote:

Hi isabel, try using "Application.ScreenUpdating = False"
To Turn Off at the start of code. and
"Application.Calculation = xlCalculationManual" To turn
off the automatic calculation
at the beginning of the code
see example:

Dim  uAs  Integer, vAs  Integer
u = Range("Months").Value - 1
Application.ScreenUpdating =False  'To Turn Off at the start of 
code.
Application.Calculation = xlCalculationManual'To turn off the 
automatic calculation
If  u > 1Then
 For  v = 1To  u - 1
 Application.Goto  Range("ColumnLong"),True
 Selection.Insert Shift:=xlDown, 
CopyOrigin:=xlFormatFromLeftOrAbove
 Application.Goto  Range("ColumnFormulas"),True
 Selection.Copy
 Selection.Offset(0, -2).Select
 Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, 
SkipBlanks:=_
 False, Transpose:=False
 Application.CutCopyMode =False
 Next  v
 
 Else

End  If
Application.ScreenUpdating =True  'To Turn on at the end of the 
code.
Application.Calculation = xlCalculationAutomatic'To turn On the 
automatic calculation

Regards.


2015-09-11 13:31 GMT-03:00 Isabel Cristina Ortiz
:

Hello,
I have been working on a workbook. It is a cash flow
and macro helps me to change the numbers of years
(columns) because each project needs different time
periods.
What I do is that I have two columns named as
"ColumnLong" and "ColumnFormulas". The first one is
empty and only serves as a reference point to add more
cells to the left to it, according to the number of
months of the cash flow.
Then, I always check the number of months on a
specific cell named "Months" and I have a loop, it
insert a column left to "ColumnLong", then goes to
"ColumnFormulas" and it paste it into the new column
which is two columns left to "ColumnFormulas".

What I need is a simpler code because I have several
loops similar to this one and I am having memory problems.
I have been working on some recommendations I found on

http://www.excelitems.com/2010/12/optimize-vba-code-for-faster-macros.html


but wasn't able to apply to this code.

I hope you can help me.
Best regards,
Isabel


This is the code I am using:

Dim u As Integer, v As Integer
u = Range("Months").Value - 1

If u > 1 Then
For v = 1 To u - 1
Application.Goto Range("ColumnLong"), True
Selection.Insert Shift:=xlDown,
CopyOrigin:=xlFormatFromLeftOrAbove
Application.Goto Range("ColumnFormulas"), True
Selection.Copy
Selection.Offset(0,   - 2).Select
Selection.PasteSpecial Paste:=xlFormulas,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Next v
Else
End If





-- 
Are you =EXP(E:RT) or 

Re: $$Excel-Macros$$ Need help on keeping copy-paste simpler

[ashish koul]

Can u pls share a sample file
On Sep 12, 2015 9:38 PM, "Isabel Cristina Ortiz"  wrote:

> Thank you Basole for your message!
> I have been already using your recommendation on my code, but it still
> takes a lot of memory.
>
> Regards,
> Isabel
>
>
>
> On Saturday, September 12, 2015 at 9:20:05 AM UTC-5, Basole wrote:
>>
>> Hi isabel, try using "Application.ScreenUpdating = False" To Turn Off at
>> the start of code. and "Application.Calculation = xlCalculationManual" To
>> turn off the automatic calculation
>> at the beginning of the code
>> see example:
>>
>> Dim u As Integer, v As Integer
>> u = Range("Months").Value - 1
>> Application.ScreenUpdating = False 'To Turn Off at the start of code.
>> Application.Calculation = xlCalculationManual  'To turn off the automatic 
>> calculationIf u > 1 Then
>> For v = 1 To u - 1
>> Application.Goto Range("ColumnLong"), True
>> Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
>> Application.Goto Range("ColumnFormulas"), True
>> Selection.Copy
>> Selection.Offset(0, -2).Select
>> Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, 
>> SkipBlanks:= _
>> False, Transpose:=False
>> Application.CutCopyMode = False
>> Next v
>>
>> ElseEnd If
>> Application.ScreenUpdating = True 'To Turn on at the end of the code.
>> Application.Calculation = xlCalculationAutomatic 'To turn On the automatic 
>> calculation
>>
>>
>> Regards.
>>
>>
>>
>> 2015-09-11 13:31 GMT-03:00 Isabel Cristina Ortiz :
>>
>>> Hello,
>>> I have been working on a workbook. It is a cash flow and macro helps me
>>> to change the numbers of years (columns) because each project needs
>>> different time periods.
>>> What I do is that I have two columns named as "ColumnLong" and
>>> "ColumnFormulas". The first one is empty and only serves as a reference
>>> point to add more cells to the left to it, according to the number of
>>> months of the cash flow.
>>> Then, I always check the number of months on a specific cell named
>>> "Months" and I have a loop, it insert a column left to "ColumnLong", then
>>> goes to "ColumnFormulas" and it paste it into the new column which is two
>>> columns left to "ColumnFormulas".
>>>
>>> What I need is a simpler code because I have several loops similar to
>>> this one and I am having memory problems.
>>> I have been working on some recommendations I found on
>>> http://www.excelitems.com/2010/12/optimize-vba-code-for-faster-macros.html
>>> but wasn't able to apply to this code.
>>>
>>> I hope you can help me.
>>> Best regards,
>>> Isabel
>>>
>>>
>>> This is the code I am using:
>>>
>>> Dim u As Integer, v As Integer
>>> u = Range("Months").Value - 1
>>>
>>> If u > 1 Then
>>> For v = 1 To u - 1
>>> Application.Goto Range("ColumnLong"), True
>>> Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
>>> Application.Goto Range("ColumnFormulas"), True
>>> Selection.Copy
>>> Selection.Offset(0,   - 2).Select
>>> Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
>>> SkipBlanks:= _
>>> False, Transpose:=False
>>> Application.CutCopyMode = False
>>> Next v
>>>
>>> Else
>>> End If
>>>
>>>
>>>
>>>
>>>
>>> --
>>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be?
>>> It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>>> https://www.facebook.com/discussexcel
>>>
>>> FORUM RULES
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>> 2) Don't post a question in the thread of another member.
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>> 4) Acknowledge the responses you receive, good or bad.
>>> 5) Jobs posting is not allowed.
>>> 6) Sharing copyrighted material and their links is not allowed.
>>>
>>> NOTE : Don't ever post confidential data in a workbook. Forum owners and
>>> members are not responsible for any loss.
>>> ---
>>> You received this message because you are subscribed to the Google
>>> Groups "MS EXCEL AND VBA MACROS" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to excel-macros...@googlegroups.com.
>>> To post to this group, send email to excel-...@googlegroups.com.
>>> Visit this group at http://groups.google.com/group/excel-macros.
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>
>> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 

Re: $$Excel-Macros$$ Need help on keeping copy-paste simpler

[Isabel Cristina Ortiz]

Thank you Basole for your message!
I have been already using your recommendation on my code, but it still 
takes a lot of memory.

Regards,
Isabel



On Saturday, September 12, 2015 at 9:20:05 AM UTC-5, Basole wrote:
>
> Hi isabel, try using "Application.ScreenUpdating = False" To Turn Off at 
> the start of code. and "Application.Calculation = xlCalculationManual" To 
> turn off the automatic calculation
> at the beginning of the code
> see example:
>
> Dim u As Integer, v As Integer
> u = Range("Months").Value - 1
> Application.ScreenUpdating = False 'To Turn Off at the start of code.
> Application.Calculation = xlCalculationManual  'To turn off the automatic 
> calculationIf u > 1 Then
> For v = 1 To u - 1
> Application.Goto Range("ColumnLong"), True
> Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
> Application.Goto Range("ColumnFormulas"), True
> Selection.Copy
> Selection.Offset(0, -2).Select
> Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= 
> _
> False, Transpose:=False
> Application.CutCopyMode = False
> Next v
> 
> ElseEnd If
> Application.ScreenUpdating = True 'To Turn on at the end of the code.
> Application.Calculation = xlCalculationAutomatic 'To turn On the automatic 
> calculation
>
>
> Regards.
>
>
>
> 2015-09-11 13:31 GMT-03:00 Isabel Cristina Ortiz  >:
>
>> Hello,
>> I have been working on a workbook. It is a cash flow and macro helps me 
>> to change the numbers of years (columns) because each project needs 
>> different time periods.
>> What I do is that I have two columns named as "ColumnLong" and 
>> "ColumnFormulas". The first one is empty and only serves as a reference 
>> point to add more cells to the left to it, according to the number of 
>> months of the cash flow.
>> Then, I always check the number of months on a specific cell named 
>> "Months" and I have a loop, it insert a column left to "ColumnLong", then 
>> goes to "ColumnFormulas" and it paste it into the new column which is two 
>> columns left to "ColumnFormulas".
>>
>> What I need is a simpler code because I have several loops similar to 
>> this one and I am having memory problems.
>> I have been working on some recommendations I found on 
>> http://www.excelitems.com/2010/12/optimize-vba-code-for-faster-macros.html 
>> but wasn't able to apply to this code.
>>
>> I hope you can help me.
>> Best regards,
>> Isabel 
>>
>>
>> This is the code I am using:
>>
>> Dim u As Integer, v As Integer
>> u = Range("Months").Value - 1
>>
>> If u > 1 Then
>> For v = 1 To u - 1
>> Application.Goto Range("ColumnLong"), True
>> Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
>> Application.Goto Range("ColumnFormulas"), True
>> Selection.Copy
>> Selection.Offset(0,   - 2).Select
>> Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, 
>> SkipBlanks:= _
>> False, Transpose:=False
>> Application.CutCopyMode = False
>> Next v
>> 
>> Else
>> End If
>>
>>
>>
>>
>>
>> -- 
>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
>> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
>> https://www.facebook.com/discussexcel
>>  
>> FORUM RULES
>>  
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please 
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice 
>> will not get quick attention or may not be answered.
>> 2) Don't post a question in the thread of another member.
>> 3) Don't post questions regarding breaking or bypassing any security 
>> measure.
>> 4) Acknowledge the responses you receive, good or bad.
>> 5) Jobs posting is not allowed.
>> 6) Sharing copyrighted material and their links is not allowed.
>>  
>> NOTE : Don't ever post confidential data in a workbook. Forum owners and 
>> members are not responsible for any loss.
>> --- 
>> You received this message because you are subscribed to the Google Groups 
>> "MS EXCEL AND VBA MACROS" group.
>> To unsubscribe from this group and stop receiving emails from it, send an 
>> email to excel-macros...@googlegroups.com .
>> To post to this group, send email to excel-...@googlegroups.com 
>> .
>> Visit this group at http://groups.google.com/group/excel-macros.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) 

Re: $$Excel-Macros$$ Need help on keeping copy-paste simpler

[Ricardo®]

Hi isabel, try using "Application.ScreenUpdating = False" To Turn Off at
the start of code. and "Application.Calculation = xlCalculationManual" To
turn off the automatic calculation
at the beginning of the code
see example:

Dim u As Integer, v As Integer
u = Range("Months").Value - 1
Application.ScreenUpdating = False 'To Turn Off at the start of code.
Application.Calculation = xlCalculationManual  'To turn off the
automatic calculationIf u > 1 Then
For v = 1 To u - 1
Application.Goto Range("ColumnLong"), True
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Application.Goto Range("ColumnFormulas"), True
Selection.Copy
Selection.Offset(0, -2).Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Next v

ElseEnd If
Application.ScreenUpdating = True 'To Turn on at the end of the code.
Application.Calculation = xlCalculationAutomatic 'To turn On the
automatic calculation


Regards.



2015-09-11 13:31 GMT-03:00 Isabel Cristina Ortiz :

> Hello,
> I have been working on a workbook. It is a cash flow and macro helps me to
> change the numbers of years (columns) because each project needs different
> time periods.
> What I do is that I have two columns named as "ColumnLong" and
> "ColumnFormulas". The first one is empty and only serves as a reference
> point to add more cells to the left to it, according to the number of
> months of the cash flow.
> Then, I always check the number of months on a specific cell named
> "Months" and I have a loop, it insert a column left to "ColumnLong", then
> goes to "ColumnFormulas" and it paste it into the new column which is two
> columns left to "ColumnFormulas".
>
> What I need is a simpler code because I have several loops similar to this
> one and I am having memory problems.
> I have been working on some recommendations I found on
> http://www.excelitems.com/2010/12/optimize-vba-code-for-faster-macros.html
> but wasn't able to apply to this code.
>
> I hope you can help me.
> Best regards,
> Isabel
>
>
> This is the code I am using:
>
> Dim u As Integer, v As Integer
> u = Range("Months").Value - 1
>
> If u > 1 Then
> For v = 1 To u - 1
> Application.Goto Range("ColumnLong"), True
> Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
> Application.Goto Range("ColumnFormulas"), True
> Selection.Copy
> Selection.Offset(0,   - 2).Select
> Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
> SkipBlanks:= _
> False, Transpose:=False
> Application.CutCopyMode = False
> Next v
>
> Else
> End If
>
>
>
>
>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 

$$Excel-Macros$$ Need help on keeping copy-paste simpler

[Isabel Cristina Ortiz]

Hello,
I have been working on a workbook. It is a cash flow and macro helps me to 
change the numbers of years (columns) because each project needs different 
time periods.
What I do is that I have two columns named as "ColumnLong" and 
"ColumnFormulas". The first one is empty and only serves as a reference 
point to add more cells to the left to it, according to the number of 
months of the cash flow.
Then, I always check the number of months on a specific cell named "Months" 
and I have a loop, it insert a column left to "ColumnLong", then goes to 
"ColumnFormulas" and it paste it into the new column which is two columns 
left to "ColumnFormulas".

What I need is a simpler code because I have several loops similar to this 
one and I am having memory problems.
I have been working on some recommendations I found on 
http://www.excelitems.com/2010/12/optimize-vba-code-for-faster-macros.html 
but wasn't able to apply to this code.

I hope you can help me.
Best regards,
Isabel 


This is the code I am using:

Dim u As Integer, v As Integer
u = Range("Months").Value - 1

If u > 1 Then
For v = 1 To u - 1
Application.Goto Range("ColumnLong"), True
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Application.Goto Range("ColumnFormulas"), True
Selection.Copy
Selection.Offset(0,   - 2).Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, 
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Next v

Else
End If





-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.