Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying cells from one worksheet to another, within the same workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying cells from one worksheet to another, within the sameworkbook
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying cells from one worksheet to another, within the sameworkbook
On Apr 22, 12:06*pm, anon 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying cells from one worksheet to another, within the sameworkbook
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying cells from one worksheet to another, within the sameworkbook
On Apr 22, 12:52*pm, anon 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying cells from one worksheet to another, within the sameworkbook
On Apr 22, 1:48*pm, wrote:
On Apr 22, 12:52*pm, anon 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying cells from one worksheet to another, within the sameworkbook
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying cells from one worksheet to another, within the sameworkbook
On Apr 22, 5:10*pm, anon 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying worksheet to another workbook | Excel Worksheet Functions | |||
Copying a worksheet to another workbook | Excel Discussion (Misc queries) | |||
Copying A Worksheet From Each Open Workbook to an new Workbook | Excel Worksheet Functions | |||
Copying worksheet from workbook to another | Excel Worksheet Functions | |||
Copying worksheet to another workbook | Excel Programming |