VBA Not selecting range properly

[DaLiMan]

Hello,

I want to create a For/Next loop to select a range.
When I try out my code it does not select the way I want.

I expect to select only D6 and M6, but it selects D6 to M6 instead.

Worksheets("sheet1").Range("D" & 6, "M" & 6).Select

When I try this line it works fine, but cannot be used in a For/Next loop.

Worksheets("sheet1").Range("D6,M6").Select

Is anyone able to tell me what i'm doing wrong here?

Thanx,
Daniel

 

[DaLiMan]

Yes it is. What's your current code look like?

Just figured out some code and a part of the code now looks something like this:

For I = 1 To Regels_deel100_ceiling
Call Upload_MaakNew(I)  ' create new workbook in other sub()
UploadBook = ActiveWorkbook.Name  'get the new name for later use.

Windows(WIKbook).Activate   ' switch back to the original workbook and use your Unio code.
    Union(Sheets("WIK data").Range("D" & ((100 * I) - 100) + 6).Resize(100), Sheets("WIK data").Range("M" & ((100 * I) - 100) + 6).Resize(100)).Copy
Windows(UploadBook).Activate  ' Back to new workbook 
ActiveSheet.Paste   'paste the selected items
Next

Now only have to save and close without anyone noticing, then a promt everything is ready for upload.
If you have any suggestion of tidy things up, it's very welcom!


PS: This "Union" thing is brilliant! Thanx again for that one...

Daniel

 

[p45cal]

Sub blah()
'Application.ScreenUpdating = False 'enable this line to hide activity
Set SourceSheet = ActiveSheet    'I'm assuming this is the active sheet when the macro reaches this point
With SourceSheet
    For i = 1 To Regels_deel100_ceiling
        Call Upload_MaakNew(i)  ' create new workbook in other sub()
        Set UploadBook = ActiveWorkbook  'get the new book for later use, but may not need this line at all.[COLOR=Red] (deleted in macro below)[/COLOR]
        Set UploadWS = ActiveSheet '[COLOR=Red]deleted in macro below[/COLOR]
        Union(.Range("D" & ((100 * i) - 100) + 6).Resize(100), .Range("M" & ((100 * i) - 100) + 6).Resize(100)).Copy [COLOR=Red]UploadWS.[/COLOR]Range("A1")
        ActiveWorkbook.Close True, "mynewfile " & i    'here I'm not sure if you've already given the file a name in Upload_MaakNew, so maybe just use: [COLOR=Blue].Close True[/COLOR]
    Next i
End With
Application.ScreenUpdating = True
End Sub

or

Sub blah2()
'Application.ScreenUpdating = False 'enable this line to hide activity
Set SourceSheet = ActiveSheet    'I'm assuming this is the active sheet when the macro reaches this point
With SourceSheet
    For i = 1 To Regels_deel100_ceiling
        Call Upload_MaakNew(i)  ' create new workbook in other sub()
        Union(.Range("D" & ((100 * i) - 100) + 6).Resize(100), .Range("M" & ((100 * i) - 100) + 6).Resize(100)).Copy Range("A1")    'this one assumes active sheet (new book) is destination sheet.
        ActiveWorkbook.Close True, "mynewfile " & i    'here I'm not sure if you've already given the file a name in Upload_MaakNew, so maybe just use: [COLOR=Blue].Close True[/COLOR]
    Next i
End With
Application.ScreenUpdating = True
End Sub

both untested. Some differences highlighted in red.

 

[DaLiMan]

both untested. Some differences highlighted in red.

Hmm, both codes look very promising.....
For me, it's time to go home so I'll test the code tomorrow.
Thanx so far en have a nice evening.

Daniel

 

[DaLiMan]

both untested. Some differences highlighted in red.

Just tested both codes and they seem to work fine.
Had to alter Set UploadBook = ActiveWorkbook ==> into ==> Set UploadBK = ActiveWorkbook in the first code. Don't know why, but the first one gave an ERROR.

Think I'll go for the second code to spare some lines....

Thanx a lot p45cal, I've learned a lot.
(altough I do not fully understand all of it.....LOL)

 

[p45cal]

Thanks for the feedback.