Macro to copy, then paste to visible cells

[hoss97]

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.
[VBA]
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
[/VBA]

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.

[VBA]
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)

[/VBA]

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.

[Demosthine]

Good morning.

Kudos on the work you have so far and welcome to the Forum. It's always good to have new people around.


Now, you are very close to your solution and should only require one line to be changed in each procedure. Change the following:

[VBA]
Range("a14:i125").Select

to

Range(Cells(14, Target.Column), Cells(125, Target.Column)).Select
[/VBA]

and

[VBA]
Range("b41:h84").Select

to

Range(Cells(41, Target.Column), Cells(84, Target.Column)).Select
[/VBA]

Any other problems, let us know.
Scott

[hoss97]

Thanks for the response Demosthine. I made the changes you suggested to the following:

[VBA]
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(Cells(14, Target.Column), Cells(125, Target.Column)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
'Call pastetovisiblecells after a ten-second delay.
Application.OnTime Now() + TimeValue("0:00:09"), "pastetovisiblecells"
End Sub
Sub pastetovisiblecells()
'
' pastetovisiblecells 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 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(Cells(18, Target.Column), Cells(143, Target.Column)).Select
Selection.SpecialCells(xlCellTypeVisible).PasteSpecial (xlPasteValues)


End Sub
[/VBA]

The First Part works except that it only copies one column. I need it to copy the visible columns, which in this case would be column a,c,e,g,i. The delay works well. The second part does show an input box, it lets me start the range, in a different work sheet, but does not paste anything. In this case, I am trying to start pasting in column b, row 18. I then want the columns b,d,f,h,j selected, and pasted down to row 143.

I think we are really close, but I am stuck. Thanks again for any help.

[alikirca20]

yes right,