Copying cells from one worksheet to another, within the same workbook

[nadia.younus]

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

 

[anon]

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

 

[nadia.younus]

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?

 

[anon]

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

 

[nadia.younus]

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?

 

[nadia.younus]

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?

 

[anon]

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.

 

[nadia.younus]

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.