Help with using a Macro Loop
 

Hello,

could someone help me with using a macro?

I need to use a macro to work along the columns of a spreadsheet (i.e.
select cell A1, copy, paste in another spreadsheet's A1, then select cell B1
and if not blank, copy and paste in the other spreadsheet's B1 and so on).

I will be using nearly all the columns in the spreadsheet so it's not
feasable to just type in the cells in the macro, hence the need for a loop.

Any help would be appreciated!!


Thanks

Help with using a Macro Loop
 

Mark,

This copies the used range of row 1 sheet 1 to row 1 sheet 2

Right click the sheet tab with your data in, view code and paste this in:-

Sub stantial()
Dim MyRange As Range
Set MyRange = Range("A1:" & Range("IV1").End(xlToLeft).Address)
MyRange.Copy
Sheets("Sheet2").Range("A1").Select
ActiveSheet.Paste
End Sub

Mike

"Mark Costello" wrote:

Hello,

could someone help me with using a macro?

I need to use a macro to work along the columns of a spreadsheet (i.e.
select cell A1, copy, paste in another spreadsheet's A1, then select cell B1
and if not blank, copy and paste in the other spreadsheet's B1 and so on).

I will be using nearly all the columns in the spreadsheet so it's not
feasable to just type in the cells in the macro, hence the need for a loop.

Any help would be appreciated!!


Thanks

Help with using a Macro Loop
 

Thank you for that, although I don't want to copy and paste the values as a
range, I need to do them individually (what I'm trying to do isn't as simple
as my example, unfortunately)

"Mike H" wrote:

Mark,

This copies the used range of row 1 sheet 1 to row 1 sheet 2

Right click the sheet tab with your data in, view code and paste this in:-

Sub stantial()
Dim MyRange As Range
Set MyRange = Range("A1:" & Range("IV1").End(xlToLeft).Address)
MyRange.Copy
Sheets("Sheet2").Range("A1").Select
ActiveSheet.Paste
End Sub

Mike

"Mark Costello" wrote:

Hello,

could someone help me with using a macro?

I need to use a macro to work along the columns of a spreadsheet (i.e.
select cell A1, copy, paste in another spreadsheet's A1, then select cell B1
and if not blank, copy and paste in the other spreadsheet's B1 and so on).

I will be using nearly all the columns in the spreadsheet so it's not
feasable to just type in the cells in the macro, hence the need for a loop.

Any help would be appreciated!!


Thanks

Help with using a Macro Loop
 

The following macro copies all of row 1 in Sheet1 to row 1 of Sheet2

------------------------------------------------------------------------------------------------
Sub CopyRow()

Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim varVal(255) As Variant
Dim i As Integer

Set wsSource = ThisWorkbook.Sheets(1)
Set wsTarget = ThisWorkbook.Sheets(2)

With wsSource.Range("A1")
For i = 0 To 255
varVal(i) = .Offset(, i).Value
Next i
End With

With wsTarget.Range("A1")
For i = 0 To 255
.Offset(, i).Value = varVal(i)
Next i
End With

Set wsSource = Nothing
Set wsTarget = Nothing

End Sub

------------------------------------------------------------------------------------------------

--

Kevin Backmann


"Mark Costello" wrote:

Hello,

could someone help me with using a macro?

I need to use a macro to work along the columns of a spreadsheet (i.e.
select cell A1, copy, paste in another spreadsheet's A1, then select cell B1
and if not blank, copy and paste in the other spreadsheet's B1 and so on).

I will be using nearly all the columns in the spreadsheet so it's not
feasable to just type in the cells in the macro, hence the need for a loop.

Any help would be appreciated!!


Thanks

Help with using a Macro Loop
 

Mark,

This copies the values 1 cell at a time


Sub stantial()
x = 1
Dim MyRange As Range
Set MyRange = Worksheets("Sheet1").Range("A1:" &
Range("IV1").End(xlToLeft).Address)
For Each c In MyRange
c.Select
Worksheets("Sheet2").Cells(1, x).Value = c.Value
x = x + 1
Next
End Sub

Mike


"Mark Costello" wrote:

Thank you for that, although I don't want to copy and paste the values as a
range, I need to do them individually (what I'm trying to do isn't as simple
as my example, unfortunately)

"Mike H" wrote:

Mark,

This copies the used range of row 1 sheet 1 to row 1 sheet 2

Right click the sheet tab with your data in, view code and paste this in:-

Sub stantial()
Dim MyRange As Range
Set MyRange = Range("A1:" & Range("IV1").End(xlToLeft).Address)
MyRange.Copy
Sheets("Sheet2").Range("A1").Select
ActiveSheet.Paste
End Sub

Mike

"Mark Costello" wrote:

Hello,

could someone help me with using a macro?

I need to use a macro to work along the columns of a spreadsheet (i.e.
select cell A1, copy, paste in another spreadsheet's A1, then select cell B1
and if not blank, copy and paste in the other spreadsheet's B1 and so on).

I will be using nearly all the columns in the spreadsheet so it's not
feasable to just type in the cells in the macro, hence the need for a loop.

Any help would be appreciated!!


Thanks

Help with using a Macro Loop
 

Are you are saying that your original data is
a1 data b1 blank c1 data d:f1 blank g1 data
and you want it to look like
A B C
a1 c1 g1 on another sheet

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mark Costello" wrote in message
...
Hello,

could someone help me with using a macro?

I need to use a macro to work along the columns of a spreadsheet (i.e.
select cell A1, copy, paste in another spreadsheet's A1, then select cell
B1
and if not blank, copy and paste in the other spreadsheet's B1 and so on).

I will be using nearly all the columns in the spreadsheet so it's not
feasable to just type in the cells in the macro, hence the need for a
loop.

Any help would be appreciated!!


Thanks