VBA - copy cells from one sheet to another

[Isis]

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

 

[Bernie Deitrick]

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]

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

 

[Isis]

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.

 

[Bernie Deitrick]

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

 

[Bernie Deitrick]

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

 

[Isis]

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,

 

[Bernie Deitrick]

You're quite welcome.

Bernie
MS Excel MVP

 

[Isis]

Bernie,

How do I check whether a particular sheet exists before creating the sheet
I need ?

Thanks

 

[Bernie Deitrick]

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