Copy cell values to another sheet
Albert,
It isn't clear what cells in the row you want to copy. have a look at the
code below and in the bit where we find 'Food' tell us what you want copying.
Bear in mind that when using OFFSET the syntax is OFFSET(Row, Column)
Sub copyCells()
' Testsubmit Macro
'Declare variables
Dim wb As Workbook
Dim ws As Worksheet
Dim newsh As Worksheet
Dim MyRange As Range
Dim LastRow As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set wb = ThisWorkbook
Set newsh = wb.Worksheets.Add
newsh.Name = "Orders for next month"
newsh.Range("a1") = "Orders for the month"
newsh.Range("a2") = "Item"
newsh.Range("b2") = "Quantity"
LastRow = wb.Worksheets("sheet1").Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = wb.Worksheets("sheet1").Range("A1:A" & LastRow)
For Each c In MyRange
If c.Offset(, 1) = "Food" Then
'tell us which cells to copy
End If
Next
--
Mike
When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
"Albert" wrote:
Hi Guys,
I am trying to create a macro (unsuccessfully) to:
1. Create a new sheet
2. select a range
3. loop through the range until the range is empty
4. check criteria of cells in range
5. if true copy 2of the cells in the row range to the new worksheet
6. loop through range and create a list in the new worksheet
This is what I got so far:
Sub copyCells()
' Testsubmit Macro
'Declare variables
Dim wb As Workbook
Dim wssheet1 As Worksheet
Dim wssheet2 As Worksheet
Dim ws As Worksheet
Dim newsh As Worksheet
Dim DestinationRange As Range
Dim SourceRange As Range
Dim lRow As Integer
Set wb = ThisWorkbook
Set newsh = wb.Worksheets.Add
newsh.Name = "Orders for next month"
newsh.Range("a1") = "Orders for the month"
newsh.Range("a2") = "Item"
newsh.Range("b2") = "Quantity"
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Do Until IsEmpty(SourceRange)
Set SourceRange = wb.Worksheets("sheet1").Range("a1")
If SourceRange.Offset(1, 0) = "Food" And SourceRange.Offset(1, 0)
"0" Then
Set DestinationRange = wb.Worksheets("Orders for the month").Range("a1")
SourceRange.Offset(1, 0) = DestinationRange.Offset(3, 0)
SourceRange.Offset(1, 5) = DestinationRange.Offset(3, 1)
Loop
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Please help?
Thanks
Albert
|