Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA - copy cells from one sheet to another
I want to copy from a sheet with data on to another empty sheet - I want to
loop through the rows of the first sheet one cell at a time, then write the contents into the second empty sheet one cell at a time - I will be looking to manipulate the data slightly before writing it back. What I am asking for is the code to loop through the first sheet and the code to write to the second, one cell at a time but also producing the same number of rows in both sheets when finished - if that makes sense. Thanks for any help |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA - copy cells from one sheet to another
Isis,
Something like this - the code just adds one to the value.... Sub TryNow() Dim myR As Long Dim myRCount As Long Dim myC As Integer Dim myCCount As Integer myRCount = Worksheets("Sheet1"). _ Cells(Rows.Count, 1).End(xlUp).Row myCCount = Worksheets("Sheet1"). _ Cells(1, Columns.Count).End(xlToLeft).Column For myR = 1 To myRCount For myC = 1 To myCCount Worksheets("Sheet2").Cells(myR, myC).Value = _ Worksheets("Sheet1").Cells(myR, myC).Value + 1 Next myC Next myR End Sub HTH, Bernie MS Excel MVP "Isis" wrote in message ... I want to copy from a sheet with data on to another empty sheet - I want to loop through the rows of the first sheet one cell at a time, then write the contents into the second empty sheet one cell at a time - I will be looking to manipulate the data slightly before writing it back. What I am asking for is the code to loop through the first sheet and the code to write to the second, one cell at a time but also producing the same number of rows in both sheets when finished - if that makes sense. Thanks for any help |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA - copy cells from one sheet to another
"Bernie Deitrick" <deitbe @ consumer dot org wrote in
: Bernie, Sorry for not replying sooner - yes, that code demonstrates what I needed to know - thank you very much. Is there a way to check for (and create if needed) a new sheet ? Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA - copy cells from one sheet to another
"Bernie Deitrick" <deitbe @ consumer dot org wrote in
: Bernie, Also this will only copy one column - I need to copy many columns - I don't quite see how to seperate out the 'loops' from your code ? Thanks for any further help. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA - copy cells from one sheet to another
Of course - do you have criteria for creating a new sheet?
Dim myS As Worksheet If Criteria = True Then Set myS = Worksheets.Add myS.Name = "Newly added sheet" End If Then you can either use myS.Cells(row, col).Value = "Whatever" or Worksheets("Newly added sheet").Cells(row, col).Value = "Whatever" HTH, Bernie MS Excel MVP "Isis" wrote in message ... "Bernie Deitrick" <deitbe @ consumer dot org wrote in : Bernie, Sorry for not replying sooner - yes, that code demonstrates what I needed to know - thank you very much. Is there a way to check for (and create if needed) a new sheet ? Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA - copy cells from one sheet to another
Isis,
The code is written to pick up the number of rows from column A, and the number of columns from row 1. You could also use Sub TryNow() Dim myR As Long Dim myRCount As Long Dim myC As Integer Dim myCCount As Integer Dim myUR As Range Set myUR = Worksheets("Sheet1").UsedRange myRCount = myUR.Cells(myUR.Cells.Count).Row myCCount = myUR.Cells(myUR.Cells.Count).Column For myR = 1 To myRCount For myC = 1 To myCCount Worksheets("Sheet2").Cells(myR, myC).Value = _ Worksheets("Sheet1").Cells(myR, myC).Value + 1 Next myC Next myR End Sub -- HTH, Bernie MS Excel MVP "Isis" wrote in message ... "Bernie Deitrick" <deitbe @ consumer dot org wrote in : Bernie, Also this will only copy one column - I need to copy many columns - I don't quite see how to seperate out the 'loops' from your code ? Thanks for any further help. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA - copy cells from one sheet to another
"Bernie Deitrick" <deitbe @ consumer dot org wrote in
: Bernie, Thanks for the quick reply - much appreciated. I found that the reason I was not getting all my data with your first code was because I had nothing in the first row, this caused only the first column to be copied - I have learned something useful. Thanks for all the code and yout time. Regards, |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA - copy cells from one sheet to another
You're quite welcome.
Bernie MS Excel MVP Bernie, Thanks for the quick reply - much appreciated. I found that the reason I was not getting all my data with your first code was because I had nothing in the first row, this caused only the first column to be copied - I have learned something useful. Thanks for all the code and yout time. Regards, |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA - copy cells from one sheet to another
"Bernie Deitrick" <deitbe @ consumer dot org wrote in
: Bernie, How do I check whether a particular sheet exists before creating the sheet I need ? Thanks |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA - copy cells from one sheet to another
A simple techniques:
Dim mySname As String Dim myS As Worksheet On Error GoTo CreateSheet: mySname = Worksheets("Newly added sheet").Name GoTo SheetExists CreateSheet: Set myS = Worksheets.Add myS.Name = "Newly added sheet" SheetExists: But, if you want to delete it (whether it exists or not, to, say, fully re-create it) then use: Dim myS As Worksheet On Error Resume Next Application.DisplayAlerts = False Worksheets("Newly added sheet").Delete Set myS = Worksheets.Add myS.Name = "Newly added sheet" Application.DisplayAlerts = True HTH, Bernie MS Excel MVP "Isis" wrote in message ... "Bernie Deitrick" <deitbe @ consumer dot org wrote in : Bernie, How do I check whether a particular sheet exists before creating the sheet I need ? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Visible Cells in Sheet with Merged and Hidden Cells | Excel Discussion (Misc queries) | |||
copy data of two cells from Sheet 2 into one cell in Sheet 1 | Excel Worksheet Functions | |||
Copy cells from one sheet to the next available row on another? | Excel Discussion (Misc queries) | |||
Copy sheet 1 data to sheet 2 cells. | Excel Worksheet Functions | |||
Copy row sheet-sheet skipping unqualified cells | Excel Worksheet Functions |