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

C

Cynthia

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?
 
S

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.
 
K

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>


Cynthia said:
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?
 
K

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>




Cynthia said:
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top