Re: Macro modification

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Ron de Bruin (rondebruin_at_kabelfoon.nl)
Date: 11/03/04 

Date: Wed, 3 Nov 2004 19:05:06 +0100

My Example is wrong ( it is working ) but I miss the typo in Tom's code

I have used Resize(4, 1)) to copy one more row
The macro delete this last row each time because of the typo.

Use Tom's changed example 

-- 
Regards Ron de Bruin
http://www.rondebruin.nl
"Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message news:%237PNN4cwEHA.3336@TK2MSFTNGP11.phx.gbl...
> This is working
>
> Set rng = Union(sh.Cells(i, 1), _
>  sh.Cells(i + 2, 1).Resize(4, 1))
>
> If i is 7 then this is the cell in row 7
> sh.Cells(i, 1),
>
> Then it use this line
> sh.Cells(i + 2, 1) to go to row 9 (i = 7 +2)
>
> Then Tom use resize to make the range 3 rows bigger
> Resize(4, 1) ( it will count the start row also, that's why the 4)
>
>
> -- 
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "HJ" <HJ@discussions.microsoft.com> wrote in message news:6368BCFC-3A47-47A5-B19F-60BAD38C7D70@microsoft.com...
>> Tom Ogilvy provided me with this great macro in order to copy certain rows
>> from a spreadsheet into a new master tab.  In summary, I am copying rows
>> 7,9,10,11 then skiping 16 rows and copying 23,25,26,27, skiping 16 rows etc
>> down the entire sheet.  For some reason, the macro is not copying row 11, 27,
>> etc. and I'm not sure how to modify to capture that row.  Can someone explain
>> to me the section of the macro sh.Cells(i + 2, 1).Resize(3,1))?  I presume
>> that is the line I would need to adjust to pick up that additional row.
>>
>> Sub CopyData()
>> Dim i As Long, rng As Range, sh As Worksheet
>> Worksheets.Add(After:=Worksheets( _
>>   Worksheets.Count)).Name = "Master"
>> Set sh = Worksheets("Input-Sales")
>> i = 7
>> Do While Not IsEmpty(sh.Cells(i, 1))
>> Set rng = Union(sh.Cells(i, 1), _
>>  sh.Cells(i + 2, 1).Resize(3, 1))
>> rng.EntireRow.Copy Destination:= _
>>   Worksheets("Master").Cells(Rows.Count, 1).End(xlUp)
>> i = i + 16
>> Loop
>> End Sub
>>
>> Thanks in advance for your help.  This is definitely a learning experience
>> for me!
>>
>> Regards,
>> HJ :)
>
>

Relevant Pages

  • Macro modification
    ... Tom Ogilvy provided me with this great macro in order to copy certain rows ... In summary, I am copying rows ... Sub CopyData() ... Set rng = Union, _ ...
    (microsoft.public.excel.programming)
  • Re: Macro modification
    ... Then Tom use resize to make the range 3 rows bigger ... In summary, I am copying rows ... For some reason, the macro is not copying row 11, 27,> etc. and I'm not sure how to modify to capture that row. ... > Sub CopyData() ...
    (microsoft.public.excel.programming)
  • Re: Macro to select files and then run different macro on selected file
    ... Application.Workbooks.Open pstrFilePath ... typo, ... Sub testing() ... Now everytime that macro is run, it will prompt you for a file to open, ...
    (microsoft.public.excel.programming)
  • Re: Calling a Excel function from VB
    ... The method that the typo was in was not even ... Does the problem occur in other workbook in the computer? ... Is it possible that the macro was mistakenly put into ... Is it possible that the macro CopyComps is defined for several times? ...
    (microsoft.public.vb.general.discussion)
  • Re: Dynamic Named Cell
    ... You would need a macro and more explanation to us. ... Your a1 a2 below don't seem to be consistent. ... Typo? ...
    (microsoft.public.excel.programming)