Hi, I need to automate copying cells from one worksheet into another
using macros but cannot figure how this will work. I have both text
and numeric fields starting from cell A1 to CE200. I need to ensure
that whenever I add a new row or column, these are also replicated to
the second worksheet, keeping all the numbers in the correct columns.

Please could I get some help. I need to get this done ASAP.

Many thanks,

ExcelVBA Beginner

In really simple terms you could do something like this;

Copy this code;

Sub copycells()
ActiveWorkbook.Sheets("sheet1").Select
ActiveSheet.Range("a1:ce200").Select
With Selection
..Copy
End With
ActiveWorkbook.Sheets("sheet2").Select
ActiveSheet.Range("a1").Select
With Selection
..PasteSpecial (xlValues)
End With
End Sub

When you run it it selects range a1:ce200 on sheet1 and copies it to
sheet2. You could run this via a button on your worksheet.

If your range is dynamic instead of naming the range a1:ce200 you
could use;

activesheet.range("a1").currentregion.select

Like I say this is very simplistic but should give you a start

On Apr 22, 12:06*pm, anon <inboxalwaysf...@hotmail.com> wrote:
> In really simple terms you could do something like this;
>
> Copy this code;
>
> Sub copycells()
> ActiveWorkbook.Sheets("sheet1").Select
> ActiveSheet.Range("a1:ce200").Select
> With Selection
> .Copy
> End With
> ActiveWorkbook.Sheets("sheet2").Select
> ActiveSheet.Range("a1").Select
> With Selection
> .PasteSpecial (xlValues)
> End With
> End Sub
>
> When you run it it selects range a1:ce200 on sheet1 and copies it to
> sheet2. You could run this via a button on your worksheet.
>
> If your range is dynamic instead of naming the range a1:ce200 you
> could use;
>
> activesheet.range("a1").currentregion.select
>
> Like I say this is very simplistic but should give you a start

this is great, thanks. Could you also let me know how I can modify the
code so that the formatting from the originalw orksheet is also copied
to the second worksheet?

The part that simply copies the values is the line;

..PasteSpecial (xlValues)

If you add in this line;

..PasteSpecial (xlFormats)

underneath it will also copy the formatting. So this part of the code
would be;

With Selection
..PasteSpecial (xlValues)
..PasteSpecial (xlFormats)
End With
End Sub

On Apr 22, 12:52*pm, anon <inboxalwaysf...@hotmail.com> wrote:
> The part that simply copies the values is the line;
>
> .PasteSpecial (xlValues)
>
> If you add in this line;
>
> .PasteSpecial (xlFormats)
>
> underneath it will also copy the formatting. So this part of the code
> would be;
>
> With Selection
> .PasteSpecial (xlValues)
> .PasteSpecial (xlFormats)
> End With
> End Sub

Wow! That's just brilliant, thanks! This actually brings me onto
another question. I'm not sure if this is possible with VBA though.
Does VBA allow for real-time updating? i.e. making a change to cell A1
in spreadhseet 1 will automatically update the cell A1 in spreadsheet
2. If so, could you provide a code example of this case?

On Apr 22, 1:48*pm, nadia.you...@googlemail.com wrote:
> On Apr 22, 12:52*pm, anon <inboxalwaysf...@hotmail.com> wrote:
>
>
>
>
>
> > The part that simply copies the values is the line;
>
> > .PasteSpecial (xlValues)
>
> > If you add in this line;
>
> > .PasteSpecial (xlFormats)
>
> > underneath it will also copy the formatting. So this part of the code
> > would be;
>
> > With Selection
> > .PasteSpecial (xlValues)
> > .PasteSpecial (xlFormats)
> > End With
> > End Sub
>
> Wow! That's just brilliant, thanks! This actually brings me onto
> another question. I'm not sure if this is possible with VBA though.
> Does VBA allow for real-time updating? i.e. making a change to cell A1
> in spreadhseet 1 will automatically update the cell A1 in spreadsheet
> 2. If so, could you provide a code example of this case?- Hide quoted text-
>
> - Show quoted text -

Also, each of the worksheets I use have their own names which VBA does
not like:

Sub copycells()
see here--------> ActiveWorkbook.Sheets("FY09").Select 'source sheet
ActiveSheet.Range("a5:n139").Select 'cell
ranges
With Selection
.copy
End With
see here--------> ActiveWorkbook.Sheets("8 Week").Select 'output
sheet
ActiveSheet.Range("i4:l139").Select 'cell
where the output should begin
With Selection
.PasteSpecial (xlValues) 'copies the values
.PasteSpecial (xlFormats) 'copies the
formatting
End With
End Sub

How can I make VBA accpet the customised worksheet names?

Second question first....

I can't see any reason why the code would error on that line to select
the worksheet, and i've copied the code and tested with no error. Are
the worksheets hidden? Is the right workbook active? (Try
myworkbook.sheets("FY09").activate ).These are the only reasons I can
see why this wouldn't work.

For your first question you need to search the forum for
worksheet_change. I've not used it howver believe this is what you're
looking for.

On Apr 22, 5:10*pm, anon <inboxalwaysf...@hotmail.com> wrote:
> Second question first....
>
> I can't see any reason why the code would error on that line to select
> the worksheet, and i've copied the code and tested with no error. Are
> the worksheets hidden? Is the right workbook active? (Try
> myworkbook.sheets("FY09").activate ).These are the only reasons I can
> see why this wouldn't work.
>
> For your first question you need to search the forum for
> worksheet_change. I've not used it howver believe this is what you're
> looking for.

ah yes, a silly mistake on my part - the code works

Thanks, I'll look up worksheet_change and hopefully work it out from
there.