Need a macro that will continue to run as long as spreadsheet cells have info

[Guest]

Hope someone can help. I have 2 spreadsheets. In SS1 I'm pulling data from columns B, C, onward and pasting it into rows 1, 2, etc in SS2. Of course I can easily record a macro by performing this function 1 cell at a time, but these will eventually be very large spreadsheets, maybe 700 columns in SS1 being pasted into 700 rows in SS2. How do I write the macro to perform this function for one column and tell it to continue running as long as the next column has data?

 

[Steve Schapel]

Cynthia,

The focus of this newsgroup is macros within Access, the database
programme. The concept of macros in Access is very different from the
concept of macros in Excel. You may have a better chance of a good
reply in an Excel newsgroup.

 

[Ken Snell]

Steve is right....but perhaps I can help:

Dim wks1 As Worksheet, wks2 As Worksheet
Dim lngRow As Long
lngRow = 1
Do While wks1.Cells(lngRow, 3).Value <> ""
wks2.Cells(lngRow, 2).Value =wks1.Cells(lngRow, 2).Value
wks2.Cells(lngRow, 3).Value =wks1.Cells(lngRow, 3).Value
lngRow = lngRow + 1
Loop

--
Ken Snell
<MS ACCESS MVP>

Hope someone can help. I have 2 spreadsheets. In SS1 I'm pulling data

from columns B, C, onward and pasting it into rows 1, 2, etc in SS2. Of
course I can easily record a macro by performing this function 1 cell at a
time, but these will eventually be very large spreadsheets, maybe 700
columns in SS1 being pasted into 700 rows in SS2. How do I write the macro
to perform this function for one column and tell it to continue running as
long as the next column has data?

 

[Ken Snell]

oops...forgot to include the setting of the two worksheet variables:


Dim wks1 As Worksheet, wks2 As Worksheet
Dim lngRow As Long
Set wks1 = Workbooks("FileName").Worksheets("SS1")
Set wks2 = Workbooks("FileName").Worksheets("SS2")
lngRow = 1
Do While wks1.Cells(lngRow, 3).Value <> ""
wks2.Cells(lngRow, 2).Value =wks1.Cells(lngRow, 2).Value
wks2.Cells(lngRow, 3).Value =wks1.Cells(lngRow, 3).Value
lngRow = lngRow + 1
Loop
Set wks1 = Nothing
Set wks2 = Nothing

--
Ken Snell
<MS ACCESS MVP>

Hope someone can help. I have 2 spreadsheets. In SS1 I'm pulling data

from columns B, C, onward and pasting it into rows 1, 2, etc in SS2. Of
course I can easily record a macro by performing this function 1 cell at a
time, but these will eventually be very large spreadsheets, maybe 700
columns in SS1 being pasted into 700 rows in SS2. How do I write the macro
to perform this function for one column and tell it to continue running as
long as the next column has data?