hoss97
04-08-2009, 08:15 AM
A large spreadsheet for data collection has become corrupted, and I am trying to move data to a new workbook. I have tried to modify and write some code to copy visible cells (with a user input box), and then paste to a range of visible cells (with another user input box), without much success. The range will always be similar to "B14:I125" (could be "J14:R125") etc. I have attached a small file so you can see sample data.
Below is the first part of the Macro...but I want the user to be able to define where the range starts by clicking on a cell. Then it will copy the visible cells in the 9 column range and 125 rows down.
Sub copyvisiblecells()
'
' copyvisiblecells Macro
' Macro recorded 4/8/2009 by John
'
'Declare local variables.
Dim EndWB As Workbook, EndWS As Worksheet
Dim Target As Range, CurrCell As Range
Dim x As Long, FromCnt As Long
'Select the range to copy.
Set Target = Application.InputBox _
(Prompt:="Select the first cell in the copy range", Type:=8)
Range("a14:i125").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
'Call pastetovisiblecells after a ten-second delay.
Application.OnTime Now() + TimeValue("0:00:09"), "pastetovisiblecells"
End Sub
Below is the second part of the code, but I want the user to be able to define the beginning cell for the paste to start, and then paste only to the visible cells in the 9 column, 125 row range.
Sub pastetovisiblecells()
'
'
'Declare local variables.
Dim EndWB As Workbook, EndWS As Worksheet
Dim Target As Range, CurrCell As Range
Dim x As Long, FromCnt As Long
'Select the range where it should be pasted.
Set Target = Application.InputBox _
(Prompt:="Select the first cell in the Paste range", Type:=8)
Set EndWB = ActiveWorkbook
Set EndWS = ActiveSheet
Set CurrCell = Target.Cells(1, 1)
Range("B41:H84").Select
Selection.SpecialCells(xlCellTypeVisible).PasteSpecial (xlPasteValues)
Hopefully I explained this correctly and any help would be appreciated. I am just beginning to learn some of the VBA things, but am not understanding certain things.
Below is the first part of the Macro...but I want the user to be able to define where the range starts by clicking on a cell. Then it will copy the visible cells in the 9 column range and 125 rows down.
Sub copyvisiblecells()
'
' copyvisiblecells Macro
' Macro recorded 4/8/2009 by John
'
'Declare local variables.
Dim EndWB As Workbook, EndWS As Worksheet
Dim Target As Range, CurrCell As Range
Dim x As Long, FromCnt As Long
'Select the range to copy.
Set Target = Application.InputBox _
(Prompt:="Select the first cell in the copy range", Type:=8)
Range("a14:i125").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
'Call pastetovisiblecells after a ten-second delay.
Application.OnTime Now() + TimeValue("0:00:09"), "pastetovisiblecells"
End Sub
Below is the second part of the code, but I want the user to be able to define the beginning cell for the paste to start, and then paste only to the visible cells in the 9 column, 125 row range.
Sub pastetovisiblecells()
'
'
'Declare local variables.
Dim EndWB As Workbook, EndWS As Worksheet
Dim Target As Range, CurrCell As Range
Dim x As Long, FromCnt As Long
'Select the range where it should be pasted.
Set Target = Application.InputBox _
(Prompt:="Select the first cell in the Paste range", Type:=8)
Set EndWB = ActiveWorkbook
Set EndWS = ActiveSheet
Set CurrCell = Target.Cells(1, 1)
Range("B41:H84").Select
Selection.SpecialCells(xlCellTypeVisible).PasteSpecial (xlPasteValues)
Hopefully I explained this correctly and any help would be appreciated. I am just beginning to learn some of the VBA things, but am not understanding certain things.