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 |
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 |
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 |
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. |
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 |
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. |
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, |
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, |
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 |
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 |
All times are GMT +1. The time now is 04:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com