vba code for master sheet

[kandre]

Hi guys,

I have a masterworkbook that consolidates data from 50 different workbooks via vba. The thing is that in each of the 50 workbooks there are check boxes on three sheets. Is it possible to write a code in the existing masterWB that can ask for and turn on/off these checkboxes? My original code is:

Sub Knapp1_Klikk()

Dim fPATH As String, fNAME As String, NR As Long
Dim wbDATA As Workbook, wsMASTER As Worksheet

Set wsMASTER = ThisWorkbook.Sheets("databank_fvp") 'edit this to the name of the master sheet
NR = wsMASTER.Range("A" & Rows.Count).End(xlUp).Row + 1 'next empty row on the master sheet

fPATH = "\\sgm434\avdelinger2$\CA\RL\Stab RL\NRL Forretningsteam\FVP\12.06.2013\FT\" 'path to all files
fNAME = Dir(fPATH & "*fvp*.xls*") 'get the first filename from the folder

Do While Len(fNAME) > 0 'process one file at a time til no more filenames
Set wbDATA = Workbooks.Open(fPATH & fNAME) 'open the found file
With wbDATA.Sheets("databank").Range("2:74") 'copies pax & atm
    wsMASTER.Range("A" & NR).Resize(73, 1).EntireRow.Value = .Value

End With

wbDATA.Close False 'close the found file
NR = wsMASTER.Range("A" & Rows.Count).End(xlUp).Row + 1 'next empty row on the master sheet
fNAME = Dir 'get the next filename
Loop

End Sub

[ovidius]

Hi,

My proposal is ...


Sub Knapp1_Klikk()

Dim fPATH As String, fNAME As String, NR As Long
Dim wbDATA As Workbook, wsMASTER As Worksheet

Set wsMASTER = ThisWorkbook.Sheets("databank_fvp") 'edit this to the name of the master sheet
NR = wsMASTER.Range("A" & Rows.Count).End(xlUp).Row + 1 'next empty row on the master sheet

fPATH = "\\sgm434\avdelinger2$\CA\RL\Stab RL\NRL Forretningsteam\FVP\12.06.2013\FT\" 'path to all files
fNAME = Dir(fPATH & "\*fvp*.xls*") 'get the first filename from the folder

Do While Len(fNAME) > 0 'process one file at a time til no more filenames
Set wbDATA = Workbooks.Open(fPATH & "\" & fNAME) 'open the found file
With wbDATA.Sheets("databank").Range("2:74") 'copies pax & atm
wsMASTER.Range("A" & NR).Resize(73, 1).EntireRow.Value = .Value

End With

wbDATA.Close False 'close the found file
NR = wsMASTER.Range("A" & Rows.Count).End(xlUp).Row + 1 'next empty row on the master sheet
fNAME = Dir 'get the next filename
Loop

End Sub

[AB33]

Ovidis,
Please use code tags with your code.

[ovidius]

Hi AB33,

Sorry, but I answered the request just as it was posted (how else would I have?)

P.S. I am "ovidius", but this is not a problem.

Ovidiu

[ovidius]

Delete double message.

[AB33]

ovidius,

Sorry for misspelling your name!

Look at OP's code. It is wrapped with a code. It is difficult to read a code with out being wrapped. Please read form's rule.

[ovidius]

Ok AB33, I read, but I still do not understand what the problem was posting my response to that request.

Thanks.

[AB33]

ovidius,
You still do not get!
If he had bothered to read form's rule, he would have wrapped your code. One of the rules says: When you post a code, you should use code tags. If you look at the code you posted, it does not have a code tag.
I know you are new to the forum and had tried to politely re-mind you.

[ovidius]

My problem is: how can I use code tags

Thanks for all,
Ovidiu

[AB33]

The form's rule also says if you need any help you should contact the moderators.
While you are at your code, click edit, then highlight (Select) your code(All lines) and click the # sign which appears on the quick Reply sub menu. The quick reply menu is on this page, down the bottom.

OR

[code]

your code

[code]

[ovidius]

Thanks very much AB33.

Regards