Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop to run macro each row | Excel Discussion (Misc queries) | |||
how to put a loop in a macro? | New Users to Excel | |||
macro loop | Excel Discussion (Misc queries) | |||
how can i loop a macro | Excel Discussion (Misc queries) | |||
macro loop | Excel Discussion (Misc queries) |