Copy and paste macro that ignores hidden columns

[Jediantman]

So I'm getting very frustrated with a macro I created which basically opens 14 individual sheets copies a selected range of data then pastes it to another. All was fine and working without problems until I had to include some hidden columns into the source data, which are also replicated in the destination. The problem I have is that on 2 out of the 14 source files when the range of cells to copy is selected it ignores the hidden columns so when it pastes it puts everything in the wrong place. The real frustration comes from the fact that the code I'm using is identical, all that changes is the file names.

The code that works is below:

[pre]

Workbooks.Open Filename:= _
"T:Aidan Spr2013 Tracker.xlsx" _
, ReadOnly:=True, Notify:=False
Sheets("Section 5").Select
Range("A23:CH130").Select
Selection.Copy
Windows("SPR13_Datamastermacro.xlsm").Activate
Sheets("Section5Master").Select
Range("A23").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=213
Range("A134").Select
The code that doesn't work properly:

Workbooks.Open Filename:= _
"T:Lewis Spr2013 Tracker.xlsx" _
, ReadOnly:=True, Notify:=False
Application.DisplayAlerts = True
Sheets("Section 5").Select
Range("A23:CH130").Select
Selection.Copy
Windows("SPR13_Datamastermacro.xlsm").Activate
Sheets("Section5Master").Select
Range("A689").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=231
Range("A790").Select

[/pre]
Any ideas are welcomed before I suffer an embolism...

 

[Luke M]

Very frustrating. Here's a few tweaks, not sure if it'll make a difference:

[pre]

Workbooks.Open Filename:= _
"T:Aidan Spr2013 Tracker.xlsx" _
, ReadOnly:=True, Notify:=False
Sheets("Section 5").Range("A23:CH130").Copy
Windows("SPR13_Datamastermacro.xlsm").Activate
Sheets("Section5Master").Range("A23").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Section5Master").Range("A134").Select

[/pre]

 

[SirJB7]

Hi, Jediantman!

Any other code in "Lewis Spr2013 Tracker.xlsx" open or worksheets activation events, at "SPR13_Datamastermacro.xlsm" activation events, at "SPR13_Datamastermacro.xlsm" workbook for activate event?

These are blind shots since it's hard to analyze it without the sample files.

Regards!

 

[Debraj]

Hi Jediantman,


One blink Shot from my side also..

[pre]

Workbooks.Open Filename:= _
"T:Lewis Spr2013 Tracker.xlsx" _
, ReadOnly:=True, Notify:=False
Application.DisplayAlerts = True
Sheets("Section 5").Select
'---------
Range("A23:CH130").SpecialCells(xlCellTypeVisible).Select
'---------
Selection.Copy
Windows("SPR13_Datamastermacro.xlsm").Activate
Sheets("Section5Master").Select
Range("A689").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=231
Range("A790").Select

[/pre]

Regards,

Deb

 

[Jediantman]

Thanks for your help guys. I'm going to have a little play with your suggestions and see if it will work. I can't really provide the sample files as the data I'm dealing with is confidential and I haven't the time to remove the data based on the amount I'm dealing with.