|
Macro paste values help
This is my first substantial excel macro... so far, things are going
well, but I'm confused about pasting values only... I've been using this expression to cut and paste, which works fine. CopySheet.Range(Hs).Copy Destination:= PasteSheet.Cells(BottomRow, 1) but then realized that I need to paste the values only. I tried using CopySheet.Range(Hs).Value = PasteSheet.Cells(BottomRow, 1).Value which doesn't work, presumably because I'm just giving it the starting cell, rather than a range. I guess that I can use separate Copy and PasteSpecial instructions, but from everything I've read on this group, I should try to avoid this. I'm not sure how to go about giving it a paste range, though, because this will change each time through a loop. I've put the full code below, if it will help. I'd certainly appreciate any other suggestions that anyone notices. Thanks, mike Sub ShuffleForceDataPrism() ' ' ShuffleForceDataPrism Macro ' Macro recorded 1/24/2007 by Moose ' This macro combines the force and wave height ' data from all sites into a single sheet ' in the format suitable for Prism software ' ' turn calculation off to speed up... don't forget to turn it back on Application.Calculation = xlCalculationManual ' Define Variables Dim i As Integer Dim PasteSheet As Worksheet Dim DataSheet As Worksheet Dim HeaderText As Variant Dim SheetsList As Variant Dim BottomRow As Integer ' Set variables Set PasteSheet = Worksheets("ADP") Set CopySheet = Worksheets("A2") Hs = "Q2:Q75" F1cm = "O2:O75" SheetsList = Array("A1", "A2", "A3", "A4", "A5", "A6", "A7", "A8", "A9", "A10", "A11", "A12", "A13", "B1", "B2", "B3", "B4", "B5", "B6", "B7", "B8", "B9", "B10", "B11", "B12", "B13", "C1", "C2", "C3", "C4", "C5", "C6", "C7", "C8", "C9", "C10", "C11", "C12", "C13", "C14", "C15", "C16", "C17", "C18", "C19", "C20", "C21") HeaderText = Array("Hs", "A01", "A02", "A03", "A04", "A05", "A06", "A07", "A08", "A09", "A10", "A11", "A12", "A13", "B01", "B02", "B03", "B04", "B05", "B06", "B07", "B08", "B09", "B10", "B11", "B12", "B13", "C01", "C02", "C03", "C04", "C05", "C06", "C07", "C08", "C09", "C10", "C11", "C12", "C13", "C14", "C15", "C16", "C17", "C18", "C19", "C20", "C21") ' Clear PasteSheet PasteSheet.Cells.ClearContents ' Add Header Row using the text in Header Text For i = 1 To 47 'Loop through text array PasteSheet.Cells(1, i).Value = HeaderText(i - 1) Next ' Loop through sheets list and cut data BottomRow = 2 For i = 0 To 2 Set CopySheet = Worksheets(SheetsList(i)) CopySheet.Range(Hs).Value = PasteSheet.Cells(BottomRow, 1) 'get Hs and put in Col 1 CopySheet.Range(F1cm).Copy Destination:=PasteSheet.Cells(BottomRow, i + 2) 'Get force and put in correct Col BottomRow = BottomRow + 50 ' increment a lot, will remove empty rows later Next |
Macro paste values help
Mike,
You just had it backwards - as long as your ranges are the same size: PasteSheet.Cells(BottomRow, 1).Value = CopySheet.Range(Hs).Value HTH, Bernie MS Excel MVP "mooseo" wrote in message ups.com... This is my first substantial excel macro... so far, things are going well, but I'm confused about pasting values only... I've been using this expression to cut and paste, which works fine. CopySheet.Range(Hs).Copy Destination:= PasteSheet.Cells(BottomRow, 1) but then realized that I need to paste the values only. I tried using CopySheet.Range(Hs).Value = PasteSheet.Cells(BottomRow, 1).Value which doesn't work, presumably because I'm just giving it the starting cell, rather than a range. I guess that I can use separate Copy and PasteSpecial instructions, but from everything I've read on this group, I should try to avoid this. I'm not sure how to go about giving it a paste range, though, because this will change each time through a loop. I've put the full code below, if it will help. I'd certainly appreciate any other suggestions that anyone notices. Thanks, mike Sub ShuffleForceDataPrism() ' ' ShuffleForceDataPrism Macro ' Macro recorded 1/24/2007 by Moose ' This macro combines the force and wave height ' data from all sites into a single sheet ' in the format suitable for Prism software ' ' turn calculation off to speed up... don't forget to turn it back on Application.Calculation = xlCalculationManual ' Define Variables Dim i As Integer Dim PasteSheet As Worksheet Dim DataSheet As Worksheet Dim HeaderText As Variant Dim SheetsList As Variant Dim BottomRow As Integer ' Set variables Set PasteSheet = Worksheets("ADP") Set CopySheet = Worksheets("A2") Hs = "Q2:Q75" F1cm = "O2:O75" SheetsList = Array("A1", "A2", "A3", "A4", "A5", "A6", "A7", "A8", "A9", "A10", "A11", "A12", "A13", "B1", "B2", "B3", "B4", "B5", "B6", "B7", "B8", "B9", "B10", "B11", "B12", "B13", "C1", "C2", "C3", "C4", "C5", "C6", "C7", "C8", "C9", "C10", "C11", "C12", "C13", "C14", "C15", "C16", "C17", "C18", "C19", "C20", "C21") HeaderText = Array("Hs", "A01", "A02", "A03", "A04", "A05", "A06", "A07", "A08", "A09", "A10", "A11", "A12", "A13", "B01", "B02", "B03", "B04", "B05", "B06", "B07", "B08", "B09", "B10", "B11", "B12", "B13", "C01", "C02", "C03", "C04", "C05", "C06", "C07", "C08", "C09", "C10", "C11", "C12", "C13", "C14", "C15", "C16", "C17", "C18", "C19", "C20", "C21") ' Clear PasteSheet PasteSheet.Cells.ClearContents ' Add Header Row using the text in Header Text For i = 1 To 47 'Loop through text array PasteSheet.Cells(1, i).Value = HeaderText(i - 1) Next ' Loop through sheets list and cut data BottomRow = 2 For i = 0 To 2 Set CopySheet = Worksheets(SheetsList(i)) CopySheet.Range(Hs).Value = PasteSheet.Cells(BottomRow, 1) 'get Hs and put in Col 1 CopySheet.Range(F1cm).Copy Destination:=PasteSheet.Cells(BottomRow, i + 2) 'Get force and put in correct Col BottomRow = BottomRow + 50 ' increment a lot, will remove empty rows later Next |
Macro paste values help
Ah, of course. That explains all the blank ranges in my worksheet...
Thanks a bunch. moose On Jan 26, 5:52 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mike, You just had it backwards - as long as your ranges are the same size: PasteSheet.Cells(BottomRow, 1).Value = CopySheet.Range(Hs).Value HTH, Bernie MS Excel MVP |
Macro paste values help
Actually, though, I realize that this isn't quite what I need. The
problem is that I don't necessarily know how long the range is going to be. BottomRow is the row index of the first cell where I want to paste the new values. Can I easily specify to go from BottomRow to BottomRow + Range(Hs).Rows.Count ? I guess it might be easier to just copy and paste special. but it seems so much cleaner to not do that. Thanks again, moose On Jan 26, 5:52 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mike, You just had it backwards - as long as your ranges are the same size: PasteSheet.Cells(BottomRow, 1).Value = CopySheet.Range(Hs).Value HTH, Bernie MS Excel MVP "mooseo" wrote in oglegroups.com... This is my first substantial excel macro... so far, things are going well, but I'm confused about pasting values only... I've been using this expression to cut and paste, which works fine. CopySheet.Range(Hs).Copy Destination:= PasteSheet.Cells(BottomRow, 1) but then realized that I need to paste the values only. I tried using CopySheet.Range(Hs).Value = PasteSheet.Cells(BottomRow, 1).Value which doesn't work, presumably because I'm just giving it the starting cell, rather than a range. I guess that I can use separate Copy and PasteSpecial instructions, but from everything I've read on this group, I should try to avoid this. I'm not sure how to go about giving it a paste range, though, because this will change each time through a loop. I've put the full code below, if it will help. I'd certainly appreciate any other suggestions that anyone notices. Thanks, mike Sub ShuffleForceDataPrism() ' ' ShuffleForceDataPrism Macro ' Macro recorded 1/24/2007 by Moose ' This macro combines the force and wave height ' data from all sites into a single sheet ' in the format suitable for Prism software ' ' turn calculation off to speed up... don't forget to turn it back on Application.Calculation = xlCalculationManual ' Define Variables Dim i As Integer Dim PasteSheet As Worksheet Dim DataSheet As Worksheet Dim HeaderText As Variant Dim SheetsList As Variant Dim BottomRow As Integer ' Set variables Set PasteSheet = Worksheets("ADP") Set CopySheet = Worksheets("A2") Hs = "Q2:Q75" F1cm = "O2:O75" SheetsList = Array("A1", "A2", "A3", "A4", "A5", "A6", "A7", "A8", "A9", "A10", "A11", "A12", "A13", "B1", "B2", "B3", "B4", "B5", "B6", "B7", "B8", "B9", "B10", "B11", "B12", "B13", "C1", "C2", "C3", "C4", "C5", "C6", "C7", "C8", "C9", "C10", "C11", "C12", "C13", "C14", "C15", "C16", "C17", "C18", "C19", "C20", "C21") HeaderText = Array("Hs", "A01", "A02", "A03", "A04", "A05", "A06", "A07", "A08", "A09", "A10", "A11", "A12", "A13", "B01", "B02", "B03", "B04", "B05", "B06", "B07", "B08", "B09", "B10", "B11", "B12", "B13", "C01", "C02", "C03", "C04", "C05", "C06", "C07", "C08", "C09", "C10", "C11", "C12", "C13", "C14", "C15", "C16", "C17", "C18", "C19", "C20", "C21") ' Clear PasteSheet PasteSheet.Cells.ClearContents ' Add Header Row using the text in Header Text For i = 1 To 47 'Loop through text array PasteSheet.Cells(1, i).Value = HeaderText(i - 1) Next ' Loop through sheets list and cut data BottomRow = 2 For i = 0 To 2 Set CopySheet = Worksheets(SheetsList(i)) CopySheet.Range(Hs).Value = PasteSheet.Cells(BottomRow, 1) 'get Hs and put in Col 1 CopySheet.Range(F1cm).Copy Destination:=PasteSheet.Cells(BottomRow, i + 2) 'Get force and put in correct Col BottomRow = BottomRow + 50 ' increment a lot, will remove empty rows later Next |
Macro paste values help
Moose,
PasteSheet.Cells(BottomRow, 1).Resize(Range(Hs).Rows.Count).Value = CopySheet.Range(Hs).Value should work... HTH, Bernie MS Excel MVP "mooseo" wrote in message oups.com... Actually, though, I realize that this isn't quite what I need. The problem is that I don't necessarily know how long the range is going to be. BottomRow is the row index of the first cell where I want to paste the new values. Can I easily specify to go from BottomRow to BottomRow + Range(Hs).Rows.Count ? I guess it might be easier to just copy and paste special. but it seems so much cleaner to not do that. Thanks again, moose On Jan 26, 5:52 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mike, You just had it backwards - as long as your ranges are the same size: PasteSheet.Cells(BottomRow, 1).Value = CopySheet.Range(Hs).Value HTH, Bernie MS Excel MVP "mooseo" wrote in oglegroups.com... This is my first substantial excel macro... so far, things are going well, but I'm confused about pasting values only... I've been using this expression to cut and paste, which works fine. CopySheet.Range(Hs).Copy Destination:= PasteSheet.Cells(BottomRow, 1) but then realized that I need to paste the values only. I tried using CopySheet.Range(Hs).Value = PasteSheet.Cells(BottomRow, 1).Value which doesn't work, presumably because I'm just giving it the starting cell, rather than a range. I guess that I can use separate Copy and PasteSpecial instructions, but from everything I've read on this group, I should try to avoid this. I'm not sure how to go about giving it a paste range, though, because this will change each time through a loop. I've put the full code below, if it will help. I'd certainly appreciate any other suggestions that anyone notices. Thanks, mike Sub ShuffleForceDataPrism() ' ' ShuffleForceDataPrism Macro ' Macro recorded 1/24/2007 by Moose ' This macro combines the force and wave height ' data from all sites into a single sheet ' in the format suitable for Prism software ' ' turn calculation off to speed up... don't forget to turn it back on Application.Calculation = xlCalculationManual ' Define Variables Dim i As Integer Dim PasteSheet As Worksheet Dim DataSheet As Worksheet Dim HeaderText As Variant Dim SheetsList As Variant Dim BottomRow As Integer ' Set variables Set PasteSheet = Worksheets("ADP") Set CopySheet = Worksheets("A2") Hs = "Q2:Q75" F1cm = "O2:O75" SheetsList = Array("A1", "A2", "A3", "A4", "A5", "A6", "A7", "A8", "A9", "A10", "A11", "A12", "A13", "B1", "B2", "B3", "B4", "B5", "B6", "B7", "B8", "B9", "B10", "B11", "B12", "B13", "C1", "C2", "C3", "C4", "C5", "C6", "C7", "C8", "C9", "C10", "C11", "C12", "C13", "C14", "C15", "C16", "C17", "C18", "C19", "C20", "C21") HeaderText = Array("Hs", "A01", "A02", "A03", "A04", "A05", "A06", "A07", "A08", "A09", "A10", "A11", "A12", "A13", "B01", "B02", "B03", "B04", "B05", "B06", "B07", "B08", "B09", "B10", "B11", "B12", "B13", "C01", "C02", "C03", "C04", "C05", "C06", "C07", "C08", "C09", "C10", "C11", "C12", "C13", "C14", "C15", "C16", "C17", "C18", "C19", "C20", "C21") ' Clear PasteSheet PasteSheet.Cells.ClearContents ' Add Header Row using the text in Header Text For i = 1 To 47 'Loop through text array PasteSheet.Cells(1, i).Value = HeaderText(i - 1) Next ' Loop through sheets list and cut data BottomRow = 2 For i = 0 To 2 Set CopySheet = Worksheets(SheetsList(i)) CopySheet.Range(Hs).Value = PasteSheet.Cells(BottomRow, 1) 'get Hs and put in Col 1 CopySheet.Range(F1cm).Copy Destination:=PasteSheet.Cells(BottomRow, i + 2) 'Get force and put in correct Col BottomRow = BottomRow + 50 ' increment a lot, will remove empty rows later Next |
Macro paste values help
Fantastic, thanks!
|
| All times are GMT +1. The time now is 01:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com