transfer data between worksheets
 

right now i'm using the following code to transfer data. instead of
reapeating the line 26 times is there a way to create this code in a smaller
statement if the data row always increases by 20 and the daily row by 1.

Private Sub Command1_Click()

Dim Daily As Worksheet
Dim Data As Worksheet


Set Daily = Worksheets("Daily")
Set Data = Worksheets("Data")

Daily.Range("A12").Value = Data.Range("A5").Value
Daily.Range("A32").Value = Data.Range("A6").Value
'all the way down to'
Daily. Range("A562").Value=Data.Range("A30").Value



End Sub

transfer data between worksheets
 

Try this. It assumes you have selected the first cell on 'Daily' where you
want the data transferred to. By the way, the last cell will be A512, not
A562.

Sub TransferData()
' Transfers data from one sheet to another.
'
' Target sheet (Daily) receives the data starting in row 12,
' and increments 20 rows for each cell of source sheet (Data) value.
'
' Source sheet (Data) values are in rows 5 to 30. (count=26)

Dim Daily As Worksheet, Data As Worksheet
Dim lRow As Long, lSrow As Long

Set Daily = Sheets("Daily")
Set Data = Sheets("Data")

' Select the first target cell ("A12") on 'Daily'
lRow = Selection.Row
' Set the # of the first row on 'Data' containing values to transfer
lSrow = 5 ' ("A5")

Do
Daily.Cells(lRow, 1) = Data.Cells(lSrow, 1)
lRow = lRow + 20
lSrow = lSrow + 1
Loop While lSrow < 31

End Sub



"Qaspec" wrote:

right now i'm using the following code to transfer data. instead of
reapeating the line 26 times is there a way to create this code in a smaller
statement if the data row always increases by 20 and the daily row by 1.

Private Sub Command1_Click()

Dim Daily As Worksheet
Dim Data As Worksheet


Set Daily = Worksheets("Daily")
Set Data = Worksheets("Data")

Daily.Range("A12").Value = Data.Range("A5").Value
Daily.Range("A32").Value = Data.Range("A6").Value
'all the way down to'
Daily. Range("A562").Value=Data.Range("A30").Value



End Sub

transfer data between worksheets
 

Qaspec wrote:
right now i'm using the following code to transfer data. instead of
reapeating the line 26 times is there a way to create this code in a smaller
statement if the data row always increases by 20 and the daily row by 1.

Private Sub Command1_Click()

Dim Daily As Worksheet
Dim Data As Worksheet


Set Daily = Worksheets("Daily")
Set Data = Worksheets("Data")

Daily.Range("A12").Value = Data.Range("A5").Value
Daily.Range("A32").Value = Data.Range("A6").Value
'all the way down to'
Daily. Range("A562").Value=Data.Range("A30").Value



End Sub




I'm looking to do nearly the same thing, but in my case, the data range
is constant, but the worksheet names change. I have the following:

Private Sub btnCombineOrders_Click()

'get today's date and set that tab to active
today = Trim(Replace(Left(Now(), 10), "/", "-"))
Sheets(today).Select

'loop through all leg's orders
For Each leg In Sheets("Leg Names").Range("A1:A100")
If leg.Value < "" Then
wkbname = "Tool Order - " & leg.Value & ".xls"
For LegRow = 12 To 51
LegQty = Worksheets(wkbname).Sheets(today).Cells(LegRow, 2).Value
LegSKU = Worksheets(wkbname).Sheets(today).Cells(LegRow, 3).Value
Next
End If
Next

End Sub

When I execute this code, I'm getting a "run-time error '9': Subscript
out of range" error on this line:
LegQty = Worksheets(wkbname).Sheets(today).Cells(LegRow, 2).Value

This is my first whack at VBA programming, and I'm not really that
proficient at VB, either, though I've got a programming background. Any
suggestions (particularly where to look in the help files for this info)
would be greatly appreciated.

Thanks!
bds

transfer data between worksheets
 

Brett wrote:
<snip
I'm looking to do nearly the same thing, but in my case, the data range
is constant, but the worksheet names change. I have the following:

Private Sub btnCombineOrders_Click()

'get today's date and set that tab to active
today = Trim(Replace(Left(Now(), 10), "/", "-"))
Sheets(today).Select

'loop through all leg's orders
For Each leg In Sheets("Leg Names").Range("A1:A100")
If leg.Value < "" Then
wkbname = "Tool Order - " & leg.Value & ".xls"
For LegRow = 12 To 51
LegQty = Worksheets(wkbname).Sheets(today).Cells(LegRow, 2).Value
LegSKU = Worksheets(wkbname).Sheets(today).Cells(LegRow, 3).Value
Next
End If
Next

End Sub

When I execute this code, I'm getting a "run-time error '9': Subscript
out of range" error on this line:
LegQty = Worksheets(wkbname).Sheets(today).Cells(LegRow, 2).Value

This is my first whack at VBA programming, and I'm not really that
proficient at VB, either, though I've got a programming background. Any
suggestions (particularly where to look in the help files for this info)
would be greatly appreciated.

Thanks!
bds

With the help from Tom Ogilvy from a post that I hadn't quite gotten to
reading before I posted this, I managed to find may through this one.

Tom - thanks for the unintentional assist!

bds