Help with Loop Please
 

Does anyone know how I can set up a loop so (let's say anywhere between 10
to 150 or more rows) "all" of the data could be accounted for?

This is what I have:

Dim wbArchive As Workbook
Dim wbOriginal As Workbook

Set wbArchive = ActiveWorkbook
wbArchive.SaveAs Filename:="E:\Archived Logs\Data.xls"

Windows("original_file.xls").Activate
Set wbOriginal = ActiveWorkbook

With wbOriginal.Sheets("Data")
wbArchive.Sheets("Sheet1").Range("A1") = .Range("A3").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B3").Value
wbArchive.Sheets("Sheet1").Range("A2") = .Range("A4").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B4").Value
End With

the wbOriginal header row starts in row 3.
I'd like to be able to loop through the rest of the wboriginal so all (up to
column G) wide, and cover as many rows down


Thanks, Don

Help with Loop Please
 

With wbOriginal.Sheets("Data")
'Loop from 3rd row to 100 row/Col A(1) to G(7)
For lngRow = 3 To 100
For lngCol = 1 To 7
wbArchive.Sheets("Sheet1").Cells(lngRow - 2, lngCol) = .Cells(lngRow,
lngCol).Value
Next
Next
End With

If this post helps click Yes
---------------
Jacob Skaria


"DonW" wrote:

Does anyone know how I can set up a loop so (let's say anywhere between 10
to 150 or more rows) "all" of the data could be accounted for?

This is what I have:

Dim wbArchive As Workbook
Dim wbOriginal As Workbook

Set wbArchive = ActiveWorkbook
wbArchive.SaveAs Filename:="E:\Archived Logs\Data.xls"

Windows("original_file.xls").Activate
Set wbOriginal = ActiveWorkbook

With wbOriginal.Sheets("Data")
wbArchive.Sheets("Sheet1").Range("A1") = .Range("A3").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B3").Value
wbArchive.Sheets("Sheet1").Range("A2") = .Range("A4").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B4").Value
End With

the wbOriginal header row starts in row 3.
I'd like to be able to loop through the rest of the wboriginal so all (up to
column G) wide, and cover as many rows down


Thanks, Don

Help with Loop Please
 

Why not just copy the data to the archive.....

Replace all of this in your (my!) code

With wbOriginal.Sheets("Data")
wbArchive.Sheets("Sheet1").Range("A1") = .Range("A3").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B3").Value
wbArchive.Sheets("Sheet1").Range("A2") = .Range("A4").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B4").Value
End With

with this code.....

With wbOriginal.Sheets("Data")
.Range("A3:G" & .Cells(.Rows.Count, "A").End(xlUp).Row).Copy _
Destination:=wbArchive.Sheets("Sheet1").Range("A1" )
End With

--

Regards,
Nigel




"DonW" wrote in message
...
Does anyone know how I can set up a loop so (let's say anywhere between 10
to 150 or more rows) "all" of the data could be accounted for?

This is what I have:

Dim wbArchive As Workbook
Dim wbOriginal As Workbook

Set wbArchive = ActiveWorkbook
wbArchive.SaveAs Filename:="E:\Archived Logs\Data.xls"

Windows("original_file.xls").Activate
Set wbOriginal = ActiveWorkbook

With wbOriginal.Sheets("Data")
wbArchive.Sheets("Sheet1").Range("A1") = .Range("A3").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B3").Value
wbArchive.Sheets("Sheet1").Range("A2") = .Range("A4").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B4").Value
End With

the wbOriginal header row starts in row 3.
I'd like to be able to loop through the rest of the wboriginal so all (up
to
column G) wide, and cover as many rows down


Thanks, Don

Help with Loop Please
 

Is there a typo that the line before End With should have B2 instead of B1
please?
if affirmative, may be

With wbOriginal.Sheets("Data")
n=.range(.range("a3"),.range("a3").end(xldown)).ro ws.count
wbArchive.Sheets("Sheet1").Range("A1").resize(n,7) =
..Range("A3").resize(n,7)
End With


"DonW" wrote in message
...
Does anyone know how I can set up a loop so (let's say anywhere between 10
to 150 or more rows) "all" of the data could be accounted for?

This is what I have:

Dim wbArchive As Workbook
Dim wbOriginal As Workbook

Set wbArchive = ActiveWorkbook
wbArchive.SaveAs Filename:="E:\Archived Logs\Data.xls"

Windows("original_file.xls").Activate
Set wbOriginal = ActiveWorkbook

With wbOriginal.Sheets("Data")
wbArchive.Sheets("Sheet1").Range("A1") = .Range("A3").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B3").Value
wbArchive.Sheets("Sheet1").Range("A2") = .Range("A4").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B4").Value
End With

the wbOriginal header row starts in row 3.
I'd like to be able to loop through the rest of the wboriginal so all (up
to
column G) wide, and cover as many rows down


Thanks, Don

Help with Loop Please
 

Thanks Jacob, but I never know how many rows I'll have...........but I do
believe KC hit it on the head......
"Jacob Skaria" wrote in message
...
With wbOriginal.Sheets("Data")
'Loop from 3rd row to 100 row/Col A(1) to G(7)
For lngRow = 3 To 100
For lngCol = 1 To 7
wbArchive.Sheets("Sheet1").Cells(lngRow - 2, lngCol) = .Cells(lngRow,
lngCol).Value
Next
Next
End With

If this post helps click Yes
---------------
Jacob Skaria


"DonW" wrote:

Does anyone know how I can set up a loop so (let's say anywhere between
10
to 150 or more rows) "all" of the data could be accounted for?

This is what I have:

Dim wbArchive As Workbook
Dim wbOriginal As Workbook

Set wbArchive = ActiveWorkbook
wbArchive.SaveAs Filename:="E:\Archived Logs\Data.xls"

Windows("original_file.xls").Activate
Set wbOriginal = ActiveWorkbook

With wbOriginal.Sheets("Data")
wbArchive.Sheets("Sheet1").Range("A1") = .Range("A3").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B3").Value
wbArchive.Sheets("Sheet1").Range("A2") = .Range("A4").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B4").Value
End With

the wbOriginal header row starts in row 3.
I'd like to be able to loop through the rest of the wboriginal so all (up
to
column G) wide, and cover as many rows down


Thanks, Don

Help with Loop Please
 

Thank you KC...........I think this will do the trick
"KC" wrote in message
...
Is there a typo that the line before End With should have B2 instead of B1
please?
if affirmative, may be

With wbOriginal.Sheets("Data")
n=.range(.range("a3"),.range("a3").end(xldown)).ro ws.count
wbArchive.Sheets("Sheet1").Range("A1").resize(n,7) =
.Range("A3").resize(n,7)
End With


"DonW" wrote in message
...
Does anyone know how I can set up a loop so (let's say anywhere between
10
to 150 or more rows) "all" of the data could be accounted for?

This is what I have:

Dim wbArchive As Workbook
Dim wbOriginal As Workbook

Set wbArchive = ActiveWorkbook
wbArchive.SaveAs Filename:="E:\Archived Logs\Data.xls"

Windows("original_file.xls").Activate
Set wbOriginal = ActiveWorkbook

With wbOriginal.Sheets("Data")
wbArchive.Sheets("Sheet1").Range("A1") = .Range("A3").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B3").Value
wbArchive.Sheets("Sheet1").Range("A2") = .Range("A4").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B4").Value
End With

the wbOriginal header row starts in row 3.
I'd like to be able to loop through the rest of the wboriginal so all (up
to
column G) wide, and cover as many rows down


Thanks, Don

Help with Loop Please
 

With wbOriginal.Sheets("Data")
'Loop from 3rd row to 100 row/Col A(1) to G(7)
totRows = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
totCols = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

For lngRow = 3 To totRows
For lngCol = 1 To totCols
wbArchive.Sheets("Sheet1").Cells(lngRow - 2, lngCol) = .Cells(lngRow,
lngCol).Value
Next
Next
End With



--
If this post helps click Yes
---------------
Jacob Skaria


"DonW" wrote:

Thanks Jacob, but I never know how many rows I'll have...........but I do
believe KC hit it on the head......
"Jacob Skaria" wrote in message
...
With wbOriginal.Sheets("Data")
'Loop from 3rd row to 100 row/Col A(1) to G(7)
For lngRow = 3 To 100
For lngCol = 1 To 7
wbArchive.Sheets("Sheet1").Cells(lngRow - 2, lngCol) = .Cells(lngRow,
lngCol).Value
Next
Next
End With

If this post helps click Yes
---------------
Jacob Skaria


"DonW" wrote:

Does anyone know how I can set up a loop so (let's say anywhere between
10
to 150 or more rows) "all" of the data could be accounted for?

This is what I have:

Dim wbArchive As Workbook
Dim wbOriginal As Workbook

Set wbArchive = ActiveWorkbook
wbArchive.SaveAs Filename:="E:\Archived Logs\Data.xls"

Windows("original_file.xls").Activate
Set wbOriginal = ActiveWorkbook

With wbOriginal.Sheets("Data")
wbArchive.Sheets("Sheet1").Range("A1") = .Range("A3").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B3").Value
wbArchive.Sheets("Sheet1").Range("A2") = .Range("A4").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B4").Value
End With

the wbOriginal header row starts in row 3.
I'd like to be able to loop through the rest of the wboriginal so all (up
to
column G) wide, and cover as many rows down


Thanks, Don

Help with Loop Please
 

I mean.........

With wbOriginal.Sheets("Data")
'Loop from 3rd row to 100 row/Col A(1) to G(7)
totRows = .Cells(Rows.Count, "A").End(xlUp).Row
totCols = .Cells(1, Columns.Count).End(xlToLeft).Column

For lngRow = 3 To totRows
For lngCol = 1 To totCols
wbArchive.Sheets("Sheet1").Cells(lngRow - 2, lngCol) = .Cells(lngRow,
lngCol).Value
Next
Next

End With


If this post helps click Yes
---------------
Jacob Skaria


"DonW" wrote:

Thanks Jacob, but I never know how many rows I'll have...........but I do
believe KC hit it on the head......
"Jacob Skaria" wrote in message
...
With wbOriginal.Sheets("Data")
'Loop from 3rd row to 100 row/Col A(1) to G(7)
For lngRow = 3 To 100
For lngCol = 1 To 7
wbArchive.Sheets("Sheet1").Cells(lngRow - 2, lngCol) = .Cells(lngRow,
lngCol).Value
Next
Next
End With

If this post helps click Yes
---------------
Jacob Skaria


"DonW" wrote:

Does anyone know how I can set up a loop so (let's say anywhere between
10
to 150 or more rows) "all" of the data could be accounted for?

This is what I have:

Dim wbArchive As Workbook
Dim wbOriginal As Workbook

Set wbArchive = ActiveWorkbook
wbArchive.SaveAs Filename:="E:\Archived Logs\Data.xls"

Windows("original_file.xls").Activate
Set wbOriginal = ActiveWorkbook

With wbOriginal.Sheets("Data")
wbArchive.Sheets("Sheet1").Range("A1") = .Range("A3").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B3").Value
wbArchive.Sheets("Sheet1").Range("A2") = .Range("A4").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B4").Value
End With

the wbOriginal header row starts in row 3.
I'd like to be able to loop through the rest of the wboriginal so all (up
to
column G) wide, and cover as many rows down


Thanks, Don

Help with Loop Please
 

Sorry I'm getting back so late........thanks KC....your's worked the best
for me......and yes, there was a typo in my post......

Don
"KC" wrote in message
...
Is there a typo that the line before End With should have B2 instead of B1
please?
if affirmative, may be

With wbOriginal.Sheets("Data")
n=.range(.range("a3"),.range("a3").end(xldown)).ro ws.count
wbArchive.Sheets("Sheet1").Range("A1").resize(n,7) =
.Range("A3").resize(n,7)
End With


"DonW" wrote in message
...
Does anyone know how I can set up a loop so (let's say anywhere between
10
to 150 or more rows) "all" of the data could be accounted for?

This is what I have:

Dim wbArchive As Workbook
Dim wbOriginal As Workbook

Set wbArchive = ActiveWorkbook
wbArchive.SaveAs Filename:="E:\Archived Logs\Data.xls"

Windows("original_file.xls").Activate
Set wbOriginal = ActiveWorkbook

With wbOriginal.Sheets("Data")
wbArchive.Sheets("Sheet1").Range("A1") = .Range("A3").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B3").Value
wbArchive.Sheets("Sheet1").Range("A2") = .Range("A4").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B4").Value
End With

the wbOriginal header row starts in row 3.
I'd like to be able to loop through the rest of the wboriginal so all (up
to
column G) wide, and cover as many rows down


Thanks, Don