VBA Two counters for tqo separate sheets copy/paste

[teatimecrumpet]

Hi,

I'm trying to copy a range of cells in every third column and then paste it into another sheet in different rows different columns (but still every third row.

My code prompts the user to browse for and open a file to copy from and pastes it into another workbook already defined as "inwkb".

so basically it needs to match the first processed k column to the first processed q column and so on and so forth.

I keep getting the subscript out of range for the highlighted code.

Any help is much appreciated.

THANKS!

My code:

FileToOpen = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If FileToOpen = False Then Exit Sub
Set OutWkb = Workbooks.Open(Filename:=FileToOpen)

Dim q As Integer
For q = 5 To 254 Step 3

Dim k As Integer
For k = 5 To 254 Step 3
Cells(15, k).Select
Range(Selection, Cells(32, k)).Select
Selection.Copy Destination:=InWkb.Sheets("New").Cells(67, q)
Next k
Next q

 

[Damon Ostrander]

Hi teatimecrumpet,

Apparently there is no sheet named "New" in InWkb. The code you posted doesn't show how you set InWkb to the workbook object of interest, and I suspect the problem lies there. If you intention is that InWkb is the workbook that contains the code you can put

Set InWkb = ThisWorkbook

at the beginning of your code (before the code that opens OutWkb).

Keep Excelling.

Damon

 

[teatimecrumpet]

Thanks Damon,

You were right about the thisworkbook problem. The code wasn't in the actual workbook that I'm working on.

Now my new problem is is that the macro seems to be copying each round of k into a q before moving onto the next q and pasting each k. So 60+ rounds of k into each q and overwriting.

Can you suggest a better way to copy k in one sheet and then paste into q of the other sheet. Then copying k+3 and pasting to q+3...etc.?

 

[teatimecrumpet]

Sorry Damon,

I shouldn't have gone with two counters. Got rid of all instances of q and changed it to k.

Thanks for the help!